Tip: never use front-back end system with Access and VB

Gentlemen,

I've discovered some interesting stuff. Previously, when I developed in Microsoft Access, I always used a front - end and a back - end. I did this by splitting the database. It works fine.

Actually, when you use VB, NEVER use database splitting. It slows down your application enormously. I use, for example, ADO in VB to access my database. I do this via an Access front - end. I open one connection to that front - end, and after opening the connection, I open about 20 recordsets. Normally, this shouldn't slow down the application heavily, because I use only one connection. But, under the hood, Access creates for every opened recordset a connection, I guess. That is slowing down the application very much. Starting the application, and opening 25 recordsets goes fast with one user, but when the second user access the database via a front - end, it takes more than 2 minutes before the user can make use of the application. The power of ADO is decreased when using front - end / back - end systems with Access databases. So, never do that.

For example, starting the application twice (with both applications accessing the same database, and it are the same programs) goes extremely more fast, because access don't hold connections (and thus resources), but your program does.

I hope this will help.

grtz

That's sounds a very interesting point, I wonder if you could clarify something...

At the moment I am using Access 2000 databases, but (almost) all the access to them uses either a VB6 program (using DAO and ADO), or ASP pages. All the user interface requires is the tables, but when the DataBases are in use, I can't modify the Access forms which I use to make administrative changes to the DBs. If I split the database would that:

1) Allow me to modify forms when someone has a connection open to the DB tables?
2) Would this split cause the connection problems that you describe, or do these only apply if you are using Access as your front-end.

Thanks

Aidan

Because you have a front - end you can modify the forms. But, when you have five front - ends at one back - end, you have to edit all the front - ends in order to keep them the same.

I guess you are only going to use one front - end, where one person will work in. Then, it's no problem. If the access to the database goes via the front - end for all the users of the database, it will cause the problems. But, if the access goes directly to the database where the tables are hold, no problem at all.

With more than 10 users, I won't recommend using Access as front - end application. You'd better switch to VB with ADO for connecting to the database in that case

grtz

Thanks for the quick reply - it sounds like I could split the DB. Only one or two people will use the Access front-end, and this will be occasional. I just want to split it so that the forms and reports won't be locked while someone else is accessing the tables. At the moment I have to copy the DB, modify the copy and then lock everyone out of the main DB while I copy the modifications across.

good luck