I'm working with VBA 97. Can someone explain the difference between the front end and back end. Any information would be very helpful.

Thanks Rick

Hi Ric,

I'll have a go. Since a picture is always worth 1K words:

1. Create an Access database to play with and import any old tables, queries, forms, reports and modules into it, or any combination of the above.

2. From the Tools menu select Tools -> Database Utilities -> Database Splitter

3. Click Split Database in the Database Splitter dialog.

4. In the Create Backend-Database dialog select a folder to place your backend db in. The default name of the backend db is YourDatabaseName_be.mdb. Click Split.

You'll notice that all the tables in your original database are now linked tables. If you select Tools -> Database Utilities -> Linked Table Manager, you'll see that the tables are all linked to the new back-end database you created. Your original database containing the linked tables is your new front-end application.

What you've done by creating a "frontend" and "backend" is both logically and physically seperate your database's application objects (queries, forms, reports, macros, modules) from your database's data objects (in the case of Access, tables).

This kind of design has all kinds of advantages. The two most prominant are:

1. Multi-user Applications: You can now place your new backend db on a file server, and multiple instances of your front-end application can access its data.

2. Ease of maintenance: If you modify objects in the front-end, it is much easier to redistribute the new version of your front-end file if it is detached from your data tables.

Splitting the (non-trivial) Access databases you work on is generally something you'll always want to do. (Table-linking is optional, other connection options are available).

The front-end/back-end distinction is roughly analogous to the client/server distinction but shouldn't be confused with it. The new front-end/back-end application I just walked you through creating is not a client/sever application. The back-end file is not a database server that provides services (like query processing) to a front-end "client" (service requester) application - it is still just a file data store managed by the Jet engine. All query processing still occurs in your front-end application. However, Access can be used to build a front-end client in a true client/server application. In this case, though, the Access front-end would connect to, and request services from, an actual database server (like SQL Server, Oracle, MySql, etc.)

Access Data Projects, and Access .mdb applications that use pass-through queries to talk to a database server, are true client front-ends applications. Access can't be used as a back-end in a client/server application.

There is certainly a lot more to be said about all this, but I hope that helps get you started.

HTH,

Bob

Thanks Bob, this helped tremendously ....

Ric

Hi Bob, I have a similar question, How would you set up a front end/back end with a security enhanced database? i want my users to have to log in and obviously each user would have access to different parts of the database...

thanks
-alexis