ODBC connection with Visual Basic

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“.

Stay Tuned

Marco Dalprato

Sono un blogger, uno sviluppatore e un web designer Italiano. Ho iniziato a programmare fin da piccolo e ad oggi ho realizzato numerose applicazioni per iOS, siti web e articoli tecnici. Sono esperto nel web design, nella programmazione Swift (iOS), Visual Basic, HTML, CSS, Javacript e PHP. Ogni tanto scrivo anche qualche articolo tecnico e qualche guida sulle ultime novità in ambito informatico.

Rispondi

Torna in alto