How to Connect to an Access Database Using VBScript

Question

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"

How are you running the file? 32-bit or 64-bit CSCRIPT.EXE or WSCRIPT.EXE? — Erik A
– Erik A, Commented Oct 25, 2019 at 21:19
Well, actually I just saved it as .vbs to my desktop and double-clicked on it. — David Britz
– David Britz, Commented Oct 26, 2019 at 2:17
I've posted a general Q&A on the subject. You can review it, and How do I run a VBScript in 32-bit mode on a 64-bit machine?. Likely, following that last one fixes your issue. — Erik A
– Erik A, Commented Oct 26, 2019 at 14:40
Thanks Erik! I was driving myself crazy wondering if I was using the right connection syntax but running it under 32-bit worked! — David Britz
– David Britz, Commented Oct 28, 2019 at 21:34

Étienne Laneville · Accepted Answer · 2019-10-25 22:23:44Z

5

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

edited Oct 25, 2019 at 22:23

answered Oct 25, 2019 at 20:30

Étienne Laneville

5,0815 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Britz Over a year ago

Thank you! I think I tried the direct ADO connection string already and got the same error message. But I'll try what you said, it does look simpler than my other scripts. If all else fails I'll make an ASP.NET console app to do it. But that might have the same connection string issues. Access is so confusing because there are so many different string templates, depending on whether it's Access 97, 2003, 2010, ADO, DAO, OLEDB, that I can't tell if it's bad syntax or whether I don't have the drivers or the wrong technology.

Étienne Laneville Over a year ago

Please post your updated code if you still run into problems!

Étienne Laneville Over a year ago

As far as connection string, perhaps you can simply check the file extension before connecting and adjust the Provider.

Hel O'Ween Over a year ago

I'd go with ADO for this, not ODBC. If so, have you installed the [ADO (2.8) drivers] (microsoft.com/en-us/download/details.aspx?id=5793) on that machine?

Safwan · Accepted Answer · 2021-05-14 07:00:44Z

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

Collectives™ on Stack Overflow

How to Connect to an Access Database Using VBScript

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related