Connect to an MS Access Database Using ADO - April 2009

The tip-of-the-month shows how to connect to an MS Access Database. Connecting is contained in class module cConnectMSAccess.vb. I created an Asp.Net Project [ConnectMSAccess.sln] to demonstrated the process. Demo pages:

Enter the fully qualified name of the database to be opened. Test the existence of the database by clicking the 'Test Connection' button. Display the databases schema using the 'Display Schema for Database' button. The Nwind.mdb database is entered by default. It is optionally distributed with this source code - see below.

The database's tables and associated columns are displayed. Click the 'Another' button to select a different database. ADOX is used to access the tables schema. It is distributed with the source code.

cConnectMSAccess - Methods and Properties

Properties Description Example
connAccess The ADODB.Connection which can be used to access the database.  
sDatabase The database name. Nwind.mdb
sJetProvider The ADODB provider. Default is: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  
sConnectionString The connection string. Built by concatenating sJetProvider with sDatabase.  
Open Open the connection using sConnectionString Returns True if successful
Close Close the opened database. Returns True if successful
TestConnection Test the connection by doing an Open then Close in a Try/Catch routine. Returns True if successful

Download VB.Net Code

Download - VS2005 VB.Net Zipped File  Size 
Connect MS Access and Nwind.mdb 582K
Connect MS Access and no Nwind.mdb 40K

Associated Database Tips

Web Page - Click to View Month Year Volume
Allow Zero Length String May 2000 v3, n10
Backup and Restore a SQL Server Database September 2010 v14, n2
Connect to an MS Access Database using ADO April 2009 v12, n9
Copy Database with Data using DAO or ADO July 2003 v6, n5
Copy SQL Server Database Schema to Another March 2011 v14, n8
Create VB Class Module Code for ADO February 2003 v6, n7
Create VB Class Module Code for DAO September 2002 v6, n2
Create VB.Net Class to Process a SQL Table using ADO
      Add methods, Copy, Count, Compare
v12, n1
v14, n5
Create Jet Relationship using ADOX March 2003 v6, n8
Create SQL Relationship using SMO February 2009 v12, n7
Dynamically Update a SQL Database Adding Tables or Columns using SMO January 2011 v14, n6
Save Memo Fields in Database Using DAO March 2002 v5, n8
Store File in Jet Database October 2000 v4, n3
Update an Access Database with a Model's Schema using VB6 and DAO September 2000 v4, n2
Update an Access Database with a Model's Schema using VB6 and ADOX June 2003 v6, n11
Update an Access Database with a Model's Schema using VB.Net and ADOX August 2003 v7, n1
Use the OleDbDataAdapter to Read and Parse a CVS File into a DataTable or Collection October 2010 v14, n3
Using ADO to Read and Parse a Text File April 2002 v5, n9

Previous Tip   Tip Index   Next Tip


Updated 2011/04/16