Slashdot Mirror
Sharing MS-Access Databases, Efficiently?
codewizard asks: "Ours is a bank and we have a bunch of MS-Access databases(>50) which are being used by around 50 users around the globe on a daily basis. The set of databases are stored on a SAMBA share and each user accesses from the mapped drive.
As expected, sharing conflicts arise and multiple users are unable to access at the same time. So, we proposed having multiple folders on SAMBA each of which would have all the databases and the users logon script would determine where their mapped drive points to. This led to synchronisation issues (when a change is required in one of the master databases, we need to manually synchronise all other folders) and increase in storage size in SAMBA. Anyone have any other ideas on how you would have gone about sharing these MS-Access databases?"
Seriously. Port the data itself to MySQL, PostGres, MSSQL7 (If you're a bank, you can likely afford it. ;) ) or something like that.
If you have custom front-ends built in Access, you don't have to abandon them -- using ODBC, link the tables from the database server to the forms/reports/queries you're using now in the Access database. It may take a little bit of doing, but I think you'll find it'll work much better.
Ed R.Zahurak
You know, oblivion keeps looking better every day.
I thought Access supported network attach via a DBI. Why don't you have a central, or semi-central DB server or servers with the applications going through DBI to get to the database? Using MySQL that would be a sinch.
7 November 2006: The day Americans realized corruption and incompetence weren't addressing 11 September 2001
SQL server. Access was never designed to do what you're doing. I doubt that a SQL server will use much more CPU and it shouldn't be too hard to re-write the access stuff from SQL. Even if it were only two users using it, I would have designed it to be SQL based from the beginning.
iirc, microsoft sql server will support and/or import access databases, so you can start small and grow as needed. And as others have posted, you can still use the access front ends. but in any event, you _need_ a real rdbms.
In Bob we trust.
Don't use Access.
No, seriously. It's not made for multi-user access. Use SQL Server, which is easy enough for Microsofties to translate over to (SQL Server 2000's table design now looks almost exactly like Access'). Or use MySQL or PostGres, if you don't want to shell out bucks. Boom. No more multi-user issues.
If you've got forms or reports or what have you in Access, translate all the data to some other database anyways, and then use linked tables. You'll save yourself so much heartache. If this database has updateable data, you may have to worry about concurrency issues, but it'll be piddly compared to "every user but one is locked out".
I would like to know so I can avoid it. Using Access for anything other than a front end on such a scale seems pure folly. The few Access installations in a network environment have been troublesome at best.
Why isn't a large, global bank using decent technology?
I am sorry to offer no help on "ask Slashdot" but this to me seems irresponsible, even if the data in these DBs is non-sensitive, non-mission critical.
This is the dumbest question ever.
One thing you could do is drop Access and use MySQL or keep Access to feed the data but have frequent exports into MySQL which are in turn queried by all the people around the world, it would work if most of them only need to take a look and not change things.
Maybe even my company can help you with this.
http://managedaffiliateprograms.com/contact.php
Several things: .MDB files over slow links, you should have no trouble with at all. Be *SURE* that you've split your Access database in two .MDB parts - the front (graphical, reports) and the back end (data). Link the tables in the frot-end to the data--end.
.MDB to be modified if you have code that doese things like this_form.width = 400. Access tries to get write access on the scewiest of things, so you don't want the data part of the database to suffer just because Access it rtying to get write access on a form.
.MDB files.
.MDB files) has to read large chunks of data over the network in order to run it's queries. If you do a select * from customers where customer_city = "redmond" Access will read the entire customer table over the network. Yuk.
Unless your users at accesing the
Also - have the front end copied over to the users hard drive - this limit network usage and will cause their local copy of the
Turn on oplocks on your Samba config for the data
Get a real database: Access (with its
If you give the same query to PostgreSQL, MySQl, or DB2 - only the query is sent to the server, and only the relevet rows are returned. A much lower bandwidth requirement - you can reasonable expect to run a properly designed database over a 56K modem conection with good results.
Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.
Stop fighting it. Just get a real database. Why are you so hell bent on using Access? What you are describing is far beyond the scope of what anyone ever imagined when designing Access. Why are you trying to simulate the features of a real database, when they already exist, and they're good too... I'm seriously puzzled.
I used to bulls-eye womp-rats in my pants
but I speak out of my own experience.
:)
:)
:)
First, as someone previously mentioned, Access is not meant for multiple access. I'm surprised you managed to support >50 access databases for ~50 users. Local access alone creates multitude of problems, not to mention global access. I remember the days when they've to yell for access an Access database for update, kinda like manual locking mechanism.
Second, some people above mistook your usage of database is a pure RDBMS alone. Access itself is an application which embedded its programs and database into one big file. I fully understand the problem of porting Access to other means like MYSQL+Java. To minimize the impact of porting existing Access database data and applications, I recommend implement an extra applcation locking to each Access file when somebody request an update. You can also implement a single sign-on system for all ~50 Access database, this way you can have more justification for the changes. (this is much better than the manual 'yell-locking' I mentioned
Third, do as they said, port to MySQL+Java. It worths the trouble.
We've developed a medium sized application in Access with a SQL backend. All you have to do is set up ODBC datasources and have a SQL Server backend by linking the tables to it.
It's a piece of cake, and you can have the Access database on the client as no data is stored in them. Plus you can also consolodate all that data into one big database which would be usefull for backup and maintenance.
Also Access databases have a tendency to corrupt, so never store data in them.
If you're a bank, you need to be running a DB that supports transactions. (PostgreSQL/Oracle come to mind). Anything else is taking unnecessary risks with your customers' data. Oracle, while very large, is an incredibly powerful, expensive, and frankly, worthwhile proposition. There are plenty of tools out there to convert your access databases (I wonder if they're even normalized?) to Oracle, and you can use J2EE or Forms, or your dev language of choice to make frontends for all your now happy users.
(Does it show I attended an oracle workshop this morning? Nope? Shucks... )
Hey, not sure if this is your problem, it probably isn't but try Tools->Options->Advanced->Default_Open_Mode->Share d. Works for me in an environment with ten or so people all using the one database at once.
That's assuming you are tied into an Access system, although I've found MS Access to be more reliable as a database than MS SQL. For example, in MS SQL you can retrieve a date record and it will be in Australian date format, put exactly the same data back into the database and it will be treated as US date format. Also this bug really shits me.
If your IT department is as anal retentive about MS as mine is, then you can't just use a real database like mySQL so unfortunately it looks like you're stuck with Access. But I don't find it all that bad, in fact I prefer using Access databases with a web frontend to any other system because you can treat the database as a file (unlike mySQL, MSSQL, oracle etc).
I am artificially intelligent.
Of course, the best solution is to step up to a real database. Failing that, you could try some of the following ideas:
If the database is small, you could have multiple copies of it, and sync the changes later. If it's really small, you could automatically make a copy for each user.
You could have a broker program (a middle tier) that is the only one which accesses the database. The clients would talk to the broker, not the database. This could be done easily with any of the middle tier technologies.
You could cache data to the client and reduce the number of accesses to the database.
There is not enough information in the original post. If your database is large or if your changes are time critical (e.g. users must see changes in real-time) then your only option is to upsize.
Despite this, it can be made to run reasonably efficiently and reliably if you know how. I have a mate (Hi, Jeremy!) who does this regularly, and other things like rewrite the GDA (no shit, he did this) on his iPaq to not do stupidly pointless transforms, and to use integer arthmetic. The result is an eye-opening iPaq that displays maps in realtime instead of at plotter speeds.
Nevertheless, for your average gonzo it's too much work. And Microsoft products are a dead end anyway. As practically everyone else is saying, whack up an ODBC interface and hide whatever you like behind it by way of a real database. If you can make it web-based, that's an additional layer of useful abstraction that allows to to hotswap even more technologies.
No doubt I'll get a /. lameness filter message about too many syllables when I press this button... (-:
Got time? Spend some of it coding or testing
Like some previous posters have said, you are pushing the limits of an Access MDB.
MS SQL-server is the obvious recommendation, mainly for "compatibility" reasons. In fact, I've counted several such recommendations already. It's a "real" database, and a reasonably easy upgrade path (depending on how the code was written...).
That said, it's cheaper (i.e. probably free for your purposes) for you to use MSDE - which is really a limited version of the SQL Server engine. I think the only major limits are that the maximum db size 2GB, and the management tools are not included (you'll have to live without, or get a license to them some other way, such as getting a copy of Office Developer).
Having said that, perhaps you really don't want to touch the code... there are many ways to optimize an Access MDB. I don't think anyone has suggested making sure Samba isn't causing a bottleneck -- I recall reading about updates or settings that dramatically affected filesystem databases like Access MDBs.
Anyone have any other ideas on how you would have gone about sharing these MS-Access databases?
The easy way to share access databases (if you really HAVE to use access - I'd strongly recommend a proper database server such as MySQL or if you can afford the licenses, MSSQL) is to share the database via ODBC. Bear in mind that when sharing Access in this fashion, the MS JET DB engine has a nasty habit of corrupting the database once you hit so many simultaneous users.
Trying to use Access for 50 databases with a multitude of concurrent users the world over is simply the wrong tool for the job.
Get a proper database and your problems will solve themselves.
Get rid of Access as a database server. It is ok to use Access as a frontend to almost any "real" database server. If you have a samba server, you may want to put a PostgreSQL or MySQL server on that machine. Install ODBC sources for the server on the clients, and connect Access through ODBC to the server. In Access, separate program code and database, delete the database part, and distribute the *.mdb "Program" via the samba server. Your users can still use the user interface they are used to, but there will be no more file sharing conflicts.
Denken hilft.
If you use your access-db in a network you can also get a corrupted db. Although this can be solved by simply opening the db on the local computer => access-db is not designed for network use.
Porting Access97 data to PostgreSQL
Create a website to access the Access databases through. Use ODBC/JDBC connections to the DB instead of using local Access forms and reports. Beyond that you can also setup any database behind the scenes with it.
The company I work for deals with CU's for archival. Since I've been here we've used Access and MSSQL as our back end and IIS/asp/msjava combination. We are moving from that to a database independent and real java/servlet environment.
One thing to remember is that if you create a website to access the database data through you will need to fork out real money to either a developer to build the site and maintain it, or assign that as someone's job. Over all it is a good idea since you then can be on any type of machine and access/update the data instead of a machine that requires Access on it.
Norris/Palin 2012
Fact: We deserve leaders who can kick your ass and field dress your carcass.
I haven't seen it mentioned here yet but it could be issues with samba. I don't think samba supports the granularity of locks on a network file that NT does. In a file based DB being able to lock just portions of the file for updates is crucial. I've seen places where people did some pretty sick things with Access so I know its possible to have 50 people use an MDB at the same time. If all 50 are doing updates though Access will pretty quickly shit itself and corrupt the db. It really isn't meant for more than 5 users simultaneously updating. And as others have said seperating the presentation from the data into different MDBs is also a must.
Also as another idea you could look into using terminal services and host the databases on that machine. Your bandwidth with probably be much better utilized. I would probably only go that route if a real RDBMS wasn't feasible.
In Republican America phones tap you.
Seriously, I want to know. Because if it's MY bank, our relationship is over.
I mean, Access - come ON!
Access is never the correct solution in the enterprise. Put the data in sql and design a web front end to access, update, and delet data...
Power Corrupts,Absolute Power Corrupts Absolutely, leaving one person(group)in charge is absolutely corrupt.
I will differ, and heartily suggest SQL Server 2000 instead. This is because:
1. Microsoft provides an excellent upgrade path from Access to SQL Server, using Wizards to automate many of the "tedious details". You should be able to split your front-end forms / code from backend data, and move your backend data from Access to SQL Server with minimal coding.
2. A SQL Server installation, IMHO, is much easier to install, setup, and maintain. This will be especially true for your situation, where you have a lot of small to moderate sized databases to maintain on what will probably be 1 - 2 Server boxes.
3. You are likely to spend a lot less to buy SQL Server. Microsoft's SQL Server license for SQL Server has tended to be cheaper than Oracle. Also, you can use a PC "commodity" server with Windows 2000 / 2003, instead of the high-end "*NIX" boxes the Oracle people usually push on their customers.
Have you tried debugging the problem from the Samba side? There are a number of helpful configuration documents and mailing lists.
In one of the installations of Samba, as a replacement to Novell, I ran into a file locking/corruption problem. The problematic application was an ancient DOS scheduling program with multiple users. The solution turned out to be:
[DOS_SHARE_NAME]
oplocks = no
There are a couple of other options for file locking but that was the one that fixed our problem.
Other locking options:
strict locking = yes
kernel oplocks = no
level2 oplocks = no
the handy groups.google search
smb.conf man page
The Unofficial Samba HOWTO
...enterprise application? A number of people have already mentioned that Access is unsuitable for that, so I'll just add my voice in agreement and move on.
A couple of options that I see, particularly if you're primarily an MS house and/or don't want to rewrite your front end:
1) Get the backend into something else, like MS SQL Server. You can keep your forms and reports virtually unchanged. If you go that route, I recommend this book highly: Microsoft Access Developer's Guide to SQL Server
2) Post your question over here: comp.databases.ms-access. There are a lot of professionals in that newsgroup who are generally more than happy to tackle questions like this and have a tremendous amount of experience and expertise behind them.
Its obvious many of you don't work in a corporate environment, if you did, I wouldn't be reading many of the comments I am. "youre using access? why you shouldnt trust your customer information to that!" please, he didnt SAY what they were using it for. In a corporate setting, there are many uses for access, that can be done quickly, cheaply, and without IT intervention. Many times these are things that scratch a particular itch for a person, that others find useful, but dont have the backing necessary or the ROI to merit a serious development effort. there are simply too many other things that take precedence over them.
these are usually small things like request tracking, departmental knowledgebases, individual or departmental timetracking utilities, etc. they aren't used for serious things and never for critical customer information. get a brain.
NEVER, EVER put databases on a network share. EVER.
I don't know of a single company that would recommend this -- in fact, Sybase (the company I'm most familiar with) goes to great lengths to say what I've just said.
Simply put: recovery cannot be guaranteed over network-shared databases in the event of a system failure. (Not to mention any performance decrease....)
The correct way to handle this situation is to install a DBMS with a client-server architecture (I have no clue if Access can do this), and have the individual users LOG IN to the database instead of sharing it via Samba.
For the sake of your banks' data, man, get reading (and working) on this!!!
Put the tables on a real server, be it free ( postgres, mysql, what ever ) or MSDE ( you get a distributable runtime if you own VB.. )
You can still use Access for your GUI and queries and reports if you cant move them to something else.
But what ever you use for the interface don't distribute raw MDB files for the data.. you are asking for problems. I don't care what Microsoft says, 'jet' is just a bad idea all around. Plus you get better performance as a side effect.
---- Booth was a patriot ----
FYI,
;-)
Access will attempt to optimize your queries and literally mangles them. Do so googling searches to find the registry key to turn this query optimization off by default.
I remember reading an article about a company that used an Access front end to a PostgreSQL backend and they had to turn on debugging on the PostgreSQL server to see what the submitted queries were. Access was optimizing their already optimized queries and literally making them not optimized in the process.
This sort of frontend to backend via Access Links and ODBC connections is very very common in the industry. Of course, there will always be some work on an IT staff member to set it up and get it working. i.e. the Access front end needs to be installed and configured properly and the MDB needs to be distributed, etc. But it's very do-able. You can use just about any backend database you like. i.e. Oracle, IBM UDB/DB2, Sybase, PostgreSQL, MS-SQL, MySQL, Interbase, etc.
We make a product that puts a TCP/IP wrapper around Microsoft Access databases. This wrapper means you can seamlessly query these databases remotely from either Windows or Linux without having to worry about sharing violations.
Our product name is UniverSQL and I would be more than happy to send you a demo if you email me. We provide full technical support and GUI admin and query tools that run on both Windows and Linux.
Regards,
Tyler
Please tell me what bank you are working at so I can be sure I never put my money in an account there.
Ok ok. Let's back up here.
First, hopefully you're using just the "client" version of Access, which doesn't allow editing of forms/reports/etc., just viewing and executing.
Second, you shouldn't be having your users all access the files at the same time on the same share. It's just asking for trouble (especially since I don't know how compatable a Samba share is with Access's sharing methods.) You'd be better off keeping a local copy on each client's computer. You could do this using a logon script, or if you're savvy enough, could even code it inside your Access application itself (on startup, check it's own version against the one on the server, if not up to date, warn the user, etc.)
A common mistake that people make when working with Access is to try to use it as anything more than a "front end". Sure, it might be 'easy' to code in, but it's pretty damn sloppy and inefficient. You're better off making nice looking forms which call pass through (re: stored procedures) queries on the server, which in turn handle the logic and data processing for you. (You are using Oracle or MSSQL, right? God I hope you're not using Access's tables to store data..) Linked tables are the _worst_ way to do things.
Also, as many others have said on here, you're better off translating your Access database into a web interface...
Not All Who Wander Are Lost
You *WILL* get a corrupted DB. Just a matter of time.
* And remember, it's spelled N-e-t-s-c-a-p-e, but it's pronounced "Mozilla."
I've seen a few posts saying that Access isn't designed for multi-user databases. It would be more acccurate to say it is not optimized for that function, but is fully capable of it.
You must carefully construct the Access database specifically for shared access; it doesn't do that by default. If your average user created the database, then it is NOT configured for multi-user access.
I created a small Access database that is used by around 40-50 people and the MDB file is shared from a NetWare server for security. You have to setup an Access "workgroup" and "join" that workgroup from each machine and have people login to the database. It does allow for some access control and does do record-level locking.
In short, I think you're having data access conflicts because your databases are not configured for multi-user access.
Ouch! The truth hurts!
Damn. I read this and thought ... I have just the solution - use a real SQL database back end and use your Access forms to connect to that database via ODBC ... surely NOBODY would have thought of that.
/. I think even the First Post guys said 'umm FP - and use SQL via ODBC'
I'll be a hero!
Me and every other mouth breathing, knuckle dragging, SQL using and ODBC connecting code monkey on
Glonoinha the MebiByte Slayer
I've used Access over shares as a front end, and as a back end. And as others have said in their various ways, replace Either the front end or the back end. DON'T use Access for both. Whipping out a custom VB or web frontend to an existing database using standard automated tools is practically seamless, and with "ONLY" 50 users you can get away with leaving Access as the backside. You could also go with switching the actual data to any of the previously recommended Backends(MySQL, MSSQL,Oracle, etc) and leave the code/frontend in Access. That of course will require you deal with the mess that is Access and ODBC. So ultimately plan to replace both, but schedule one to replace first and phase into replacing both.
Food not Bombs is a nice platitude but it breaks down when you notice that the Bombees are usually well fed
For a long time, we were having corruption issues, and didn't know what the reason was - it got so bad, that I ended up writing a patch to custom sync records on tables with a backup - that is, if a user changed a record, it would be saved prior to the change, then saved after - if any corruption happened, the system would sense this, flag the record, and pull the old record from the backup table. This worked fairly good (it was randomized and distributed across the running instances of the app, so it would only "bog down" one person at a time), but it was far from ideal - and when the backup table became corrupted...
Eventually, our sysadmin of the NT server we were hosting the Access DB file on figured out the problem - it turned out that there is a file locking flag setting (I cannot tell you the exact name of the switch - I appologize), that when it was off (or on?) would cause these corruption issues. When we flipped it, the problem went away completely.
Of course, this doesn't answer your question - but it does answer the question of whether corruption is truely an issue with a multi-user Access DB. For your question, I think I would handle it in the following manner:
First off, you would want to consolidate the tables of the DB's involved. 50 separate DB's seem like way too many - I can understand if some are set up for one department, and others for another - if that is the case, group the tables together in that manner, and get the DBs organized. Next, you will want to set up indexed primary key fields on those tables that have similar keys (or same keys) - for selection performance and SQL JOIN reasons. You will then want to make some kind of front-end (or modify the one you already have - which it doesn't sound like you have one, or if you do - it is an Access front -end).
The thing you have to realize about Access (speaking of 97 here - not sure about recent versions) - is that it expects to be "local" to the client - that is, it is opened like a file, and not in a "client-server" true-ODBC fashion. From what I understand, what the JET engine does when you access it via ODBC is the same as if you were accessing it as a file under DAO - just one small level of redirection - it isn't possible to use ODBC on the database otherwise. If you want real ODBC functionality, then I suggest you convert your database(s) over to PostgreSQL or something, then set up a client based DSN-less connection using a VB or Java front-end (or, if you don't want the pain of maintaining multiple client installs, set up Perl, PHP, or Java servlet system to query and display results from the DB - look into LAMP systems as well, if you want to really do some fun stuff).
In theory, you could code a service type application in VB that could talk via TCP/IP to clients, passing SQL queries, and it would use that against the Access DB, then return back a recordset (maybe with requests and responses in XML format) - you would have to do some kind of session management, so you know who requested what, and when - managing queues of requests and responses. Not sure how well it would work, though...
Ideally, your best bet is to get away from Access, and on to something better - then build front-ends to the data that limit what the users can do to the data (also, build in an audit table with functions to update it - pass the function say a key value, a type, a new value, and an old value - the audit table would store this information, along with a timestamp and user name - so you know who changed what when, and they can't lie about it if they bork the database). Finally, set up a system to back up those DBs if you don't already do it (I would hope you do).
Reason is the Path to God - Anon
You could try to webify it. Lots of things talk to Access over the web; even from a Unix web server. Look at ODCBSocketServer+Perl for doing it from Unix; ASP, ColdFusion, WinPerl, etc. for doing it from Windows.
Of course, everyone else is right, you really, really shouldn't use Access for anything. At all. Ever.
Best of luck.Power corrupts. PowerPoint corrupts absolutely. E. Tufte
Okay, first thing first. This is running off of SAMBA. You need to upgrade these MDBs to SQL Server (standard). Don't argue, just do it. If your boss doesn't agree just point him to this slashdot posting and he'll get it.
Next, you have to convert the Access file to an ADP (Access Data Project). When you create an ADP you can tell it to what database to connect to. This is GREAT when calling the 'connection' property, because now you don't have to create a connection to the SQL Server database each and everytime you want to do something. You just do this:
Dim ADOrst as New ADODB.Recordset
ADOrst.Open "sql statement", CurrentProject.Connection
THAT'S IT! The biggest headache will be to convert DAO to ADO. It's worth it. Take a look at this link: http://www.msofficemag.net/features/2000/01/vba200 001sf_f/vba200001sf_f.asp. It will help. There are other sites, use Google, that will show you how you can use your DAO connection code and make it look like ADO, so it gives you time to actually do the manual recoding will keeping your users happy and connected. This is a short-term band-aid. You are still going to have to re-code to ADO, but is not that difficult.
**BENEFITS OF ADP**: You will have the power to accesss views, stored procedures, tables, as if they were local. I was a bit scared when I couldn't use my queries in access, but I converted them to either views, but 85% of them were converted into stored procedures. They are a lot better, because know I just setup my stored procedures to accept variables, sql server does the grunt work for data crunching, and then my recordsets were returned back very quickly. Here is an example:
dim rst as new ADODB.recordset
rst.open "exec proc_stored_procedure_name " & variable1, CurrentProject.Connection
If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
Do While rst.EOF = False
{do something with the records}
Loop
End If
You can use your stored procedures over and over again for different parts of the program without having to recreate the wheel everytime. I know you probably use queries or tables as lookup sources for drop-down boxes. Create a View or Stored Procedure and then set the controls "Row Source" property to the view or stored procedure. To do it programatically do this:
cntrlDropDown.RowSource "proc_storedproc_or_view"
That's it. You can also pass variables to the stored procedure and return the recordset to your control. In access I had a seperate query for each drop down box for hierarchy data, now I just have one stored procedure and pass it a variable to tell it what listing on the heirarchy I need.
One major benefit of stored procedures is that they are cached and optimized on SQL Server without having the database having to optimize it every time it runs. It keeps the plan already and saves time there.
Next, convert DAO to ADO. If you have something like this for DAO:
Dim db As DAO.Database
Dim rst as recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("sql statement")
Then you should change it to this:
Dim rst As ADODB.RecordSet
rst.Open "sql statement", CurrentProject.Connection
If you also have a recordset object, you just need to modify it like above. You can do away with the database object if you use the CurrentProject object. You no longer have to close the database object either since it was never created, but you still need to close the recordset object and set it to nothing, like so:
rst.close
Set rst = Nothing
Everything should still work fine. One more note about using ADO. If you use the AbsolutePosition property, in DAO the value starts with 0 for the first record, in ADO it start with 1 for the first record. I have to learn that the hard way.
I know ADPs are fairly new,
A number of others have mentioned porting the back end from Access's built in JET database using file sharing semantics to another SQL database. MySQL has been mentioned, and the combination of an Access front end with MySQL servering tables via ODBC is a much more reliable, especially with multiple users.
It is possible to write Visual Basic for Access code that opens a database, looks at the tables, and generates SQL DDL and DML statements. Having done this, here is a list of gotcha's, or Things discovered as life goes on...
SQL Naming and Other Standards Checks
Check all tables against the following list while in Design view:
1. SQL names may not contain spaces. Access names may.
2. Access allows the use of some SQL reserved words, like INDEX, for table and column names. SQL obviously does not. These names must be changed to an SQL identifier (not a reserved word).
3. Set the Required property to Yes for all primary key columns.
4. Set the Indexed property to No for all columns that are members of a multi-column primary key.
More details on these and other topics are presented below.
Fields vs. Indexes
The Jet database engine splits fields and indexes into two separate hierarchies. One cannot tell when inspecting fields if they are part of an index or not. Instead one must navigate from the field level back up to the table level and then down to the index level.
Primary Keys and NULL Values
Access does not mandate table primary key columns to be NOT NULL. One can use a column in a primary key even if the Required property is No. What Access does instead is disallow NULL values in the corresponding index. So during data entry, an INSERT or UPDATE with a NULL primary key is rejected by the DAO at index update time, which precedes column update time.
MySQL will not allow this. It (and other database servers) mandate that no part of the PRIMARY KEY may contain a NULL value. In Preview, verify NOT NULL is specified for all columns in the CREATE TABLE statement when a subsequent ALTER TABLE ADD PRIMARY KEY statement is generated.
Primary Key Indexes
Access automatically generates an index for each column in a multi-column primary key. If indexing is also specified for such a column in the table design form, Access generates two different indexes on the same column.
To eliminate redundant indexes, do not index a column that belongs to a mult-column primary key.
Autonumber Data Type Support
Access does not allow setting the required property on an autonumber field. Set NOT NULL in an SQL DDL statement when both of the following conditions are true:
1. The column has the dbAutoIncrField attribute.
2. The primary key contains exactly just this column.
Access allows autonumber fields that are NOT keys. MySQL does not.
--Jason Kazarian
access2mysql@leftbrainedgeeks.com
First, I agree to move to something else, but I won't say my preference here. Some wank will find a way to blame my choice on my family tree, and sidetrack the discussion into the gutter.
That said, if you insist on using MDB's, hook to them with a front end...check out Cold Fusion, formerly from Allaire, now in Macromedia's hands.
You shouldn't allow anyone but your DB Admin to tap directly into your Access files. Building an interactive, browser based front-end is not that hard, and it provides many of things you are either looking for now, or will be seeking in the future. I worked for a bank, and found them pushing MS solutions. While we pushed something mo' betta, we also worked to get the data they already had into the hands of the users, via MS Access and CF.
Ton's of examples out there...get started now, and relax sooner than later.
Makes good sense. Assuming he can move to SQL easily. I know I did. I wonder what kind of table structure his 50 db's represent.
Like I said, I have my own preferences these days...none of which currently include MDB's. I think I only have one legacy Access base still working, and it's 3 hears old.
Not knowing his skills, budget, resources and goals in detail, it's hard to recommend beyond examples, etc.
just change the name of the mdb file to .mde (e.g. finance.mde), and it will manage locks etc. better. Our shop did this as a temporary solution while moving to a real RDBMS and it worked for 30 users.
In new Access database projects I still prefer to use Data Access Objects (DAO) even though it is an 'older' technology.
Here is why I generally dislike Active Data Objects (ADO):
The main disadvantage is that DAO incurs a slight performance hit for the SQL pre-processing step. It adds perhaps a couple milliseconds to start of the query. For performance-critical queries you can always write a pass-thru SQL query or use ODBCDirect. (ADO can be thought of as simply 100% pass-thru queries.)
it can be done. The key is to make sure your applications are opening the databases and recordsets in the proper share/locking mode, minimize the amount of time lock is in place, minimize the amount of time recordsets are open, and handle the runtime errors correctly. Multiple users should be able to have read access to same database simultaneous, and still be able to update the database but at the same time. First make sure all your network shares are setup properly for this, and you probably have a big enough user base to justify moving to a true client-server based database. But don't listen to those who tell you to stop using Access just because it's not a "real" database system.
you can use this little library to get direct platform-indepentant access for all you access data.
thank God the internet isn't a human right.
Now, on the client PCs, Macs or Linux boxen...
That's it!
The only real caveat is that MS-Access itself will refuse to open an MS-Access database via the MS-Access ODBC driver, so if you are actually using the front-end features of MS-Access, then you will need to start porting them to MS-Excel or REALbasicPro while you keep runing (with file-mapping the .MDB file) in parallel. (Note: If you are using MS-Access as a front-end, you may also want to consider simply importing your .MDB file into a SQLServer2000 database, but even this will require tweaking your MS-Access code in ways that would require a bank to do lots of re-testing --thus it's recommended to do a rolling conversion using ODBC Router, which will also enable painless switch-over of the back-end .MDB-file to SQLServer2000, ORACLE 9i or DB/2 when the front-ends are ready.)
There's an article that was linked from slashdot describing how to convert access to sql here: http://developers.slashdot.org/developers/03/04/14 /1059254.shtml?tid=185&tid=156