VBScript test ODBC connection

[pgriffin]

I need to take the ODBC name, table name from a text string and do nothing more than test that the ODBC connection is there and the table name is valid.

I'm searching EVERYWHERE. I am using MacroScript to call the VBScript and to take in the text string...

Anyone have some VBScript they are willing share? I may resort to opening the database (good or bad) and then trying to GetNextRecord....

[Marcus Tettmar]

You could do this:

Code: Select all

VBSTART

Function TestODBC

On Error Resume Next

    TestODBC = 0
    Set DB = CreateObject("ADODB.Connection")
    DB.Open "Provider = MicroSoft.JET.OLEDB.4.0; Data Source=c:\DB\DB.mdb;"
    If Err then
        MsgBox "Error: " & Err.Number & " - " & Err.Description
        TestODBC = 1
    Else
        SQL = "Select * FROM MyTable"
        Set RS = DB.Execute(SQL)
        If Err then
            MsgBox "Error: " & Err.Number & " - " & Err.Description
            TestODBC = 2
        End If
    End if

End Function

VBEND

VBEval>TestODBC,r

You could be more specific by querying the value of Err.Number or Err.Description. You can probably find a list of error codes somewhere.

[pgriffin]

Thanks Marcus,

I will modify the code to send the odbc name and table name to the VBScript routine, then I'll repost in case anyone else is interested.

btw, I always use this link for finding error codes...

http://msdn.microsoft.com/library/defau ... 0-499_.asp

[pgriffin]

Ok, here is my final version. If anyone wants to use this and have the function return the actual error number, just enable the "TestODBC = err.number" lines.

Of course, you could get very advanced here and select specific column names, etc...but my intention is only to tell the user that the connection information they entered works or does not work.

Code: Select all

VBSTART

Function TestODBC(ODBCname,TableName)
dim SQLString
dim DB
dim RS

On Error Resume Next

    TestODBC = 0
    Set DB = CreateObject("ADODB.Connection")
	on error resume next
    DB.Open "DSN=" & ODBCname
    If Err then
        TestODBC = 1
		'TestODBC = err.number
    Else
        SQLString = "Select * from " & TableName
		on error resume next
        Set RS = DB.Execute(SQLString)
        If Err then
            TestODBC = 2
			'TestODBC = err.number
        End If
    End if

End Function

VBEND
VBEval>TestODBC("%ODBCname%","%TableName%"),dbresult
label>end