ODBC Connection w/VB 6.0

[jerschwab]

Hi, this is my first post to this forum! I see there are lots of ODBC experts, so I hope someone can help?

Question 1:
Is there VB code to check if an ODBC connection is available?

Question 2:
Is there code to release an ODBC license after the connection is finished being used?


I am using Access 2000 with Transoft ODBC drivers and the U/SQL Client/Server Administrator.

We have shared licenses and seeing the "(E3015) Duplicate Client License Detected" message box along with a bunch of other error messages is very lame.

Thanks very much! I'm new to this forum, and really hope to contribute and gain a lot from others posts!

- Jeremy



P.S. I've been searching around the Internet and it appears there may be no easy way to do this... I am just looking for a simple piece of code to release the ODBC lock (shutting down the database will release it). I found the following pages, but am not sure if anything there would help:

http://www.devcity.net/forums/faq.asp?fid=44&cat=General

http://www.sql-server-performance.com/bl_asp_ado.asp

Any insights here or gentle shoves in the right direction? 95% of our tables are from an Access back end, but 5% are linked through Transoft ODBC drivers (on our Impact Encore 4.0 ERP system).

THANKS VERY MUCH!!

 

[melakeside]

You can mess around with this code and see if it will work for you.


Option Compare Database
Option Explicit



'In order for the program to run successfully, the ODBC System Data source
'should appear as: Amisys-Live
'
'The error code associated with this problem is 3151. To fix this problem:
'
'Start->Settings->ControlPanel->ODBC Data Sources->System DSN
'If there are any spaces in the data source, click the configure button
'and delete the spaces
'
'



Function RelinkOne()
On Error GoTo ErrMsg:
'Relinks one of the Amisys Attached tables
'which will make Access recognize the current machine's user
Dim db As DATABASE, tdf As TableDef
Dim rst As Recordset
Dim rst2 As Recordset
Dim mytype As String
Dim myname As String
Dim mynewname As String
Dim foreignname As String
Dim sql As String
Dim mymess As String
'Dim sngStart As Single
'Dim sngEnd As Single
'Dim seconds As Single

'sngStart = Timer



Set db = CurrentDb
Set rst = db.OpenRecordset("msysObjects&quot
Set rst2 = db.OpenRecordset("tblMessage&quot

'Loop through System Objects table until finding the first attached table
DoCmd.Hourglass True
rst.MoveFirst
Do Until rst.EOF
mytype = rst![NAME]
'mytype = rst![Type]
If mytype = "c1_c1" Then 'Relink just one table then end program
myname = rst![NAME]
mynewname = myname & "a"
foreignname = rst![foreignname]
Set tdf = db.TableDefs(myname)
tdf.NAME = mynewname
Set tdf = db.CreateTableDef(myname)
tdf.Connect = "ODBC;DSN=AMISYS-LIVE;DATABASE=;;"
tdf.SourceTableName = foreignname
db.TableDefs.Append tdf
sql = "Drop table " & mynewname & ";"
db.Execute sql
DoCmd.Hourglass False
'sngEnd = Timer
'seconds = sngEnd - sngStart
'MsgBox "This program took " & seconds & " seconds"
Exit Function
End If
rst.MoveNext
Loop



DoCmd.Hourglass False
Set db = Nothing


ErrMsg:
' Set tdf = db.TableDefs(mynewname)
' tdf.Name = myname
' mymess = "Error code " & Err & "." & Chr(10) & Chr(13) & Chr(10) & Chr(13)
' mymess = mymess & "There has been an error in relinking the attached Amisys tables. "
' mymess = mymess & "Please call the IM department for assistance."
' MsgBox mymess, vbOKOnly, "Important!"


mymess = "Amisys Link Table Error"

rst2.AddNew
rst2!Date = Date
rst2!Time = Time
rst2!Message = mymess
rst2.Update

DoCmd.OutputTo acOutputTable, "tblMessage", acFormatHTML, "s:\dbs\infomgmt\rfreed\fax\test\mytest.html", -1


DoCmd.Hourglass False
Exit Function