Linked table manager to remote MS SQL server
Posted on 2008-10-22
I have a MS Access front end which is linking to a remote SQL server. Within the forms are many drop down boxes. The combo boxes use the "linked table manager" to link to the look-up tables. Currently this has been set up by using the "linked table manager" to use the ODBC connection. It results in a description like below:
ODBC;Description=Datasource for IMEX at Logan;DRIVER=SQL Server;SERVER=XXX-SERVER;APP=Microsoft Office 3;WSID=THINKPAD; DATABASE=IMEX_HQ; Network=DBMSSOCN; Trusted_Connection=Yes; TABLE=dbo.tblTransfers
But if I try to connect to the new server it fails. I have tried using udl to devise a connection string but this seems to exceed 255 characters:
[Provider=MSDASQL.1;Password=xxxx;Persist Security Info=True;User ID=uuuuuu;Extended Properties="Description=dynxxxxxwebsites_xxxxx;DRIVER=SQL Server;SERVER=18.104.22.168;UID=uuuuuu;PWD=xxxxxxx;APP=Microsoft Data Access Components;WSID=THINKPAD;Network=DBMSSOCN";Initial Catalog=dynxxxxxwebsites_xxxxx
Can anyone suggest how I can link these tables to the remote server. Without having to to include statements in every combo box.
Incidentally the VBA coding behind the forms can use SQLOLEDB.1 to access the data.