Script to test an ODBC connection?

Posted on 2006-04-24
Last Modified: 2012-06-27
I am not sure if this is the right place to ask but here goes:

I have a user who manually setup a System DSN  ODBC connection for SQL 2000.

I can only as for a name of that connection such as "ODBC_SQL", this name is the DSN configuration for SQL 2000 that he allready stated the server, the user name and password and which database to connect to in the setting.

He is running window 2000.
I know the driver he use is " sun.jdbc.odbc.JdbcOdbcDriver",
I am unsure but the URL can be "jdbc:odbc:ODBC_SQL "

Is there a vb script that I can use to connect to his datbase to test if certain table exists? so that when I run the script, if the table exists then I get a pop up message saying good connection, else saying bad connection?
Question by:fylix0000
    LVL 4

    Assisted Solution

    dim c1 as adodb.connection
    dim rs1 = adodb.recordset

    set c1 = new adodb.connection
    c1.connectionstring = "ODBC=ODBC_SQL"

    set rs1 = new adodb.recordset
    set rs1.activeconnnection = c1

    on error goto errTrap: "SELECT 'Y' FROM table_name"
    'no problem with table or connection

       set rs1.activeconnection = nothing
       set rs1 = nothing

       set c1 = nothing

        'problem with table or connection

    does that help?


    Author Comment

    I save it as test.vbs and run but got the error

    Line: 1
    Char: 8
    Error: Expected end of statement
    Code: 800A0401
    Source: Microsoft VBScript compilation error.

    Sorry if I sound clueless because I am :)
    LVL 8

    Expert Comment

    >>Dim rs1=adodb.recordset

    it should be
     dim rs as adodb.recordset
    LVL 26

    Expert Comment

    if you use vbscript

    you need to declare as just

    dim rs


    dim c1 as adodb.connection

    set c1 = wscript.createobject("adodb.connection")
    LVL 26

    Accepted Solution

    dim c1
    set c1 = wscript.createobject("adodb.connection")

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations.  Recently, I solved an EE question using the LIKE function.  In order for th…
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now