How to Connect to an Access Database Using VBScript

user_12128310 0
Viewed 8831

I have an Access database for a client who wants to connect to and query the database using vbscript (so they can automate without actually opening the Access 2000 MDB). I can't figure out how to make the database connection.

I've tried several scripts, using both DAO and OLEDB. Below I've pasted the closest I've got, using an ODBC File DSN (I'm afraid using a System DSN would require extra work on the client's end, I'm trying to keep it simple).

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'ERROR OCCURS HERE
objConnection.Open "FileDSN=D:\RLS.dsn;" 

objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT County FROM CountyTBL" , objConnection, adOpenStatic, adLockOptimistic

Here is the contents of RLS.dsn (I created this using Windows Control Panel so I am confident it's correct):

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=D:\
DBQ=D:\RLS_be.mdb

The error message I got (and this was similar with the other 2 scripts I tried as well) was:

"Line 5, Char 4 Error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. Source: Microsoft OLE DB Provider for ODBC Drivers"

ms-access vbscript
2 Answers

You can simply use ADO to connect to the file without setting up a DSN. This will be simpler for your client.

For Access 2000, 2002-2003 MDB, use the following connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RLS_be.mdb"

For Access 2007, 2010, 2013 ACCDB:

"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\RLS_be.accdb"

The overall connection code:

' Build connection string
Dim sConnectionString
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RLS_be.mdb"

' Create connection object
Dim objConnection
Set objConnection = CreateObject("ADODB.Connection")

' Open Connection
objConnection.open sConnectionString

' Get recordset from SQL query
Dim objRecordset
Dim sQuery
sQuery = "SELECT County FROM CountyTBL"

Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.CursorLocation = adUseClient
objRecordset.Open sQuery, objConnection, adOpenStatic, adLockOptimistic
user_73190460

Here is another version using the connection string for a MS-Access 2016 database. The example connects to the 'clients.accdb' database and retrieves the value of the 'ClientID' field of the record where the 'ClientName' is equal to 'Joe Smith', then it loops through the records returned by the SQL statement.

Note that the extra quotations in the SQL statement are used in order to handle the scenario in which the name might contain a single quot ('), for example O'Connor.

Dim oConn, oRS

Dim ClientName : ClientName = "Joe Smith"

Set oConn = WSH.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=C:\test\clients.accdb"
Set oRS = oConn.Execute("Select ClientID From Clients Where ClientName=""" & ClientName & """")

Do While Not(oRS.EOF)

 'Do something with the record.

 oRS.MoveNext
Loop

oRS.Close
oConn.Close
user_68221920
Related