Solved

ODBC connection to "Database_Name" failed Using Access forms, with linked table to SQL Server

Posted on 2014-12-01
20
264 Views
Last Modified: 2014-12-02
using access as a front end with sql server 2008 as the backend database. i get error when i try  connecting on another machine on the network: ODBC connection to "DbName" failed

The connection works fine on my machine. Even when i configured ODBC connection on the other machines on the network, i still get the error. Can't seem to be able to open the application on another machine on the network. Help pplease with ideas
0
Comment
Question by:peterslove
  • 11
  • 6
  • 3
20 Comments
 
LVL 84
Comment Utility
Could be lots of things. Are you sure the correct Drivers are installed on the "other" machine?

Can you open Access on the other machine and create a new connection to that server? If so, can you then link tables from a database on that server?
0
 

Author Comment

by:peterslove
Comment Utility
i can open access on the other machine and link to thesame table as that in the application. But opening the application on the other machine always gives same error.  ODBC connection to " " failed
0
 
LVL 84
Comment Utility
Are you using a DSN to connect? If so, did you create that DSN on the other machine? Note that when doing so, you must use the odbcad32.exe utility found in Windows\SysWOW64, and NOT the control panel applet.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Firewalls can also be problematic.
Are Named Pipes the only permitted connection type?
If they are, only local access will be obtainable

What authentication method?
If you created the SQL Server and are on the machine, you are owner.
Logon a different machine with a different account and you are not.

Many things to check!
0
 

Author Comment

by:peterslove
Comment Utility
I am using DNS and i just used the control panel to navigate to the ODBC and created a connection.
0
 

Author Comment

by:peterslove
Comment Utility
i use sql server authentication with username and password with all priviledges on both machines, so it could not be an authentication issue
0
 
LVL 84
Comment Utility
As mentioned previously - use the one in Windows\SysWOW64 instead. File name is odbcad32.exe.
0
 

Author Comment

by:peterslove
Comment Utility
i just run the odbcad32.exe  found in C:\Windows\System32\odbcad32.exe
since i am using a 32 bit windows XP computer.
User DNS, SQL server for driver. Restarted the application but still same error.
What is it that i cannot run it over the network?
0
 

Author Comment

by:peterslove
Comment Utility
when i open the file and get the ODBC fail error, on closing error box, i get this error message:

runtime error 2467: The expression you entered refers to an object that is closed or doesnt exist
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
"I am using DNS and I just used the control panel to navigate to the ODBC and created a connection."
Ok,
Do it again.
Take ALT-PrintScreen's of each step
Paste them in a Word doc.
Post it.

Let's have a look.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:peterslove
Comment Utility
I HAVE HERE ATTACHED A SCREENSHOT OF HOW I CONNECTED MY LINKED TABLES
Opening-Access-Form-with-linked-table.do
0
 

Author Comment

by:peterslove
Comment Utility
I HAVE HERE ATTACHED A SCREENSHOT OF HOW I CONNECTED MY LINKED TABLES PDF FILE
Opening-Access-Form-with-linked-table.pd
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Ok,

I always create a File DSN
Choose the latest version of the SQL Native Client you have installed (I have 11.0, you appear to have 10.0)
Walk through that.
One dialog, the final one -- which you HAVEN'T included -- has a test data source... button
Clicking it posts up results of the test.
Does it succeed?

Post screenshots from ALL the dialogs -- and I get that you want to edit out some sensitive stuff.
Do that, but mark clearly where you've done it.
0
 

Author Comment

by:peterslove
Comment Utility
i have the connection successfully tested as shown. But opening the app over the network gives same connection error
Opening-Access-Form-with-linked-table.pd
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
If you have a successful connection, the next step is to use the Linked Table Manager to ensure that all your linked tables are using it.
Any passthrough queries will also need to be updated to use the successful DSN, and anywhere in code where you have used a connection string (ADO code) that will also need updating.
0
 

Author Comment

by:peterslove
Comment Utility
i have just one linked table. And i even use to link manager to update the table and also check the box "Always prompt for new location"
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
So, does the Linked Table Manager prompt for a new location
Do you navigate to the File DSN you created
Does it give the "All selected linked tables were successfully refreshed" message box?
After, can you open the linked table?
0
 

Author Comment

by:peterslove
Comment Utility
I got it, i think i just needed to use the Linked Table Manager and make sure all the tables were properly liked and refreshed. Thank you
0
 

Author Comment

by:peterslove
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for peterslove's comment #a40476486
Assisted answer: 500 points for Nick67's comment #a40474989

for the following reason:

works
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Glad you got it sorted!

Nick67
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now