5

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"

David Britz
  • 117
  • 1
  • 3
  • 9
  • How are you running the file? 32-bit or 64-bit CSCRIPT.EXE or WSCRIPT.EXE? – Erik A Oct 25 '19 at 21:19
  • Well, actually I just saved it as .vbs to my desktop and double-clicked on it. – David Britz Oct 26 '19 at 02:17
  • 1
    I've posted [a general Q&A on the subject](https://stackoverflow.com/q/58571740/7296893). You can review it, and [How do I run a VBScript in 32-bit mode on a 64-bit machine?](https://stackoverflow.com/q/2806584/7296893). Likely, following that last one fixes your issue. – Erik A Oct 26 '19 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 Oct 28 '19 at 21:34

2 Answers2

4

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
Étienne Laneville
  • 4,697
  • 5
  • 13
  • 29
  • 1
    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. – David Britz Oct 26 '19 at 02:14
  • Please post your updated code if you still run into problems! – Étienne Laneville Oct 26 '19 at 02:34
  • As far as connection string, perhaps you can simply check the file extension before connecting and adjust the Provider. – Étienne Laneville Oct 26 '19 at 02:36
  • I'd go with ADO for this, not ODBC. If so, have you installed the [ADO (2.8) drivers] (https://www.microsoft.com/en-us/download/details.aspx?id=5793) on that machine? – Hel O'Ween Oct 28 '19 at 10:50
0

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
Safwan
  • 181
  • 5