Question Using Access 2007 in multi user environment

[Michael L]

Hello every one,

Have not used access a lot since access 97,2000.
I work for a small company (10 users) which logs a jobs in hard copy format.
Is it possible to use access 2007 in a multi user environment with 2 or more people updating the information from seperate computers in real time.

Can this information be displayed and editted on an intranet or any other way.

Regards

 

[boblarson]

Hello every one,

Have not used access a lot since access 97,2000.
I work for a small company (10 users) which logs a jobs in hard copy format.
Is it possible to use access 2007 in a multi user environment with 2 or more people updating the information from seperate computers in real time.

Can this information be displayed and editted on an intranet or any other way.

Regards

Yes, you can do this. I would suggest reading this about splitting your database as that is the way to go and to have each person with a copy of the frontend on their machine:
http://members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[shadow9449]

Hello every one,

Have not used access a lot since access 97,2000.
I work for a small company (10 users) which logs a jobs in hard copy format.
Is it possible to use access 2007 in a multi user environment with 2 or more people updating the information from seperate computers in real time.

Can this information be displayed and editted on an intranet or any other way.

Regards

Yes, it works in a similar manner to previous versions of Access in this regard. The only difference is that when you check to add your Trusted Locations, you have to also check to allow the file to be accessed over the network.

You may consider splitting the database at some point as well if it's going to be shared. I usually don't split the database if I plan to be working on it frequently unless there are more than 15 - 20 users. Some people advise splitting it as soon as you have more than one user. It's up to you...

SHADOW

 

[boblarson]

You may consider splitting the database at some point as well if it's going to be shared. I usually don't split the database if I plan to be working on it frequently unless there are more than 15 - 20 users.

That is like playing Russian Roulette with your database. It isn't a matter of IF it will corrupt, just a matter of when and how bad. Your actions are apparently based upon the luck of never having it happen YET. The problem is, if you understand the actual workings inside of it all, it is really NECESSARY to split if you are going to want things to be safe.

Some people advise splitting it as soon as you have more than one user. It's up to you...

And up to your willingness to replace the data/database. Just like driving without seatbelts - you may not get hurt in a crash if you aren't wearing one, but it is much safer if you do wear one at all times. It won't protect you fully, but it is amazing how many people get thrown out of their vehicle and killed because they weren't wearing one. Such is the same with splitting your database (even if you only have ONE user) if used over a network.

 

[shadow9449]

It isn't a matter of IF it will corrupt, just a matter of when and how bad. Your actions are apparently based upon the luck of never having it happen YET. The problem is, if you understand the actual workings inside of it all, it is really NECESSARY to split if you are going to want things to be safe.

You may be right, but I've been using Access for 14 years and what I find corrupts the database is usually faulty hardware (intermittent NICs, routers, improper cabling, even power failure and wireless networks that go up and down like a yo yo). I have many hundreds of users using my databases with as many as 40 in the system at the same time.

One other thing that I've find can damage a database will be if two users try to write to the same record at the same time. There is a warning asking if you want to copy or drop changes but the result of either course of action is to fry the record. I hope that in a split database the result is different. I avoid this problem altogether by user record-level locking on my forms.

I'm sure that different developers have different experiences and in guiding someone, I would advise that they read Kallal's paper and make their own decision.

SHADOW

 

[boblarson]

You may be right, but I've been using Access for 14 years and what I find corrupts the database is usually faulty hardware (intermittent NICs, routers, improper cabling, even power failure and wireless networks that go up and down like a yo yo).

And a split database will usually survive an incident like that which one that wasn't won't. At worst you usually lose the one frontend that had the lost packet due to the nic, router, etc. And that is more easily replaced than the data that multiple users have been entering all day. So, what I'm saying is, you wouldn't keep a SQL database, for business, on a single disk drive (RAID is the normal safeguard) so why would you be less vigilent on other data when it is not that hard to implement?

 

[shadow9449]

And a split database will usually survive an incident like that which one that wasn't won't. At worst you usually lose the one frontend that had the lost packet due to the nic, router, etc.

I didn't realize that the tables wouldn't fry if the database is split. I thought the problem with Access is that it locks the table because it has to transfer ALL the records and if you disconnect from the table, the database gets fried. I would have assumed this would be a problem with split databases as well.

If you're right, then it certainly makes a strong case for splitting. The only disadvantage is if you have to maintain the database and need the user to send it to you. I usually have them zip and email the whole file which is a cinch because the tables are enclosed in the same file.

I suppose the best way would be to have them send you a copy of the FE, a copy of the BE, use a utility such as the famous Bob Larson FE copy utility. It would be hard to work on if you have to relink the back end to match your own PC, then send it back and require everyone to relink based on the network configuration...?

SHADOW

 

[Banana]

You may be right, but I've been using Access for 14 years and what I find corrupts the database is usually faulty hardware (intermittent NICs, routers, improper cabling, even power failure and wireless networks that go up and down like a yo yo). I have many hundreds of users using my databases with as many as 40 in the system at the same time.

FWIW, I wouldn't want 2 users, let alone 40 users work on a unspilt database.

Furthermore, it scares me because Jet is fundamentally a file server, not a database server so it's one page away from haywire.

Finally, just because it hasn't happened doesn't mean it won't happen tomorrow. I can easily find someone who has never wore seatbelt for last 20 years, but it's even easier to find people who died in car crash because they weren't wearing the seatbelt.

I avoid this problem altogether by user record-level locking on my forms.

It's not a question of locking, but rather accessing the pages at all that puts it at risk of corruption.

 

[boblarson]

I didn't realize that the tables wouldn't fry if the database is split. I thought the problem with Access is that it locks the table because it has to transfer ALL the records and if you disconnect from the table, the database gets fried. I would have assumed this would be a problem with split databases as well.

There are times when you can lose the backend even though split, but that is more rare than the common occurence.

If you're right, then it certainly makes a strong case for splitting. The only disadvantage is if you have to maintain the database and need the user to send it to you. I usually have them zip and email the whole file which is a cinch because the tables are enclosed in the same file.

If you are working remotely and not on the actual system, then yes it does become a slight issue. However, you can use something like Armen Stein's relinking code which can overcome that.

 

[Banana]

I didn't realize that the tables wouldn't fry if the database is split. I thought the problem with Access is that it locks the table because it has to transfer ALL the records and if you disconnect from the table, the database gets fried. I would have assumed this would be a problem with split databases as well.

First, that old myth of all records being transferred is incorrect. Jet uses pages, and it does have to take in schema and indexes to figure out which page it need to retrieve, but certainly not all records.

Here's a simple proof. Make two tables. Insert 50 records in one table and 50,000 in other. Open them and see if the second table takes 1,000 times longer. Heck it won't be even 5x times longer. They'll open just as fast because Jet will retrieve just enough to fill the screen and lazily retrieve pages based on your interaction.

If you're right, then it certainly makes a strong case for splitting. The only disadvantage is if you have to maintain the database and need the user to send it to you. I usually have them zip and email the whole file which is a cinch because the tables are enclosed in the same file.

That's quite a different scenario. We would split database if we need to allow two different users to work on one database at same time. Emailing each other database for self-use is perfectly fine and can work as long others don't have to have the most to update data.

I suppose the best way would be to have them send you a copy of the FE, a copy of the BE, use a utility such as the famous Bob Larson FE copy utility. It would be hard to work on if you have to relink the back end to match your own PC, then send it back and require everyone to relink based on the network configuration...?

Actually, you only need to make sure the production BE is on a folder that everyone can access (e.g. a file server for example) then link to that, then distribute to everyone. It should then work as long the computer it's installed on can navigate to the same folder.

 

[shadow9449]

FWIW, I wouldn't want 2 users, let alone 40 users work on a unspilt database..

The 40 user system was split long ago! The fewest I've split is about 6. Never had a problem less than 6.

Furthermore, it scares me because Jet is fundamentally a file server, not a database server so it's one page away from haywire...

Unfortunately you're right. And it's unlikely that Microsoft will change anything about this in future releases of Access. All I can say is that thankfully, the performance is pretty good overall, even with a decent number of users.

It's not a question of locking, but rather accessing the pages at all that puts it at risk of corruption.

Please elaborate or provide a reference for some further reading

SHADOW

 

[shadow9449]

If you are working remotely and not on the actual system, then yes it does become a slight issue. However, you can use something like Armen Stein's relinking code which can overcome that.

In MOST systems that I've split, I log on from remote and work on one of the desktops (and then tell one of the users to paste the file onto each user's computer when I'm done because I'm too lazy ). If the user is too cheap to spring for Access on their computer(s) (which can really add up if you have a lot of users), then I have no choice but to ask them to email it. I've implemented code that stores the path to the BE in each FE. If I have to make changes to the FE, that path is no longer valid and EACH user has to find the path. You'd be surprised at how confused some people get by having to use a file browser to find a database to get their day started.

SHADOW

 

[shadow9449]

That's quite a different scenario. We would split database if we need to allow two different users to work on one database at same time. Emailing each other database for self-use is perfectly fine and can work as long others don't have to have the most to update data.

I'm not following you here. I'm talking about people using the database whereas I will be (perhaps across the country) making changes to the tables and forms and code and so on. I work when they are closed for the day.

Actually, you only need to make sure the production BE is on a folder that everyone can access (e.g. a file server for example) then link to that, then distribute to everyone. It should then work as long the computer it's installed on can navigate to the same folder.

Problem is if I've needed to work on the BE offsite and had to change the path between the FE and BE. They will then have to relink to get back up and running.

SHADOW

 

[boblarson]

Problem is if I've needed to work on the BE offsite and had to change the path between the FE and BE. They will then have to relink to get back up and running.

That's why I said you could use relinking code to relink and if you store the current location before making changes, you could let it relink automatically for them. It is totally possible to do without too much work.

Here's the code:
http://www.jstreettech.com/files/JStreetAccessRelinker.zip

 

[shadow9449]

That's why I said you could use relinking code to relink and if you store the current location before making changes, you could let it relink automatically for them. It is totally possible to do without too much work.

Here's the code:
http://www.jstreettech.com/files/JStreetAccessRelinker.zip

Just skimming through it, it looks VERY useful! I'll have to experiment with it and see how it goes.

Many thanks!

SHADOW

 

[Banana]

Unfortunately you're right. And it's unlikely that Microsoft will change anything about this in future releases of Access. All I can say is that thankfully, the performance is pretty good overall, even with a decent number of users.

And why should they? Jet is great solution for this particular niche. There's also SQLite which is fundamentally same thing and I understand it's been popular with developers who wanted no-fuss database engine that didn't require a daemon. Access is primarily marketed toward knowledge workers and for one-user application, the convenience of having everything in one file makes perfect sense. Not everyone has the time and training to set up a full-on server with daemon to perform the requests. Furthermore, Access makes for a great front-end client because it gives developer the versatility of having both local table and query engine in addition to a full-on server backend, so I'd argue that theorticially, Access front-end/RDBMS backend would be able to support much more concurrent users than a thin client front-end/RDBMS backend or C++/C#/VB/Whatever front end with no query engine/RDBMS backend, especially if local processing and lookup tables are important for the applications.

Please elaborate or provide a reference for some further reading

AFAIK, there is nothing authoritative on specifics on how Jet works. There's lots of information from Microsoft's KB article about how to optimize Jet's performance, but not as much "Whys". I only learned this from other MVPs on both this board and UtterAccess, basically a result of logical inference and indirect experimentation. As one MVP at UtterAccess noted, a common topic in optimizations is usage of indices which led to the conclusion that indices are stored on a separate pages, so when Jet looks at .mdb file, it has to fetch all pages about indiices as well as the schema and maybe tidbits here and there and use those pages to figure out which pages (with actual data) it needs for a particular operation.

So, I can only point to the KB articles and whitepapers about optimizations and hopefully you'll get bits and pieces about how Jet works.

I'm not following you here. I'm talking about people using the database whereas I will be (perhaps across the country) making changes to the tables and forms and code and so on. I work when they are closed for the day.

Scratch my comment. I misunderstood you- I thought you were talking about scenario where one user works on database at a time and sharing with others by emailing the file, which doesn't require splitting and is perfectly fine because it's still one-user application, even if it's shared among various users, just not concurrently.

Problem is if I've needed to work on the BE offsite and had to change the path between the FE and BE. They will then have to relink to get back up and running.

One way around this is to run two versions, one production and one development version and when you're ready to roll out a new version, you just import in all non-tables objects to the production version then distribute that FE version which has the links already set up or use the codes that Bob showed you.



BTW, if you don't mind, can I ask you how are you sharing Access across the world? VPN? Terminal server? Carrier pigeons?

 

[shadow9449]

And why should they? Jet is great solution for this particular niche.?

The reason they should is because there are millions of people out there who have the skills to create a great database using Access. Why should there be limitations as to concurrency, stability and security for these databases because of a lacking native back end. I know this is going to get into the long debate about how Access is not jet and so on, but the fact remains that when you pop open an Access database and create a table, that table IS using Jet. A great benefit to Access would be either for the native integration to be with a better back end or to improve Jet to the point where it's stable and has the security that other back ends use.

There's also SQLite which is fundamentally same thing and I understand it's been popular with developers who wanted no-fuss database engine that didn't require a daemon..

I've seen SQLlite used in database applications. Do you know offhand if there is a cost per user? With Access, I can create a database and just pop it onto a user's network. If they have Access installed, great, otherwise, it's a free download for Access 2007 Runtime (if you don't mind having to mess with the user's registry to force a secure location ) Do you know what the performance is when you get into a higher number (let's say over 50) users? Is it as portable as an .mdb file that can be zipped and emailed in under a minute?

Access is primarily marketed toward knowledge workers and for one-user application, the convenience of having everything in one file makes perfect sense. Not everyone has the time and training to set up a full-on server with daemon to perform the requests. Furthermore, Access makes for a great front-end client because it gives developer the versatility of having both local table and query engine in addition to a full-on server backend, so I'd argue that theorticially, Access front-end/RDBMS backend would be able to support much more concurrent users than a thin client front-end/RDBMS backend or C++/C#/VB/Whatever front end with no query engine/RDBMS backend, especially if local processing and lookup tables are important for the applications.

That's interesting. It certainly gives good reason to use Access.

BTW, if you don't mind, can I ask you how are you sharing Access across the world? VPN? Terminal server? Carrier pigeons?

Terminal server.

The way I have this one set up is as follows: there are nine physical locations with 40 people altogether - eight branches and a busy head office. I created folders on the server labelled 1 - 8. The server is located at the head office. I put the back end on the server and one front end in each folder which are linked to the back end. Each location logs into their front end. So, you have 3-4 people sharing a front end rather than 40. Each user at the head office is running over a LAN so they all have their own FE. When I need to work on it, I log in using Remote Desktop. The people at the head office all have Access installed but they didn't want to pay for the other users so the latter are using the runtime.

This has been running this way for about a year and a half. So far, there was a hiccup once in March where the server needed to be rebooted. Other than that, I'm told that the setup has performed perfectly.

SHADOW

 

[Banana]

The reason they should is because there are millions of people out there who have the skills to create a great database using Access. Why should there be limitations as to concurrency, stability and security for these databases because of a lacking native back end. I know this is going to get into the long debate about how Access is not jet and so on, but the fact remains that when you pop open an Access database and create a table, that table IS using Jet. A great benefit to Access would be either for the native integration to be with a better back end or to improve Jet to the point where it's stable and has the security that other back ends use.

Three answers:

1) Again, Access is usually marketed/geared toward smaller groups for who need no fuss database application that can be set up and running in matters of days, if not hours.

2) The limitation are simply a fact of the design, so it's not artificial, and can be easily removed by using a different backend.

3) They already tried native integration. Remember ADP? While it's still supported in 2007, when you read between the lines and considering that they added lot of new stuff to ACCDB, they didn't do so with ADP, and the fact that Access team recommends ACCDB over ADP, it doesn't look like ADP is going anywhere. Furthermore, Pat Hartman already has berated ADP for being limited in that you can only link to SQL Server, while with MDB/ACCDB, you can link to whatever your heart desires, even to non-RDBMS source such as Outlook and Excel spreadsheets or even plain jane CSV files. Finally, I understand that while ADP was great with SQL Server 2000, it wasn't less so with 2005 where they added/changed many features thus breaking the integration and we've not addressed the fact that most of time it would be easier to write stored procedures using SQL Server tools, not in ADP. To be quite honest, I wouldn't event want to try and manage backend RDBMS via Access- I'd prefer to run both at once and write changes to the RDBMS directly in its environment, be it SQL Server Management Studio for SQL Server or command-line client for MySQL or Navicat for MySQL and concentrating on developing the forms inside Access.


In my last project, I wrote an application with MySQL as backend, using security of MySQL and found it to be quite stable. Exactly what do you think is missing here?

I've seen SQLlite used in database applications. Do you know offhand if there is a cost per user? With Access, I can create a database and just pop it onto a user's network. If they have Access installed, great, otherwise, it's a free download for Access 2007 Runtime (if you don't mind having to mess with the user's registry to force a secure location ) Do you know what the performance is when you get into a higher number (let's say over 50) users? Is it as portable as an .mdb file that can be zipped and emailed in under a minute?

This is off the top of my head, but it's just a library, and is essentially a file server, just like Jet is. It is free to use and distribute, but you would still need an application that can access the functions in the library to make use of the SQLite. As for performance, security and concurrency, I'd imagine they would be approximately same as Jet. For example, SQLite has no record-level locking but instead does a database-lock, effectively serializing the changes. I think it's a great product and from what I'm reading, it's primarily marketed as a embedded solution, which is a slightly different niche from the one Jet is trying to fill in. You can read about it at their site and reach your own decisions.

That's interesting. It certainly gives good reason to use Access.

Indeed. In my brief stints learning about .NET, I had a shocking realization about *major* differences in how data are retrieved. When writing a application in language of your choice, programmers traditionally create a disconnected recordset, and use that in conjunction with bulk updates for the application. In other words, they're chunky. OTOH, Access is chatty instead of chunky. When working with back-end, Access will retrieve just enough rows to fill the screen for the users, plus few more as a buffers then after that, it lazily retrieves rows, so users get both fast load-time and browse-time. With disconnected recordsets as generally used in other applications, fast load time is not always doable (though it can be hid by wrapping it in background, or if language/libraries support it, doing it asynchronously and display the initial set on screen as Access does.) and the data may not be always 'live' (because Access refresh the data at a specific interval). So Access provides users fast access to data and ensure that it's always fresh, at expense of a slightly increased network traffic. To me the expense is preferable to chunky approach's expense of grabbing hundreds if not thousands of records in one go which is more likely to create deadlocks and lock waits (Well, to be fair, several databases now support MVCC so no locks is required at all for reads, though some may lock for update. Even granting that there's no locks created, the expense of retrieving so many rows is still there, and if 10 users were to access the backend at approximately same time, it'd be longer than if they waited in line or than if they only retrieved few rows enough to paint the screen with). So I was surprised to hear that such functionality isn't natively supported outside of Access, and made me appreciate even more about what Access does behind the scene to provide fast and reliable access to data.

Terminal server.

An excellent solution.

 

[shadow9449]

Three answers:

1) Again, Access is usually marketed/geared toward smaller groups for who need no fuss database application that can be set up and running in matters of days, if not hours.

And this can't be done if Jet weren't improved...? That way you get the best of all worlds.

2) The limitation are simply a fact of the design, so it's not artificial, and can be easily removed by using a different backend.

You correctly pointed out in item 1 the downside of using a different backend. When you tell people about your success with MySQL, you point them to a white paper on Microsoft's website. This white paper is above the heads of MANY people who do have the skillset required to create a successful Access database. Why not change Jet to accommodate these people? Microsoft invests so much time improving the front end (thankfully) so why not give it a more robust back end at the same time - one that does not require more studying of white papers to interact with.

Remember ADP?

ADP was far from "native" integration. There were many limitations using ADP and required the resource-overhead of having SQL Server installed (not to mention cost until 2005 when Microsoft released SQL Server Express. I don't even want to discuss the problems with MSDE!)

... or Navicat for MySQL and concentrating on developing the forms inside Access.

Have you used Navicat? I downloaded a demo of it and it looked like a very user-friendly way to work with MySQL. If you have, what is your view?

In my last project, I wrote an application with MySQL as backend, using security of MySQL and found it to be quite stable. Exactly what do you think is missing here?

See above. Not to mention the learning curve necessary to learn how to use and deploy MySQL (and the little quirks when it comes to differences in data types, for example).

... it's just a library, and is essentially a file server, just like Jet is. It is free to use and distribute, but you would still need an application that can access the functions in the library to make use of the SQLite. As for performance, security and concurrency, I'd imagine they would be approximately same as Jet.

Then I'll stick to the convenience of the native Jet

... . When working with back-end, Access will retrieve just enough rows to fill the screen for the users, plus few more as a buffers then after that, it lazily retrieves rows, so users get both fast load-time and browse-time. With disconnected recordsets as generally used in other applications, fast load time is not always doable (though it can be hid by wrapping it in background, or if language/libraries support it, doing it asynchronously and display the initial set on screen as Access does.) and the data may not be always 'live' (because Access refresh the data at a specific interval). So Access provides users fast access to data and ensure that it's always fresh, at expense of a slightly increased network traffic.

Sounds like Access is a better system than most people give it credit for.

SHADOW

 

[Banana]

And this can't be done if Jet weren't improved...? That way you get the best of all worlds.

To improve on those, Jet would have to be fundamentally changed. As I said earlier, the limitations are not artificial but rather inherent in the design of the Jet and that's why I pointed out SQLite earlier- both offers a no fuss setup at expense of limited concurrency and stability, both which are only fixed by having a daemon, which throws out the whole advantage of no-fuss setup.

You correctly pointed out in item 1 the downside of using a different backend. When you tell people about your success with MySQL, you point them to a white paper on Microsoft's website. This white paper is above the heads of MANY people who do have the skillset required to create a successful Access database. Why not change Jet to accommodate these people? Microsoft invests so much time improving the front end (thankfully) so why not give it a more robust back end at the same time - one that does not require more studying of white papers to interact with.

Well, I'd argue that if they were in over their heads, they're better off handing off the job to a contractor who has the sufficient expertise and training to handle the development. It is simply a matter of the design. A while back, someone asked about difference between VB.NET and Access, to which I gave analogy of Access being alike legos and VB.NET being alike to a bunch of wood and rocks. You can build a replica of say, Effiel tower in no time with Legos, but it's not as realistic and life-like. But you can carve out from wood or stone and make it even more realistic but it doesn't only take much more time but also require more skills to produce such model. Finally, many more people can build Effiel Tower out of Legos, but few people have the time, skill and patience to do the same with raw materials. I believe the analogy stills applies here- we can keep asking for more and more functionality until we're blue in face, but eventually we start to leave some of people out of the picture, and that would be now contrary to Microsoft's goals with the Access (though I'm sure several of us here may not always agree with their goals- I certainly don't and think Access is more properly a IDE but I'm not the one dictating the policy.) To improve the back-end for increased concurrency and performance without fundamentally changing the design as a file server dispensing out pages would not be a trivial, and why even bother when there are several good database engines out there for use?

And here's more. It's not the only solution. You proved so yourself by using Terminal Server to extend Access beyond a single LAN network and make it usable in world. Even better yet, you didn't have to change one line of code for the Access application to work in a Terminal Server setting.

David Fenton would like to prove so with replications (and IINM, SQL Server since dropped its support for replication, so that puts Jet ahead of SQL Server already because it's still fully supported. {Though it is not to say that it will continue to be supported as ACCDB no longer support this functionality, though Access 2007 is fully capable of supporting replication}). We're still talking pure Access solution here.

Luke Chung, the guy who runs FMS Inc has done a study and claimed that only 2% of Access database needs to be upsized. (though I'd really like to know where he got that number...) I think this shows that Access does a great job of filling its intended niche as a small group database solution.

Which reminds me- that's another thing I think we're seeing in conflict. Access is great at both being a small database solution *and* a default front-end client, so for that reason it may yo-yo between two different niche between versions. 2007 was a swing toward the former, definitely, much to my consternation.

That's where the analogy of legos and woods/rocks break down. Access is actually versatile enough to be molded to other Legos blocks *and* wood/rocks that has the cutout that's compatible with Access's molding. So there's already many way to take Access in different directions, with different costs in regards to training, time and expertise for a given specifications.

ADP was far from "native" integration. There were many limitations using ADP and required the resource-overhead of having SQL Server installed (not to mention cost until 2005 when Microsoft released SQL Server Express. I don't even want to discuss the problems with MSDE!)

Yes, it was a long way off, but IINM, it was just one version and thus was expected to get better over time. Only that developers (based on what I read and I could be wrong here) didn't want any of it. Too many niceties about .mdb/.accdb had to be sacrificed just to use .adp and for little gain. Thus Microsoft's hope of moving us from Jet to SQL Server/MSDE didn't get anywhere. I should point out that even though DAO (as a data access technology) was obsoleted long ago, and we were encouraged toward ADO, 2007 ends up with a new version of Jet and DAO. Should speak volumes, I think.

Have you used Navicat? I downloaded a demo of it and it looked like a very user-friendly way to work with MySQL. If you have, what is your view?

First a warning: If you're using MySQL, you should be using command line clients for long enough to be familiar with it. Nothing will make sense until otherwise. In fact, I'm having this issue with SQL Server because there's not a command line client to slap me silly, especially in realms of permissions, indices, and configurations. So I'm learning SQL Server slower compared to MySQL. I'd bet that Navicat would just be as much crutch without the familiarization.

That said, when you've became familiar with MySQL's inner working, NaviCat is absolutely great way to work with stuff. Made my security administration a breeze and it was easy to quickly create tables, once I knew what SQL should look like and occasionally edit some of it. I'd buy it in heartbeat, actually, if I'm still working on MySQL.

Sounds like Access is a better system than most people give it credit for.

Indeed. A while ago, we had a thread about why IT hates Access, and I think it was quite illuminating. If you haven't had read that one, here it is.