Solved

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

Posted on 2014-12-01
20
279 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
[X]
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
  • 11
  • 6
  • 3
20 Comments
 
LVL 84
ID: 40474277
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
ID: 40474294
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
ID: 40474301
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
Independent Software Vendors: 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!

 
LVL 26

Expert Comment

by:Nick67
ID: 40474308
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
ID: 40474309
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
ID: 40474312
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
ID: 40474318
As mentioned previously - use the one in Windows\SysWOW64 instead. File name is odbcad32.exe.
0
 

Author Comment

by:peterslove
ID: 40474417
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
ID: 40474452
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
ID: 40474466
"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
 

Author Comment

by:peterslove
ID: 40474510
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
ID: 40474516
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
ID: 40474557
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
ID: 40474585
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
ID: 40474599
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
ID: 40474794
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
ID: 40474989
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
ID: 40476486
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
ID: 40476623
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
ID: 40476501
Glad you got it sorted!

Nick67
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

749 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