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.
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)
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
