Steven Deetz
Registered User.
- Local time
- Today, 14:16
- Joined
- Jul 19, 2001
- Messages
- 49
I have a client who has an Access application where there are several linked ODBC tables from an SQL Server through a DSN that connects to the SQL Server through an IP address. I can manually use the DSN and enter the User ID and Password to see the list of tables and link the ones I want. I then manually run a delete query and update query to refresh the information in the selected tables.
This link needs to be refreshed before every update or the delete and update queries will fail. I want to programatically delete and recreate the link before each update but I always seem to get a failed connection to the SQL Server on the web.
After manually creating the link I created a simple bit of code to list the tabledef name and tabledef attributes of the web tables. I have tried various assortments of this information in the connection string. I am using DAO and can create a DSN-less auto linkage for SQL Server tables on a local server. I have tried the DSN-less verbiage and the two below strings to create a connection but non of these attempts work. I am wondering if anyone has some suggestions on the connection strings, etc? Thanks in advance!
'First connection string attempt
strConnect= "ODBC;DSN="DSNName";SERVER=IPAddress;UID=UserName;PWD=ValidPassword"
'Second connection string attempt
strConnect = "OCBC;DSN=DSNName";SERVER=IPAddress;Description=Connect to web tables;APP=Microsoft® Access;WSID=WorkstationName;DATABASE=DataFileName;Network=DBMSSOCN;UID=UserName;PWD=ValidPassword;Trusted_Connection=No"
This link needs to be refreshed before every update or the delete and update queries will fail. I want to programatically delete and recreate the link before each update but I always seem to get a failed connection to the SQL Server on the web.
After manually creating the link I created a simple bit of code to list the tabledef name and tabledef attributes of the web tables. I have tried various assortments of this information in the connection string. I am using DAO and can create a DSN-less auto linkage for SQL Server tables on a local server. I have tried the DSN-less verbiage and the two below strings to create a connection but non of these attempts work. I am wondering if anyone has some suggestions on the connection strings, etc? Thanks in advance!
'First connection string attempt
strConnect= "ODBC;DSN="DSNName";SERVER=IPAddress;UID=UserName;PWD=ValidPassword"
'Second connection string attempt
strConnect = "OCBC;DSN=DSNName";SERVER=IPAddress;Description=Connect to web tables;APP=Microsoft® Access;WSID=WorkstationName;DATABASE=DataFileName;Network=DBMSSOCN;UID=UserName;PWD=ValidPassword;Trusted_Connection=No"
