How to connect to MySQL database from Visual Basic 6

Question

I am using visual basic 6. I have a button created which when pressed should display all the entries of the table. I am using following code to connect to MySQL database. I have used the Microsoft Remote Data Services as my reference

code:

Private Sub cmdConnectMySQL_Click()
Dim cnMySql As New rdoConnection
Dim rdoQry  As New rdoQuery
Dim rdoRS   As rdoResultset

  cnMySql.CursorDriver = rdUseOdbc
  cnMySql.Connect = "uid=root;pwd=;
  server=localhost; driver={MySQL ODBC 3.51 Driver};
  database=demo;dsn=;"
  cnMySql.EstablishConnection
  With rdoQry
    .Name = "selectUsers"
    .SQL = "select * from user"
    .RowsetSize = 1
    Set .ActiveConnection = cnMySql
    Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
  End With

  Do Until rdoRS.EOF
    With rdoRS
      rdoRS.MoveNext
    End With
  Loop
  rdoRS.Close
  cnMySql.Close

End Sub

I am not able to connect to the database. How do I connect?

How do you know you the connection is failing? I don't know VB that well, but I'm guessing there's some way to check to see if EstablishConnection fails, and maybe you can get some more information about why...is it that the username/pass isn't found? That the driver/connector is installed/located correctly? Any number of things can result in a query not coming back, you need some more information. — prelic
– prelic, Commented Aug 30, 2011 at 0:35
Would someone be willing to comment the code so I can better understand and hopefully implement it? — Steev43230
– Steev43230, Commented Aug 13, 2024 at 16:22

Eireash · Accepted Answer · 2011-11-22 10:57:21Z

2

Can you try it using ADO instead of RDO?

Add a reference to the Microsoft ActiveX Data Objects 2.8 Library
Set up an ODBC DSN to connect to the database

Then use code something like this

Dim cnConnection As ADODB.Connection
Dim adorsRecordSet As ADODB.Recordset
Dim sDatabase As String
Dim sSQL As String

sDatabase = "NameOfTheMysqlDSN"
sSQL= "Select * From user"

Set cnConnection = New ADODB.Connection
cnConnection.Open sDatabase
Set adorsRecordSet = New ADODB.Recordset

adorsRecordSet.Open sSQL, cnConnection 

Do Until (adorsRecordSet.EOF)
     adorsRecordSet.MoveNext
Loop

answered Nov 22, 2011 at 10:57

Eireash

991 gold badge1 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MarkJ Over a year ago

+1 ADO has to be better than RDO. ADO is still supported by Microsoft, for one thing

Raji Hamidu · Accepted Answer · 2014-03-01 14:12:45Z

' the follwoing code inside module and use adodc

Public Myconn As New ADODB.Connection
Public Recset As New ADODB.Recordset
Public SqlStr As String
Public Function Connectdb()
Set Myconn = New ADODB.Connection
Set Recset = New ADODB.Recordset
Myconn.Open "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties='DRIVER=SQL Server Native Client 10.0;SERVER=.\sqlexpress;Trusted_Connection=Yes;APP=Visual Basic;WSID=YOUNGPROGRAMA;DATABASE=StdB;';Initial Catalog=StdB"
End Function

' the following code inside ur form

Private Sub Command1_Click()

        Connectdb
         SqlStr = "Insert into Logintb  values('" + Text1.Text + "', '" + Text2.Text + "'  )"

        Recset.Open SqlStr, Myconn, adOpenKeyset, adLockOptimistic

        MsgBox "New User Added"


        Myconn.Close



    End Sub

Private Sub Form_Load()
Connectdb



With Form1
.Top = (Screen.Height - .Height) / 2
.Left = (Screen.Width - .Width) / 2
End With
End Sub

'it works 
'for verification call Mr. Raji on 08067455933

Collectives™ on Stack Overflow

How to connect to MySQL database from Visual Basic 6

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related