ODBC Connection in VBA to SQL Server throwing an error but tables can open

Question

This is a strange problem I'm having. I have an Access database that opens an ODBC connection to SQL Server in VBA and creates a query, which is written to an Excel file. This works fine on my machine but when other users run it, they get an error saying that a table is missing that is in the SQL syntax that I'm using. The table is not missing. This is the same database that works fine on my machine. If I link the tables to the database, the users can open them and add and edit data, it's just that it's throwing an error when trying to open the recordset in VBA. Any idea on what could be causing this? It's not a driver issue, they have those and I can't imagine it being any type of security setting issue because they can access the records through linked tables.

Any assistance would be appreciated.

Answer 1

I'll guess that the problem is with your connection string and that your code is connecting to the wrong database.  Either that or your table is not in the schema expected by your connection.  You can use the profiler tool to observe how your app interacts with the server.  That will show you which database your app actually connects to and how it qualifies the table with the schema name (if any). 

Answer 2

Thanks but that's not it. As I said, the same database works fine on my machine. No connection issues or anything like that. It's only on other users machines that the problem is occurring. I am using the same connection string for the tables as I am for the ODBC call in VBA. No issue whatsoever with the tables, the error occurs when it tries to open the SQL recordset. Any othe ideas on what it might be?

Answer 3

Hello,

Please, could you tell us for both computers ?

- the full operating system

- the full version ( 2005,2008,2008 R2,2012 + last installed service pack ) and edition of your SQL Server

- the full version and manufacturer of your ODBC provider

- the code about the open the SQL recordset

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

PS : a problem difficult to simulate on a remote computer : authorizations not provided to a schema,table or view for a Windows user.

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.