Microsoft Access Database Development With VBA

Microsoft Windows Data Sources

 

The Data Source of an Application

 

Introduction

In Microsoft Access, a database is created as a computer file and it has a path, that is, where the database file is located. The path to a file is also known as its location. The path to a database, including its name, is also called the data source. Using the path to a database is probably the easiest way to access it but this is more convenient if you are working in Microsoft Access. If you plan to access your database from another programming environment, one solution is to create an ODBC data source. To do this, in the Control Panel (Start -> Control Panel, System and Security), click Administrative Tools. Double-click Data Source (ODBC) to open the ODBC Data Source Administrator. Click MS Access Database:

ODBC Data Source Administrator

Click Add. You shoud receive a dialog box with many options including Microsoft Access Driver (*.mdb, *.accdb). You may receive a dialog box without Microsoft Access:

Create New Data Source

If that happens, click Cancel. In the ODBC Data Source Administrator, click Coonfigure... You may receive an error message box stating that "The setup routines for the Microsoft Access Driver ...":

Microsoft ODBC Administrator

If you receive such an error, close the ODBC Data Source Administrator dialog box and download an application from the Microsoft website. At the time of this writing, the file can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=13255 and installed on your computer. After installing the application, restart the ODBC Data Source Administrator. Click the Add button. In the Create New Data Source wizard, click Microsoft Access Driver (*.mdb, *.accdb):

Create New Data Source

Click Finish. In the following screen, you would be asked to enter a name for the data source. You can enter the name in one or more words. In the Description text box, you can enter a short sentence anyway you like. Here is an example:

ODBC Microsoft Access Setup

TTo specify the database that would be used, click Select. If the database is located on a local drive, select it. Here is an example:

Select Database

 
 
 

If the database is located in a shared drive of a computer connected to the one you are using, click Network, locate, and select the computer. After selecting the database, click OK. If you need to be authenticated in order to use the database (if the database is protected), click the Advanced button. By default, a database is meant to allow anybody to use. In this case, you can leave the Login Name and the Password empty. Otherwise, type the necessary credentials. Here is an example:

Data Source

After using (that is, if you had used) the Set Advanced Options dialog box, click OK (or Cancel to keep it the way it previously was):

ODBC Microsoft Access Setup

After entering the necessary information and selecting the desired database, you can click OK. Your new data source should appear in the list of User Data Sources:

ODBC Microsoft Access Setup

Click OK to close the ODBC Data Source Administrator dialog box.

Using an ODBC Data Source

To use an ODBC data source to open a database, programmatically create an ADO connection. When opening, include the data source. Here is an example:

Private Sub cmdDataSource_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    
    connector.Open "DSN=gdcs;UID=;PWD=;"

    Set connector = Nothing
End Sub

After opening the ADO connection and specifying its data source, you can perform any of the routine operations we have seen in ADO so far. Here is an example of creating a table and adding records to it:

Private Sub cmdCreateEmployees_Click()
    Dim dbConnection As ADODB.Connection

    Set dbConnection = New ADODB.Connection

    dbConnection.Open "DSN=Exercise;UID=;PWD=;"
    dbConnection.Execute "CREATE TABLE Employees" & _
                         "(" & _
                         "  EmployeeNumber Integer, " & _
                         "  FirstName Varchar(20), " & _
                         "  LastName Text(20), " & _
                         "  HourlySalary Double" & _
                         ");"

    dbConnection.Execute "INSERT INTO Employees VALUES(48058, 'Daniel', 'Keller', 22.50);"
    dbConnection.Execute "INSERT INTO Employees VALUES(27479, 'William', 'Schenke', 14.20);"
    dbConnection.Execute "INSERT INTO Employees VALUES(96350, 'Margareth', 'Greenmore', 10.50);"
    dbConnection.Execute "INSERT INTO Employees VALUES(62094, 'Laura', 'Walters', 18.75);"
    dbConnection.Execute "INSERT INTO Employees VALUES(28005, 'Patrick', 'Starr', 12.45);"

    MsgBox "A table named Employees has been created.", vbOKOnly Or vbInformation, "Exercise"

    Application.RefreshDatabaseWindow

    Set dbConnection = Nothing
End Sub

In the same way, you can perform any of the operations we have seen so far, including adding records, performing data analysis, navigating among records, and using data joins, etc.

 
 
   
 

Previous Copyright © 2005-2013, FunctionX, Inc. Next