MySQL database with Desktop Applications

Asked By 470 points N/A Posted on -
qa-featured

Hello,

I'm a software developer who develops both desktop applications and web applications. My preferable database when developing desktop applications would be MS SQL Server. Sometimes I use Sybase SQL Anywhere and MS Access Database.

Recently I heard that MySQL can be used for desktop applications. I only use MySQL for web applications but if it's true that MySQL can be used in desktop environment as well that would be great I can connect both web and desktop applications to MySQL and interact with each other.

I'm using Visual Basic to develop desktop application while I'm using Netbeans for web applications. I'm very excited with the prospect of developing web and desktop applications connected to MySQL database. The prospects are endless and I just can't wait to apply it for the next project if it's true. Unfortunately I can't find the information I need while Googling.

I'm currently using Windows XP 32 bit SP3 with 2 GB RAM installed. Is there any additional steps needed to make MySQL database work with desktop applications? Or any additional files needed to be installed to make it work? I'm open to any ideas and suggestions guys. Looking forward to hearing from you all.

Thanks,

Stan

 …..

SHARE
Best Answer by Harry
Best Answer
Best Answer
Answered By 200 points N/A #106607

MySQL database with Desktop Applications

qa-featured

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.

Answered By 0 points N/A #106609

MySQL database with Desktop Applications

qa-featured
  • To connect the MySQL database through a visual basic application, you have to download and install the MySQL ODBC driver in your computer. In MySQL database you have to create a user and grant permissions to that user.
  • Create user stan@localhost identified by ‘password‘;
  • Grant all privileges on *.* to stan@localhost identified by ‘password’;
  • In the new Visual Basic project, add Microsoft Remote Data Object, from the References in Project menu. Now you can write the VB code to connect the database as follows:

    • Private Sub cmdConnectMySQL_Click()
    • Dim cnMySql As New rdoConnection
    • Dim rdoQry As New rdoQuery
    • Dim rdoRS As rdoResultset
    • cnMySql.CursorDriver = rdUseOdbc
    • cnMySql.Connect = "uid=UserName;pwd=UserPassword;
    • Server=MysqlServerName;" & _
    • "Driver={MySQL ODBC 3.51 Driver};
    • Database=DataBaseName;dsn=;"
    • cnMySql.EstablishConnection

Login/Register to Answer

Related Questions