Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 27th, 2004, 04:23 PM
Registered User
Join Date: Jul 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default MDB vs ADP, once again.


I found this thread after some searching. I'm still somewhat disoriented, and looking for a straight awnser.

I have an ASP.NET application that generates reports, we can't make every report imaginable. We want to ship an Access Front end to users that want more than the CR reports and want to build queries. Use of the Access GUI for building queries is paramount. We created views and SQL Server users for authentication and filtering (filters based on 'CURRENT_USER')

I have a problem with both ADP and MDB files.

MDB files, with LINKED TABLES seem to import the data instead. I create the file, link the tables once, and after that, i'm never asked a username/password for the connection. The data is immediatly displayed (no delay, as if it were available locally), whether i open on the same computer, or another. Thus, the data is not updated, and my authentication scheme doesn't work. Pointless.

ADP files, queries are inexistant, according to the link above. The views i created appear as 'queries' in Access, which for one, will confuse the users, and two, it seems completly illogical. (Isn't a view a virtual TABLE based on a query and created in memory?) The Access GUI does not offer the same interface a mdb for creating something that would behave like a query.

I'm somewhat dissapointed by ADP's. All i want is the data stored in SQL Server, accessible within access (READ ONLY), and filtered by using views (based on SQL Server authetication). Everything that the users do within access shall stay in access.

Thanks for any light you can shed down the well.

Old August 5th, 2004, 02:01 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

quote:MDB files, with LINKED TABLES seem to import the data instead. I create the file, link the tables once, and after that, i'm never asked a username/password for the connection. The data is immediately displayed (no delay, as if it were available locally), whether I open on the same computer, or another. Thus, the data is not updated, and my authentication scheme doesn't work. Pointless.
Don’t know if I can help with your whole picture, but I can shed some light on how linking to SQL Server tables occurs. The data isn’t imported.

Opening Linked SQL Server Tables and Queries:

When you open a linked ODBC table, or execute an Access saved query that has an ODBC table as it’s source, Jet always sends a remote SQL request to the server. Your application passes the connection information stored in your DSN to the ODBC Driver Manager. This tells the Driver Manager which ODBC Driver to use, and which connection attributes to pass to the Driver. The DBMS-specific Driver then opens a connection to your backend. When you open a linked table in Access, this is the query plan Jet uses:

--- temp query ---

- Inputs to Query -
ODBC table 'dbo_TableA'
- End inputs to Query -

453467912) Remote SQL

The Remote SQL notification indicates that Jet is making a bunch of ODBC API calls and a SQL request to the Driver Manager, which are passed on to the Driver. First, Jet calls the SQLTables ODBC function to determine if the requested table exists on the server. Second, Jet calls the SQLColumns and SQL Statistics ODBC functions to get the table schema information (field names, indexes). Last, Jet maps Jet data types to the remote table data types.

Jet caches all this table schema information locally, which is why the tables open more quickly on subsequent tries.

Next, Jet has to decide which type of local DAO Recordset object to open to store the data returned by the server. The recordsource for the linked table data you see displayed in a datasheet is always a DAO Recordset. Jet always tries to open a Dynaset (updateable) Recordset first, which it can do as long as the table on the server contains a unique index (or primary key). If Jet can’t open a Dynaset Recordset, it opens a Snapshot (read-only) Recordset.

Basically the same thing happens when you run a saved query based on a linked- table. Jet parses the Access SQL string, then compiles it to see which parts (if any) it has to process locally (local processing is forced by the use of Access specific operators and functions that aren’t supported by ODBC-SQL or T-SQL). Generally, the whole SQL string is passed to the server for processing (via the Driver Manager and Driver, which translate the Access SQL string to ODBC SQL and T-SQL respectively). SQL Server parses and compiles the SQL string, retrieves the results, and returns them to the local Jet engine. DAO evaluates the results locally, and builds the appropriate Recordset and Field objects.

You can see all these steps mapped out for you if you turn on Tracing in the ODBC Data Source Administrator dialog. Go to the Tracing tab and click Start Tracing Now. A trace file (SQL.log) of the whole operation will be written to your primary drive.

Refreshing your ODBC Connection:

If you want to refresh the client side DAO Recordset more frequently to view changes made by other users, you should be able to simply decrease the ODBC Refresh Interval by changing the value in Tools -> Options -> Advanced -> ODBC Refresh Interval. The default is set to 1500 secs (25 minutes). You can use any range you like from 1 to 32,766. Access should automatically refresh records you are using on an ODBC connection at whatever interval you choose. (Only works on a network).

SQL Server Authentication:

I’m using a System DSN that I created to use SQL Server authentication. When I log on as a new user, open Access and open a linked table the first time, I am prompted for authentication credentials. This happens for every logon on each account, but only the first time. After that, the loaded ODBC Driver maintains the connection attributes on the connection at their current settings. I won’t be prompted again for any other SQL requests I pass over that ODBC connection. You might want to play around with User DSNs on each machine if a System DSN isn’t working for you for some reason.

Don’t know if any of that will help you out. I don’t use linked tables much; just wanted to think this stuff through in one place (so far as I can), and thought I’d pass it along.

Oh, about queries not existing in ADPs. Simply means that Jet/DAO QueryDef objects (“Queries” in the Access’s Database Window) don’t exist in ADPs. You’re right that views are “virtual tables” based on SELECT queries. I just like to think of them as “table filters.” That’s all they do. They’re like Access queries, in that they represent stored SQL statements, but Access queries have more functionality. They support the ORDER by clause, parameters, and can contain INSERT, UPDATE, and DELETE statements. Views can’t do any of those things. SQL Server basically uses views to accomplish the tasks performed by Access SELECT queries, and stored procedures to accomplish the tasks performed by Access action queries. Same functionality, just distributed over two object types.

You can, however, create a Jet-based view using ADOX with code like the following. But what you get is an Access SELECT query, which is why the views you created appear as queries in Access. Views and Access SELECT queries are not-so-distant cousins.

Sub CreateView()
    On Error GoTo CreateViewError

    Dim cmd As New ADODB.Command
    Dim cat As New ADOX.Catalog

    cat.ActiveConnection = _
        "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='C:\ViewsData.mdb';"

    cmd.CommandText = "Select * From Customers Where ID = 1"

    ' Create the new View
    cat.Views.Append "qryDataList", cmd

    Set cat.ActiveConnection = Nothing
    Set cat = Nothing
    Set cmd = Nothing
    Exit Sub


    Set cat = Nothing
    Set cmd = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub



Similar Threads
Thread Thread Starter Forum Replies Last Post
import data from mdb to another mdb mat41 Classic ASP Professional 3 March 9th, 2007 07:01 AM
adp slowness ginoitalo Access 1 November 6th, 2006 08:21 AM
Where is the processing done in ADP Scripts82 Access VBA 3 March 23rd, 2006 12:00 AM
MDB vs. ADP files MarioG Access 2 May 4th, 2004 07:10 AM
ADP Menubar ginoitalo Access 2 April 11th, 2004 11:53 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.