Improve company productivity with a Business Account.Sign Up

x
?
SolvedPrivate

MS Access ODBC issue

Posted on 2015-02-19
9
Medium Priority
?
27 Views
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.
0
Comment
Question by:StampIT
8 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40620383
Yes, it certainly could be the connection string. Check out connectionstrings.com for suggested connection strings to various back-end systems.
0
 
LVL 23

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.


Kelvin
0
 

Author Comment

by:StampIT
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.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 23

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?


Kelvin
0
 
LVL 50

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

Accepted Solution

by:
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.
0
 
LVL 23

Expert Comment

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


Kelvin
0
 

Author Comment

by:StampIT
ID: 40623139
Kelvin,

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

606 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