CODEWITHSUNDEEP
sql-servervbams-access
namko
namko

Reputation: 647

Currentproject.connection works without linked tables?

I have a linked table connected to the SQL Server backend. I went to the linked tables option in Access and deleted the connection which means there are no linked tables any more. However, when I go to debug console and type ?currentproject.Connection, I get the same connection string I got when I had the linked tables. The connection strings looks like this

 Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Dave\Desktop\COMP\COMPMAIN.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Users\Dave\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

However, even though the connection string is exactly the same when I remove the linked tables a simple code like this

Function connection()
Dim rst As New ADODB.Recordset
Dim i As Integer
rst.Open "select * from Customers", CurrentProject.connection
Do While Not rst.EOF
i = i + 1
rst.MoveNext
Loop
Debug.Print i

End Function

Which used to return 124 when I had linked tables

Gives me the follow error

Microsoft access database engine cannot find the input table or query

I am confused because the error does not say the connection is invalid but says cannot find the table

Upvotes: 0

Views: 102

Answers (1)

4dmonster
4dmonster

Reputation: 3031

currentproject.Connection in .mdb points to this .mdb itself. Not to linked tables! When you had have linked tables in your .mdb your code established connection to this .mdb and pulled data from linked table reference placed there. When you deleted linked table from .mdb your code establishes connection to .mdb but can not find table(as you have deleted it).

Connection string for linked table is stored in .Connect property:

CurrentDB.TableDefs("Customers").Connect

Upvotes: 1

Related Questions