SQL query run on VB 6.0

Below mentioned is a method that can be used to connect to an Access database using ADOdb. Follow this initial steps before working on the code.

 

First, in the Project menu, choose 'References' and check the 'Microsoft ActiveX Data Objects 2.x Library'. Also select 'Microsoft ActiveX Data Objects 2.x Recordset Library

2.6 works fine.

 

Also choose the installed version of 'Microsoft Data Access Components' 

Now work on the code:
 

---

Sub Get_Accountno()

    Dim Conn as ADODB.Connection 

    Dim Rs as ADODB.Recordset     

    Dim strQuery As String       

 

Set Conn = New ADODB.Connection

  With Conn

  .CursorLocation = adUseClient

  .ConnectionString= "Provider=Microsoft.Jet.OLEDB.4.0;" & _

           "Data Source=C:MyPathmyDatabase.mdb"

  .Open

  End With

   

strQuery = "SELECT Accountno FROM tblTable " & _

"WHERE  Username = 'BillK'"

   

Set Rs = New ADODB.Recordset

    With Rs

    Set .ActiveConnection = Conn

        .CursorType = adOpenStatic

        .Source = strQuery

        .Open

    End With

    

Set txtAccountno.DataSource = Rs

    With txtAccountno

        .DataField = "Accountno"

        .Refresh

    End With

    

   Rs.Close

   Set Rs = Nothing

   Conn.Close

   Set Conn = Nothing

End Sub

---

Hope this would be helpful. Regards!