HOWTO: Use "DSN-Less" ODBC Connections with RDO

• Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 5.0

SUMMARY

With Microsoft Visual Basic versions listed above, you can specify your ODBC driver and server in your connect string when using RDO (Remote Data Objects) and DAO (Data Access Objects). This eliminates the need to set up a DSN (Data Source Name). This is called a "DSN-Less" ODBC connection because you do not need to set up a DSN in order to access your ODBC database server.

To do this, you specify a "driver=" parameter in your connect property. The following three examples show how this is done with the SQL Server, Access, and Oracle ODBC drivers:

   'Microsoft SQL Server ODBC Driver example
    cnstr = "driver={SQL Server};server=myserver;" & _
      "database=pubs;uid=sa;pwd="
   cn.Connect = cnstr

   'Microsoft Access ODBC Driver example
   cnstr = "Driver={Microsoft Access Driver (*.mdb)};" & _
     "Dbq=c:\program files\devstudio\vb\biblio.mdb;" & _
     "Uid=Admin; Pwd="
   cn.Connect = cnstr

   'Microsoft ODBC Driver for Oracle example
   cnstr = "Driver={Microsoft ODBC Driver for Oracle};" & _
     "ConnectString=OracleServer.world; Uid=demo; Pwd=demo"
   cn.Connect = cnstr

   "{SQL Server}"

   The connect string contains a series of semi-colon-delimited
   arguments as defined by the ODBC interface — including the
   ODBC driver itself. That is, all ODBC drivers have specific
   argument requirements so you should consult the documentation
   included with the driver for specific information. This
   connect string is passed to the ODBC API SQLDriverConnect
   function along with the hEnv for the associated rdoEnvironment
   object.

MORE INFORMATION

If you do want to set up a DSN, you can use the following methods:

• Manually with the ODBC Admin utility(Odbcad32.exe).
• Through code with the RDO rdoRegisterDataSource method.
• Through code with the DAO RegisterDatabase method.
• Through code with the ODBC API SQLConfigDatasource API function.

Sample Program

The following RDO example uses a "DSN-less" ODBC connection so you do not need to set up a DSN with the ODBC Admin utility beforehand.

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a CommandButton to Form1, Command1 by default.

3. Paste the following code into the code window of Form1:

         Private Sub Command1_Click()
           Dim Cn As New rdoConnection           'creatable rdoConnection
           Dim Qr As New rdoQuery                'creatable rdoQuery
           Dim Rs As rdoResultset                'pointer to rdoResultset
           Dim cnstr As String                   'hold connection info
   
           cnstr = "driver={SQL Server};server=myserver;" & _
             "database=pubs;uid=sa;pwd="
           Cn.Connect = cnstr
   
           Cn.CursorDriver = rdUseClientBatch
           Cn.EstablishConnection Prompt:=rdDriverNoPrompt
           Set Qr.ActiveConnection = Cn
           Qr.SQL = "Select * From Authors"
           Set Rs = Qr.OpenResultset(Type:=rdOpenKeyset, _
             LockType:=rdConcurBatch)
           Debug.Print Rs(0), Rs(1), Rs(2)
         End Sub
   
   

4. Note that you must change your DRIVER, SERVER, DATABASE, UID, and PWD parameters in the Connect method. You also need to modify the SQL statement contained in the Command1_Click event to match your own SQL data source.

5. Start the program or press the F5 key.

6. Click the Command1 button to create an rdoResultset and display the first row of data in the debug window.

REFERENCES

In Visual Basic Books Online see:

  Guide to Building Client Server Applications in Visual Basic (Enterprise)
    Part 3: Data Access Options
      Using Remote Data Objects and the Remote Data Control
        Establishing an RDO Connection
          Providing Connection Strings to RDO

Keywords          : APrgDataODBC vb432 VB4WIN vb5all vb5howto VBKBODBC VBKBRDO kbhowto
Version           : 5.0
Platform          : WINDOWS
Issue type        : kbhowto