Sometimes it could happen that you need directly access to a local ODBC database in your Visual Basic project and do to the fact that I wasn’t able to find much on the web about this topic, I decided to post something about it.
In order to do so you need to first import the “data.odbc” and “system.data” and then you have to set the connection string in order to directly access to your database.
In my case the database was on my pc (localhost) the DSN name was “Name” and the credentials were “root” and “password”. Of course you will have to change those parameters in order to match to your ODBC settings (see the string called “ODBCString “)
The example below rapresents a basic connection to a local ODBC database.
Imports System.Data.Odbc Imports System.Data ' End of the imports Private Sub ODBConnect() Try Dim ODBCString = "SERVER=localhost;DSN=Name;UID=root;PWD=password" Dim connection As Odbc.OdbcConnection connection = New Odbc.OdbcConnection(ODBCString) connection.Open() MsgBox("Connected !", vbInformation, "Database") connection.Close() Catch ex As Exception MsgBox("Not connected", vbInformation, "Database") End Try End Sub
You can also do some SQL things on you database if the connection as been successfully finished. To do so you just need to use the command “OdbcCommand” as seen below.
Private Sub ODBConnectErase() Try Dim connection As Odbc.OdbcConnection connection = New Odbc.OdbcConnection(ODBCString) connection.Open() Dim mystring As String = "drop database DATABASE_NAME" Dim command As Odbc.OdbcCommand = New Odbc.OdbcCommand(ODBCString, connection) command.ExecuteNonQuery() MsgBox("Database has been deleted successfully.", vbInformation, "Database") connection.Close() Catch ex As Exception StatusLabelDb.Text = "Cannot delete the database. " & vbCr & ex.Message.ToString End Try End Sub
Important note: alwasy look at your Visual Basic project Architecture because it must be the same of your project settings otherwise you will get an errror like “The specified DSN contains an architecture mismatch between the Driver and Application“.