Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 701
  • Last Modified:

ODBC with Access Word Merge Issues

I am trying to understand the Link Database process in Access.

I have an Access 2007 database with Linked Tables to SQL Server 2008.

The issue I have is when I perform the link from my development machine via ODBC other users cannot open the linked tables from from their machine.  When I perform the link in ACCESS from someone else machine, others are able to open the tables just fine.

So, I am doing something wrong on my machine OR have some version of ODBC installed that won't translate correctly.

The only difference I can see is the following:  When looking at the 'tool tip' on the Access linked table there are the connection properties...

Works:
ODBC; DRIVER=SQL SERVER; SERVER=MYSERVER; UID=BOB;APP=Microsoft Data Access Components;..........

In the ODBC Create New Data Source, Workstation is using SQL Client 2000.85.1117.00   (FILE: SQLSRV32.dll)

Doesn't Work:
ODBC; DRIVER=SQL SERVER; SERVER=MYSERVER; UID=BOB;APP=Microsoft Windows Operating System;.........

OR

ODBC; DRIVER=SQL SERVER; SERVER=MYSERVER; UID=BOB;APP=2007 Microsoft Office System;.........

In the ODBC Create New Data Source, Workstation is using SQL Server Native Client 2009.100.1600.01   (FILE: SQLNCLI10.dll)

I am not sure how to affect/change the APP section during the ODBC Link Process in Access.

OR do I need to install the SQL Native Client on every workstation?  If so, how do I do that?

Can anyone provide some insight?
0
browe68
Asked:
browe68
  • 4
  • 2
1 Solution
 
Surone1Commented:
sql native client is probably only for the local machine..
is the server the only machine that works?
0
 
Surone1Commented:
"Driver={SQL Server};Server=machine\instance;Database=dbname;Uid=sa;Pwd=password;"
for ado connections something like this works in my case..
0
 
browe68Author Commented:
If I link tables from the development workstation that has the SQL Native Client, that workstation operates fine and can open up the SQL Server (linked) tables in Access.

However, none of the other machines can.   So, I am guessing that I need to distribute the SQL Native Client 10 to everyone.

If I perform the links from a different workstation that has an earlier Client = (SQL Client 2000.85.1117.00   (FILE: SQLSRV32.dll), then all the workstation can open the SQL Server Tables (linked).   I assume all workstations (over 100) have this client on their system.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Surone1Commented:
oops, yeah i oversaw that "detail"
but it only takes one upgrade to see if it works then.
0
 
browe68Author Commented:
The solution is to upgrade all clients to SQL Native Client 10.   I am wondering if there is an easy way to do this?
0
 
Surone1Commented:
there may be if all of the client machines are part of the same domain, but that would be a question for other zones i guess..
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now