Trust SQL split tables in 2007

Topic

New Reply

Hi,

I have a database that links to sql tables that reside in sql server express on the user’s server. All was working fine with the database until they upgraded to 2007 on one of the computers. Now I keep getting OBDC errors. I’ve tested the OBDC connection and it works fine from Conntrol panel however whenever I try to open the database I get OBDC error. I’ve “trusted” the database etc. however do not know how to actually trust the sql tables that are on the server. Their IT engineer doesn’t have a clue as to what I am talking about. I’m not even sure I’m on the write path with this. All the other workstations are working fine. Just this one in 2007.

Thanks,
Leesha

Reply | Quote

Replies

What version of SQL Server Express are you using? 2008, 2005 or 2000? And you may need to set permissions in the SQL Server side for the userID being used in the ODBC connection. And you don’t have to set trusted locations for ODBC linked tables in SQL Server. Also are you using the same front-end for all of the workstations?

Reply | Quote

What version of SQL Server Express are you using? 2008, 2005 or 2000?

The are using 2005.

>>And you may need to set permissions in the SQL Server side for the userID being used in the ODBC connection.

I haven’t had to do this for any of the other 3 users. The other users are in Access 2002 which is working fine. The are all using the same front-end for each workstation. The only difference between this workstation and the others is the Access version = 2007. I’ve tested the ODBC connection in Control panel and the test is successful. It’s not until I try to open the access database that I run into issues.

Leesha

Reply | Quote

OK – we haven’t seen any issues using either SQL Server 2005 or 2008 with Access 2007 using ODBC linked tables, but one possible issue is the Native Mode SQL Server driver for ODBC tables. We have seen some issues if you try to use the standard SQL Server driver with 2007. I would recommend the Microsoft SQL Server 2008 Native Client – you can download it from that link – it appears about half way down that web page and the file is called sqlncli.msi – you will want the X86 version (unless the workstation is running a 64-bit version of Windows, which is unlikely).

EDITED – you will need to recreate the ODBC data source using the SQL Server Native Client 10.0 driver.

The other thing I would try is to open the database while holding down the shift key and then check to see if you can open any of the ODBC linked tables – it is possible you have an issue with one or a few of the tables rather than all of them. You might try the linked table manager – it is on the Database Tools ribbon in 2007. Another possibility to check is to make sure the DNS name for the data source is exactly the same as what your front-end is looking for.

And finally, I would make sure that both SP1 and SP2 of Office 2007 have been installed. Hope this helps – problems like this can be really frustrating to troubleshoot!

Reply | Quote

Thanks! At least I have something to try!!

Leesha

Reply | Quote