accesing a remote table in access sql error 8000-4005

Question

I using an excel macro to manipulate an access database, whith no change i start to get error 8000-4005, using an sql like SELECT * from [;DATABASE=C:\AdmData\Base\tempo.accdb;].tablename. microsolft support told me that in 64-bits access remote table access requires more detail, but i can access info regarding requiered info. i use a lot of this remote access so use linked tables via access graphical interface is not an option. where i can find the sintax requiered to access a remote table from the sql sentence directly?

'connection setence

Set CN = CreateObject("ADODB.Connection")

CONEXION = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\AdmData\Base\Tempo.accdb;"

CN.Open CONEXION

'table access

ConAdm = "[ODBC;APP=Microsoft Office;DATABASE=C:\AdmData\Base\tempo.accdb;].tablename "

CONSQL = " SELECT *  from " & ConAdm & "  "

Set Datos = CN.Execute(CONSQL)

and i get error 8000-4005

could you help me?

regards fontanahe

Answer 1

To resolve the error 8000-4005 when accessing a remote table in Access SQL, you may need to ensure that your connection string and SQL syntax are correctly configured. Here are some steps to consider:

1. Check Connection String: Ensure that the connection string is correctly formatted. You are using the ACE OLEDB provider, which is appropriate for Access databases. Make sure the path to the database is correct and accessible.
2. ODBC Connection: Since you are using an ODBC connection to access the table, ensure that the ODBC driver is properly installed and configured. The connection string you provided for the ODBC connection should be verified for correctness.
3. Syntax for Remote Table Access: The syntax for accessing a remote table through ODBC in Access SQL typically follows this format:

      SELECT * FROM [ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\AdmData\Base\tempo.accdb;].tablename
   

   Ensure that you replace tablename with the actual name of the table you are trying to access.
4. Permissions: Verify that you have the necessary permissions to access the database and the tables within it. Lack of permissions can also lead to errors.
5. Database State: Ensure that the database is not in a state that prevents access, such as being opened exclusively by another user or application.

If you continue to face issues, consider checking the Microsoft documentation for further details on remote table access and the required syntax for your specific scenario.

---

References:

• Error "Operation must use an updateable query" When you access Excel through ODBC
• Connection error when using CurrentProject.Connection or CurrentDB.Connection in Access

Answer 2

It does not work, looking in more detail, i can see the librarys that i add to excel in order have correct librarys for my macro when i program the macro. . in place appears complements in microsoft store. i can find the excel menu where i had add those library some month ago.but now when i go to excel complements appears empthy with no librarys.

Answer 3

By libraries, I assume you refer to an ADO library? And somehow, one of those libraries is no longer referenced? Restoring the reference should be pretty straightforward.

I'm still not clear on why you create an ADO connection, but then use an ODBC method to execute the SQL through that connection. Can you elaborate on the intended operation so I can better understand what you expect to have happen?