Trouble connecting and querying in ADO

Question

I am making a .MDB file which include a ms access database and a form made with vb 6. I am using ms access 2000, and I need to connect to both my local database in the MDB, and a remote MS SQL 2005 database.

In the below code, I can use a msgbox to display the value return from the result set, but when try to output it in a textBox, e.g: txtStatus.Value = txtStatus.Value & rstRecordSet.Fields(1) & vbCrLf, it just hangs. And the method show in the original example from the tutorial got a method of Debug.Print something, but it turns out didn't do anything which I can see. I mean, VB doesn't have a console panel, where will the print statement goes to?

The code with got error:

    Function Testing()
On Error GoTo Error_Handling
   Dim conConnection As New ADODB.Connection
   Dim cmdCommand As New ADODB.Command
   Dim rstRecordSet As New ADODB.Recordset

   conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
   App.Path & "\" & CurrentDb.Name & ";"
   conConnection.CursorLocation = adUseClient

   With cmdCommand
    .ActiveConnection = conConnection
    .CommandText = "SELECT * FROM Opt_In_Customer_Record;"
    .CommandType = adCmdText
   End With

   With rstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmdCommand
   End With

   If rstRecordSet.EOF = False Then
        rstRecordSet.MoveFirst
        Do
            MsgBox "Record " & rstRecordSet.AbsolutePosition & " " & _
          rstRecordSet.Fields(0).Name & "=" & rstRecordSet.Fields(0) & " " & _
          rstRecordSet.Fields(1).Name & "=" & rstRecordSet.Fields(1)
          rstRecordSet.MoveNext
        Loop Until rstRecordSet.EOF = True
   End If

   conConnection.Close
   Set conConnection = Nothing
   Set cmdCommand = Nothing
   Set rstRecordSet = Nothing

   Exit Function

Error_Handling:
MsgBox "Error during function Testing!"
Exit Function

End Function

Answer 1

I thought it was a joke at the beginning ;-) Anyway I assume you're talking about ADO, as in your title.

Here you can find stuff. This site will help you with the connection strings for different database.
The difference between access and sql server using ADO it is exactly the connection string. I would suggest you to avoid Remote Data Controls cause make your life simpler at the beginning but then you have to struggle with them cause they don't work properly.

This is an example of connection and fetch of data:

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strSql As String

cnn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=m:\testdbSource\testSource.mdb;" & _
    "User Id=admin;Password=;"
cnn.Open

cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "select * from tblSource"
cmd.Execute

Set cmd = Nothing
cnn.Close
Set cnn = Nothing

This sample works for me:

Function Testing()

    On Error GoTo Error_Handling

    Dim MyDb As String
    Dim conConnection As New ADODB.Connection
    Dim cmdCommand As New ADODB.Command
    Dim rstRecordSet As New ADODB.Recordset

    MyDb = "db1.mdb"

    With conConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = App.Path & "\" & MyDb
        .Open
    End With


    With cmdCommand
        .ActiveConnection = conConnection
        .CommandText = "SELECT * FROM Opt_In_Customer_Record;"
        .CommandType = adCmdText
    End With

   With rstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmdCommand
   End With

   Do While Not rstRecordSet.EOF
        MsgBox "Record " & rstRecordSet.AbsolutePosition & " " & _
          rstRecordSet.Fields(0).Name & "=" & rstRecordSet.Fields(0) & " " & _
          rstRecordSet.Fields(1).Name & "=" & rstRecordSet.Fields(1)
          rstRecordSet.MoveNext
   Loop

   conConnection.Close
   Set conConnection = Nothing
   Set cmdCommand = Nothing
   Set rstRecordSet = Nothing

   Exit Function

Error_Handling:
    MsgBox "Error during function Testing!"
    MsgBox Err.Description

End Function