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

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
petersloveAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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!
petersloveAuthor Commented:
I am using DNS and i just used the control panel to navigate to the ODBC and created a connection.
petersloveAuthor Commented:
i use sql server authentication with username and password with all priviledges on both machines, so it could not be an authentication issue
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As mentioned previously - use the one in Windows\SysWOW64 instead. File name is odbcad32.exe.
petersloveAuthor Commented:
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?
petersloveAuthor Commented:
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
"I am using DNS and I just used the control panel to navigate to the ODBC and created a connection."
Do it again.
Take ALT-PrintScreen's of each step
Paste them in a Word doc.
Post it.

Let's have a look.
petersloveAuthor Commented:
petersloveAuthor Commented:

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.
petersloveAuthor Commented:
i have the connection successfully tested as shown. But opening the app over the network gives same connection error
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.
petersloveAuthor Commented:
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"
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
petersloveAuthor Commented:
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
petersloveAuthor Commented:
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:

Glad you got it sorted!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.