Being able to check for the status of an ODBC driver through a DSN connection using Visual Basic for Applications can save you time by ending your program if the ODBC connection doesn't exist. VBA is used in Microsoft Office applications to automate routine tasks otherwise done manually. In Excel, you can use VBA to automate the process of importing data from an Access database by using an ODBC connection.
- Skill level:
- Moderately Easy
Launch Microsoft Office Excel. Click the "Developer" tab followed by "Visual Basic." Click the "Insert" menu and then "Module" to insert a new module.
Create a new sub procedure:
Private Sub checkODBC()
Create the object variables you will use to check the ODBC driver:
Dim adoCNN As ADODB.Connection Dim can Connect As Boolean Set adoCNN = New ADODB.Connection
Define the ODBC name, login name, and password:
adoCNN.Open "DSN Name", "username", "password"
Check the state of the ODBC driver:
If adoCNN.State = adStateOpen Then can Connect = True adoCNN.Close End If
Display a message box informing the user of the state of the ODBC connection:
If can Connect Then MsgBox "ODBC connection is ready!" Else MsgBox "ODBC connection is not ready!" End If
Press "F5" to run the procedure.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for