MS Access ODBC issue

Posted on 2015-02-19
Last Modified: 2016-05-22
Tried to run a report in an Access database. The result was an ODBC call failed. The report data source is a linked SQL table. The ODBC bridge tests successfully. Could the issue be the connection string ? The SQL tables were linked on a different machine . Both machines are using a DSN that is using Windows authentication. Thanks.
Question by:StampIT
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 14

Expert Comment

ID: 40620383
Yes, it certainly could be the connection string. Check out for suggested connection strings to various back-end systems.
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40620387
Can you open the tables by themselves?
Are there views that may not have relinked. Is there any connection strings stored in VBA that need amendment?
Did the report work on the original machine that the relinking was done on?

Generally, as long as the server name and database name are the same from all locations, it shouldn't matter what machine it was linked from.


Author Comment

ID: 40620397
I neglected to mention that the machine I'm trying to run this on is using a  run time version of Access not a full version so I cannot open tables.

No views. No VBA connection strings.

The report does work on the original machine.
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40620503
Using a runtime shouldn't make any difference. I regularly ship databases to be used in runtimes, connected to SQL Server without any issue. The only difference here is I use DSN-less connections - to save issues installing DSN's. At the end of the day though, they are both ODBC connections.

Have you tested locally in a runtime?

LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40620941
The other thing you might be experiencing is a timeout.

Is the report based on a table or a query?  If a Query, you might need to change the ODBC timeout from the default (usually 60) to some other value.  Zero (0) will allow the query to run until completion, regardless of how long it takes.

Accepted Solution

StampIT earned 0 total points
ID: 40621469
The issue has been resolved. I did not know it at the time but I had installed the DNS and the database on an XP computer. When I installed on a Windows 7 machine every thing worked fine. Not sure how to handle this. Should I delete the question ?

Thanks for all the help.
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40622440
An XP computer should not have made any difference (assuming it was SP3)


Author Comment

ID: 40623139

The operating system is XP SP3. All I can tell you is it did not work on XP and worked on Windows 7. Could it have something to do with version of Access runtime ? Thanks.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question