CODEWITHSUNDEEP
sql-serverms-accessvba
Eric
Eric

Reputation: 363

Unable to open Linked Access 2003 table to MS SQL Server in VBA code

I have a table in Access 2003 that is linked to a MS SQL server table. I am able to go into the Access table, manually update it and see the changes reflected in the MS Sql server table.

However, I'm unable to even just open the table and put the result in the record set in my VBA code. 

Set rs = CurrentDb.OpenRecordset("dbo_tbl_EventLogs", dbOpenTable)

(rs is always at nothing)

The same line of code works on a 'local' Access table.

Upvotes: 0

Views: 243

Answers (3)

Parfait
Parfait

Reputation: 107747

Consider using dbOpenDynaset option which is used by default for linked tables or stored queries whereas dbOpenTable is used for local tables as mentioned for OpenRecordset() method:

If you open a Recordset in a Microsoft Access workspace and you don't specify a type, OpenRecordset creates a table-type Recordset, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset-type Recordset.

Set rs = CurrentDb.OpenRecordset("dbo_tbl_EventLogs", dbOpenDynaset)

Upvotes: 2

djeos
djeos

Reputation: 1

Make sure you store the user credentials used to connect to the database... (attribute dbAttachSavePWD)

Upvotes: 0

Rafael Mori
Rafael Mori

Reputation: 949

You need to connect to your DB using ADO. Is simple and easy. Just change to your info and enjoy it.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

ServerName = "yourServer,yourPort(example: 1442)" 'Enter your server name and a port here
DatabaseName = "yourDBinTheServer" 'Enter your  database name here
UserID = "yourUserID" 'Enter your user ID here
Password = "yourPassword" 'Enter your password here

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection

cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & ";Uid=" & UserID & ";Pwd=" & Password & ";"
rs.open yourQueryAsString, cn

Upvotes: 1

Related Questions