Solved

ODBC with Access Word Merge Issues

Posted on 2010-09-16
6
687 Views
Last Modified: 2013-12-25
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
Comment
Question by:browe68
[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
  • 4
  • 2
6 Comments
 
LVL 13

Expert Comment

by:Surone1
ID: 33695207
sql native client is probably only for the local machine..
is the server the only machine that works?
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33695258
"Driver={SQL Server};Server=machine\instance;Database=dbname;Uid=sa;Pwd=password;"
for ado connections something like this works in my case..
0
 

Author Comment

by:browe68
ID: 33695302
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 13

Expert Comment

by:Surone1
ID: 33695379
oops, yeah i oversaw that "detail"
but it only takes one upgrade to see if it works then.
0
 

Author Comment

by:browe68
ID: 33695894
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
 
LVL 13

Accepted Solution

by:
Surone1 earned 500 total points
ID: 33695939
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

623 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