Results 1 to 2 of 2
  1. #1
    samye is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2022
    Posts
    7

    ADODB Runtime error 80040e09

    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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't have a fix for connection but the SQL looks wrong. Why is 9892 in brackets? [] are used to define objects, not literal text or numbers. If BuildingCode is text type field, use apostrophe delimiters. Concatenation is not necessary for this SQL. If you want dynamic input for the BuildingCode parameter, that is another issue.
    Code:
    rs.Open "SELECT Bldg_Name FROM Building WHERE BuildingCode = '9892';", cn
    Side note: post code between CODE tags to retain structure and readability.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-02-2019, 11:45 AM
  2. ADODB error connecting to SQL
    By JamesSF in forum Programming
    Replies: 17
    Last Post: 12-22-2018, 11:53 AM
  3. Replies: 2
    Last Post: 01-10-2017, 02:50 AM
  4. 2010 Runtime ADODB Connection Failure
    By SteveDurham in forum Access
    Replies: 2
    Last Post: 10-04-2011, 03:31 PM
  5. Runtime 2010 ADODB Connection failure
    By SteveDurham in forum Import/Export Data
    Replies: 5
    Last Post: 07-18-2011, 12:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums