CODEWITHSUNDEEP
sql-serverms-accessvba
namko
namko

Reputation: 647

currentproject.connection.execute in local tables vs linked tables

When we use currentproject.execute code like this:

CurrentProject.connection.Execute "INSERT INTO Books (Name, Type) VALUES ('Book Name', 'STRING');"

To manipulate both local and linked tables. During both case if I debug.print currentproject.connection, I get the same string which looks like this:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\David\Desktop\Comp - Copy.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Users\David\AppData\Roaming\Microsoft\Access\System3.mdw;Jet OLEDB:Registry Path=Software\Microsoft\Office\16.0\Access\Access Connectivity Engine;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

Since the connection string is same for both cases how does Jet Engine determine which one is local table and which one is linked?

Upvotes: 1

Views: 591

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 48999

Well if the table is linked, then Access actually looks at each table, and pulls that information each time.

If you want to see the actual connection used, then in the debug window do this:

? currentdb.TableDefs("local table").Connection

<blank returned>

Or

? currentdb.TableDefs("some linked table").connection

ODBC;Description=My cool conneciton;DRIVER=SQL Server;
SERVER=localhost\SQLEXPRESS;
UID=AlbertKallal;Trusted_Connection=Yes;
APP=Microsoft Office 2010;DATABASE=test3

You are most free to currentconnetion, even if you have “several” different Access back ends, or linked tables to sql server.

I guess I could state and suggest that Access really eventually uses the information in the table link to resolve the sql, not the CurrentProject.Connection.

currentProject.Connection will “resolve” and tell access where the table is – including linked tables. But once access grabs and touches the linked table based on CurrentProject.Connection, it then uses the information in the linked table.

This setup thus allows you to use any local table, or linked table as if it was just a plane jane regular table from Access point of view. So

step one: CurrentProject.Connection (where is the table, or the link)

step two: Get information from that table def - either local, or linked

step three: do the sql dirty work.

Upvotes: 2

Related Questions