Question linked table manager hopeless

[oli82]

Hi,

I have mdb frontend, SQL backend, I am trying to update a
linked tables in linked table manager, i select all the
tables, click prompt for new location and then press ok.
When I used access 2007 I get the error 'ODBC--call failed' and I cannot
even select the dsn I want to change it too. While in 2003 I have been
able to select another dsn with no error.

It works fine with Access 2003 just access 2007.

Any help would be great, shall i try to force a relink by VBA?Cheers,

Oliver

 

[boblarson]

What version of SQL Server and what ODBC driver are you using?

 

[oli82]

SQL server 2005

how do i find the odbc driver?

odbc file dsn is this:
[ODBC]
DRIVER=SQL Server
UID=oliverm
Trusted_Connection=Yes
DATABASE=Team
WSID=DELLLAT7
APP=2007 Microsoft Office system
SERVER=ESRISDE\development

 

[boblarson]

SQL server 2005

how do i find the odbc driver?

odbc file dsn is this:
[ODBC]
DRIVER=SQL Server
UID=oliverm
Trusted_Connection=Yes
DATABASE=Team
WSID=DELLLAT7
APP=2007 Microsoft Office system
SERVER=ESRISDE\development

For 2005, unless you are on Windows Vista, you need to use the SQL Native Client driver. So I think you could just replace
DRIVER=SQL Server

with

DRIVER = SQL Native Client

 

[oli82]

Thanks for the help, i tried to connect but it wouldnt happen the users laptop does not have the SQL native client driver in the odbc driver list.

There is only
SQL server version 2000.85.1132.00 SQLSEV32.dll date 14/4/08

But I cant even select the dsn, the window that should popup (select data source) after i click ok does not appear.

Any ideas.

Many many thanks

 

[boblarson]

You will need to install the driver on the user's machine:

http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi

 

[oli82]

bob whats the reason for swapping to the native client - is it usually installed as standard.

Why does this make a difference, I am not a sceptic I just want to understand!

Cheers,

Oliver

 

[boblarson]

bob whats the reason for swapping to the native client - is it usually installed as standard.

Why does this make a difference, I am not a sceptic I just want to understand!

Cheers,

Oliver

SQL Server 2005 is a completely different system than 2000 (from what I understand it has a lot of structural changes). So you have to use a driver designed specifically for it. You can't use a driver for an older version because that driver doesn't have a clue as to what it needs to make the newer version work.

The reason you just use the normal SQL Server driver on Windows Vista is that in Windows Vista, the new functionality of the SQL Native Client is included in the SQL Server driver and therefore you don't need the other one anymore.

 

[oli82]

sorry this still hasnt worked i cant even get to the point where i have the ability to select a dsn file, i just click ok and there is an error ODBC call failed. It appears there is no way out of it, its only on 2007 and its on more than one laptop. I am running SQL server 2005
Your help is really appreciated.

 

[oli82]

[FONT=&quot]

[/FONT]

 

[boblarson]

[FONT=&quot]

[/FONT]

This didn't give a clip as it is on your computer not on a web available server. Just use the attachment feature to attach the jpg.

 

[oli82]

see picture i dont understand it