Drop Connection to SQL Server 1

[itsmarkdavies]

I am using Access 2000 to link tables from SQL Server. The tables are linked using ODBC DSN`s. I am running a query to return some rows to Access, but once I have returned the rows, I want to drop the linked table connection to SQL Server, because it is locking a table on the server. I think I need to write a bit of VBA code, but I don`t know where to start. Any ideas would be greatly appreciated. Thanks.

 

[vickvickvi]

hello , I did add one line to this code to delete the connection. Here you will find the code to add a ODBC table connection to DSN. DSN connects to SQL Server, Oracle, etc. As long as the DSN is setup correctly, it will work. You can cut and pasted the below code into your code

good luck


Function LinkFromDNSConnection(Optional StrLocation As String = StrSQLServerConnect, Optional StrSourceTable As String = "CMDATA_Invalid_Records_Pass1", Optional StrDestinationTable As String = "CMDATA_Invalid_Records_Pass1&quot
'***********************************************************
'*
'* Purpose : Link Tables from SQL Server to current database
'* StrLocation = Connection String
'* StrSourceTable = Table name in the DSN location
'* StrDestinationTable = Table name that access is going to name it.

'*Global Const StrSQLServerConnect As String = "ODBC;DATABASE=PropertyListing;" & _
"UID=LinknPropertyMaster;" & _
"PWD=LinknPropertyMaster;" & _
"DSN=LinknPropertyMaster"

'***********************************************************

Dim db As Database
Dim td As TableDef 'Holds the table definition

Set db = CurrentDb
Set td = db.CreateTableDef(StrDestinationTable)
td.Connect = StrOracleConnect
td.SourceTableName = StrSourceTable

'td.SourceTableName = "cmdata_199911"
db.TableDefs.Append td
db.TableDefs.Delete td


End Function

 

[BayouTech]

The post by vickvickvi was helpful and does work. But What I would like to know is how do I send the connect string to a SQL Server when the tables are already linked in the front-end. This method would require that I connect and delete every table when the user uses the system but if I have multiple users creating the table will make multiple tables of the same item and deleting may result in users getting errors because another user got out the system and deleted the tables that are linked. What would you suggest to prevent this.