Log off ODBC connection

[hal8000]

I am using the docmd.transferdatabase to import data from a database via and ODBC connection in VBA code. This database has numerous different companies within it. It works fine the first time I run the code. But if you run it again and select a different company from the database it still brings in the first companies data. It is like it is still logged in with the first runs login info. Is there away to force the ODBC connection to close after you have transferred the data. This may be simple but I have yet to find any reference to this anywhere. Thanks in advance for any suggestions.

 

[hwkranger]

an example of my ODBC usage:

Public Const cnProvider As String = "MSDataShape" '"SQLOLEDB"
Public Const cnSecurity As String = "SSPI"
Public Const cnSrvName As String = "XXXXXXXX"
Public Const cnTimeOut As Integer = 900
Public DBCnn As New ADODB.Connection
Public cnDBName As String

Public Function fSetADOConnection()
On Error GoTo cnADOErr

'This is for startup initialization - set the connection database
If Len(cnDBName) < 1 Then
Dim strDbName As String
'format the string
strDbName = CurrentDb.TableDefs(1).Connect
strDbName = Right(strDbName, (Len(strDbName)) - InStr(1, strDbName, "Database", vbTextCompare) - Len("Database"))
strDbName = Left(strDbName, Len(DatabaseNameFormat))
'set the Public connection name to the formated string
cnDBName = strDbName
End If


With DBCnn
.Provider = cnProvider
.ConnectionString = "DATA PROVIDER=SQLOLEDB;"
.CommandTimeout = cnTimeOut
.Properties("Data Source") = cnSrvName
.Properties("Initial Catalog") = cnDBName
.Properties("Integrated Security") = cnSecurity
.Open
End With



Exit Function

cnADOErr:
DBCnn.Close
Resume Next

End Function


When I'm changing between Databases, I always change where my tables are pointing or I change a global named cnDBName.

Essentially, you need to close your ODBC connection and reopen it. the .Close Method of your connection and then reconnect.

I hope this helps.

Be safe,



Randall Vollen
National City Bank Corp.

 

[hal8000]

Yes, I figured that but I don't know how to do that when I am using the transferdatabase method. I don't know what to call the connection. I thought about using a real ADO connection like what you have. But it was a lot more work and I sorry to say I was being lazy.