How To Use "DSN-Less" ODBC Connections with RDO and DAO

Sample Program

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add a command button to Form1, Command1 by default.
3. Paste the following code into the General Declarations section of Form1.
   
   Note You must change Username= <username> and PWD =<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

         Dim en As rdoEnvironment
         Dim cn As rdoConnection
   
         Private Sub Form_Load()
           MousePointer = vbHourglass
           Dim strConnect As String
           ' Change the next line to reflect your driver and server.
           strConnect = "driver={SQL Server};server=jonfo5;" & _
             "database=pubs;Username=<username>;PWD=<strong password>;"
           Set en = rdoEngine.rdoEnvironments(0)
           Set cn = en.OpenConnection( _
             dsName:="", _
             Prompt:=rdDriverNoPrompt, _
             ReadOnly:=False, _
             Connect:=strConnect)
           cn.QueryTimeout = 600
           MousePointer = vbNormal
         End Sub
         Private Sub Command1_Click()
           MousePointer = vbHourglass
           Dim rs As rdoResultset
           Set rs = cn.OpenResultset(Name:="Select * from authors", _
             Type:=rdOpenForwardOnly, _
             LockType:=rdConcurReadOnly, _
             Options:=rdExecDirect)
           Debug.Print rs(0), rs(1), rs(2)
           MousePointer = vbNormal
         End Sub
   
   						

4. Note that you must change your DRIVER, SERVER, DATABASE, UID, and PWD parameters in the OpenConnection method. You also need to modify the SQL statement contained in the Command1_Click event to match your own SQL data source.
5. Check the Microsoft Remote Data Object in the Project References.
6. Start the program or press the F5 key.
7. Click the Command1 button to create a rdoResultset and display the first row of data in the debug window.