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.
On Error GoTo CreateViewError
Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
cat.ActiveConnection = _
"Provider='Microsoft.Jet.OLEDB.4.0';" & _
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
Set cat = Nothing
Set cmd = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"