ODBC Connection Issue (1 Viewer)

Hi all,

I am having an issue with an ODBC connection from an Access 2003 database to an SQL Server database.

Previously we linked to an .mdb copy of the SQL database as we were unable to set up a read-only connection. We have just managed to find a way of linking which gives us read-only access so we are trying to link to the live database.

I linked the tables yesterday and tested them and they seemed to work fine, however this morning when I have tried to run the queries attached to the tables I am getting the following error:

Could not execute query; could not find linked table.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Cost'. (#208)

If I hover over the table it still gives the correct connection path, and if I go to the linked table manager it updates the table, however if I try to open the table I get another error:

Microsoft Access can't open the table in Datasheet view.

I've had a look on various forums and have found discussions regarding different types of DSN but I'm a novice at this so what I found so far doesn't make a lot of sense to me!

Any help anyone can give me would be greatly appreciated.

Many thanks in advance.

What version of SQL Server? You may not be using the correct driver (and that can be dependent upon which version of Windows you are using as well).

The generic SQL Server driver should only be used if you have

Windows XP -----> SQL Server 2000
Windows Vista ----> SQL Server 2000 or 2005
Windows 7 --------> SQL Server 2000, 2005 or 2008
Windows 8 --------> SQL Server 2000, 2005, 2008, or 2012

Otherwise you need to use the

SQL Native Client driver for SQL Server 2005
SQL Native Client 10 driver for SQL Server 2008
SQL Native Client 2012 driver for SQL Server 2012

That is, if you are using certain datatypes in the SQL Server which Access does not natively support. A lot of times you can use the generic SQL Server driver for any of them but it really depends on how the SQL Server tables/fields are set up.

Hi SOS,

Many thanks for your reply, however I think I have fixed it. I re-linked all of the tables ticking the 'Save Password' box and it seems to have worked!

Thanks again