MS Access ODBC issue

Posted on 2015-02-19
Medium Priority
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 connectionstrings.com 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.
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

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
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

752 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