Hello,
I have searched high and low on the web and cannot find an answer. I have a 2010 Access DB that I am trying to connect using ADODB via Word VBA. I create/open the connection and when trying to open the recordset receive a run-time error that the ODBC record(s) cannot be read; no permission on <my table name>. The database is located on the server and I suspect it has something to do with Workgroup permissions. The DB is 2010 but my Access is 365 MSO (Version 2210 Build 16.0.15726.20068) 32-bit.
I do not have the authority to change anything on our file server but can put a ticket in to have security modified.
I'm sure others have had this problem and it's so weird I cannot find anything on it. I found a URL (https://www.oreilly.com/library/view...0/ch04s02.html) that discussed the various properties of the connection and copied some of that code to look at the mode. I know the connection is open because it brings back property information.
Here is the code:
Sub TestODBC()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=S:\Access Applications\BackEnd_Tables\Facilities_v07.accdb;U id=Admin;Pwd=;"
'cn.Open "FacilityAccess" uses ODBC driver defined on PC
rs.Open "SELECT Building.[Bldg_Name] FROM Building " & "WHERE (((Building.[BuildingCode]) = " & "[9892]));", cn
MsgBox rs!Bldg_Name
rs.Close
cn.Close
End Sub