SQL query run on VB 6.0

Asked By anonymous 7060 points N/A Posted on - 04/07/2012

I have an Access database and I want to connect it to the VB 6.0 application and then run query to edit, alter , create, select records. How to do this.

• Status: Open
• Question Views: 1276
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: Visual Basic Classic

Best Answer by Allen Kenneth

Go To Solution

Answered By Sheldon Ron 0 points N/A #83158

SQL query run on VB 6.0

 

You are up with a simple set of connecting databases and run SQL queries..

You can run queries using ADCDC, ADODB, ODBC etc, connections.

I shall narrate the use with ADODB connections.

Private Sub cmdXXX_click()

dim con as new ADODB.connection

dim rs as new ADODB.recordset

con.connectionstring = (“ <specify connection string>”)

con.open

con.begintrans

set rs = con.execute(“<SQL Query>”)

con.committrans

End Sub

 

The connection string is a set of the database location, username, password, locks and other parameters separated by colons.

You need to add the references by clicking Project-> references. Check the Microsoft active X 2.1 library and click OK.

 

All ADODB controls get activated on the references feature. 

Thanks.

 

About Sheldon Ron

Questions
3

Answers
52

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 04/07/2012
• Question Category: Visual Basic Classic

Best Answer

Best Answer

Answered By Allen Kenneth 20 points N/A #83160

SQL query run on VB 6.0

Hello,

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!

About Allen Kenneth

Questions
1

Answers
100

Best Answers
74

• Vote Up 0 Vote Down

• Posted on - 12/12/2012
• Question Category: Visual Basic Classic