MySQL database with Desktop Applications

MySQL database with Desktop Applications

Hi Stan,

As you know, MySQL is the most popular free and open source Relational Database Management System in the world. Surely it can be used with both web based and standalone applications written in common programming languages. First you have to download the latest MySQL setup file from the MySQL site and install the server.

Then configure the server instance as a multifunctional database from the MySQL server instance configuration wizard. After successful configuration, MySQL will run as a windows service. MySQL is not now heavyweight as Oracle, thus it will not eat up your resources. Your computer configuration is quite enough for running MySQL server.

To connect your VB desktop application and MySQL server, we need a 3rd part tool, which commonly called as a connector. For each programming language it will be a different connector. For Visual Basic, the connector is called as MySQL ODBC driver. So you need to find it and install it on your computer. Then you have to create Microsoft Remote Data Object for your Visual Basic project.

1. Go to Project option in the menu bar, then select the references, from there select the Microsoft Remote Data Object from the list.
2. Start a new Visual Basic project and add the Microsoft Remote Data Object – Using the menus select Project > References and then select the Microsoft Remote Data Object from the list. Now then anywhere from your code you can access the MySQL database and execute queries.

• The following sample code will help you more.
• Private Sub connectMySQL_Click().
• First you have to create three variables.
• One for store the connection, one for store the query and one for store the results returned by the query.

Dim connection As New rdoConnection

Dim rs As rdoResultset

Dim query As New rdoQuery

Then you can connect to the MySQL server using connection variable with the MySQL ODBC Driver. You have to provide the username where I mentioned username and password where I mentioned password, for the server and the IP address or the name of the server and the name of the database you are connecting as well.

connection.CursorDriver = rdUseOdbc

connection.Connect = "uid=Username;pwd=Password;server=ServerIpOrName;"

& _"driver={MySQL ODBC 3.52 Driver};database=Database;dsn=;"

connection.EstablishConnection

Now we can test the connection by running a query. For that purpose we are creating a simple query for return the all records of subjects table. The results for the query will be saved in rs Result Set variable.

With query .Name = "selectSubjects"

.SQL = "select * from subjects"

Set.ActiveConnection = connection

Set rs = .OpenResultset(rdOpenKeyset, rdConcurRowVer)

End With

For display the result, we have to iterate through the result set. So we would use a loop for that purpose.

Do Until rs.EOF

With rs

In here you can call the fields of a result set. You can reference a table field as !field

rs.MoveNext

End With

Loop

After using the Result Set, you have to close it. You have to close the connection to the database as well.

rs.Close

connection.Close

End Sub

Hope you will have a better idea about connecting your Visual Basic desktop application to MySQL server. It will be advisable to create a single common database connection code in your program and use the same code whenever possible.