Is Access ADP ODBC?

[bradmaunsell]

Based on some recent reading, it is my understanding that an ACCESS ADP uses LINKED tables.

That is, the upsizer creates some kind of internal ODBC connection to the SQL database.

Furthermore, because it is ODBC linked, the ADP actually runs the Access Jet engine and not the SQL engine. That means everything is happeneing on the workstation/network and not the server.

During my SQL 2005 Express learning curve, I have set up a SQL 2005 Express database and then created a sepearte Access front end (without running the upsizer). I have created a connection string and call module for connecting and discnnecting for use with unbound forms. This works fine. I am still struggling with rnning stored procedures in from Access VBA for sp stored SQL 2005 Express.

Can anybody confirm my thining, tell me I am absolutely wrong, point me in the right direction or throw me some kind of life preserver?

Thanks

 

[belovedcej]

adp does NOT use linked tables.

There are two ways Access can link to the SQL server. The first is ODBC, using the jet engine and linked tables. It is a mdb file.

The adp file, or Access Project, has no queries or tables of its own - all of that resides on the server. Only true front-end stuff resides in access - the forms, reports, data access pages, and modules.

Since you are using SQL and not some other back end, I highly recommend using an ADP file. (If you were using Oracle you would have to continue using linked files.) It is faster and the Access file is much smaller because it doesn't have any data being stored.

Do a google for Access adp and see what you find for more info. Also, just open access and create a new access "project" and start playing. It will help more than explanations will. I think you'll like adp.

 

[bradmaunsell]

I did setup an ADP and I am amazed at the speed of several routines that rea extremenly slow on my linked mdb application.

I was concerned that the ADP maintained an open connect to all tables whether any forms etc were open. Based on the performance of my test, all looks pretty good.

Thanks for the response.

 

[belovedcej]

Glad to help! One thing to consider as you pursue this. You will find threads dealing with security and adp. If your users have modify access to the tables on the server, a knowledgable user could coneivable create a new adp file and link to those tables, thereby bypassing your controls.

It is typically recommended that forms be unbound and updates be done by stored procedure. Then users only have read access to views and execute access to stored procedure, which protects your tables better.

 

[bradmaunsell]

Years ago, I did some work in Access 97 and SQL 7. Those apps did not display any tables in Access. There was no continual connection to Access 97.

When creating forms, I had a module that would connect to the SQL database when the form opened. Then the vba populated the form and disconnected from the database. I also did some of this same sort of thing using VB 6.

This was more work to program, but did not seem to "clog" the network as my current Access 2003 MBD applications do. We have several apps with about 120 tables and 90 MB of frontend code. Although the programs do a great job for our underwriters, thay are big and performance is truely aweful.

The ADP appears similar (but different) than the stuff I wrote in 97. I am begining to get the "hang of it" now.

Thanks again for the help.