How to Connect to Access Database with VBScript

I am posting this one because it took me a little while to find a solid solution. Everything I found had an answer, just not an answer that works. With this solution, you will be able to connect to and pull data from an MS Access database with VBScript.

So without further ado, here is the example:

Access Source Data

For this example, I am using a database stored in a folder called “example” on my desktop. The table I will be targeting is called “people” and the data contained in my table is:

ID	Fname
1	Jim
2	Bob
3	Carl
4	Sandra
5	Jim

Connect to Access Database With VB Script | Access 2007,2010,2013

This example uses the “Provider=Microsoft.ACE.OLEDB.12.0;” to access *.accdb files

Dim connStr, objConn, getNames
'''''''''''''''''''''''''''''''''''''
'Define the driver and data source
'Access 2007, 2010, 2013 ACCDB:
'Provider=Microsoft.ACE.OLEDB.12.0
'Access 2000, 2002-2003 MDB:
'Provider=Microsoft.Jet.OLEDB.4.0
''''''''''''''''''''''''''''''''''''''
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\username\Desktop\example\example.accdb"

'Define object type
Set objConn = CreateObject("ADODB.Connection")

'Open Connection
objConn.open connStr

'Define recordset and SQL query
Set rs = objConn.execute("SELECT Fname FROM people")

'While loop, loops through all available results
DO WHILE NOT rs.EOF
'add names seperated by comma to getNames
getNames = getNames + rs.Fields(0) & ","
'move to next result before looping again
'this is important
rs.MoveNext
'continue loop
Loop

'Close connection and release objects
objConn.Close
Set rs = Nothing
Set objConn = Nothing

'Return Results via MsgBox
MsgBox getNames
	

Connect to Access Database With VB Script | Access 2000, 2002-2003

This example uses the “Provider=Microsoft.Jet.OLEDB.4.0” to access *.accdb files

Dim connStr, objConn, getNames
'''''''''''''''''''''''''''''''''''''
'Define the driver and data source
'Access 2007, 2010, 2013 ACCDB:
'Provider=Microsoft.ACE.OLEDB.12.0
'Access 2000, 2002-2003 MDB:
'Provider=Microsoft.Jet.OLEDB.4.0
''''''''''''''''''''''''''''''''''''''
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\username\Desktop\example\example.mdb"

'Define object type
Set objConn = CreateObject("ADODB.Connection")

'Open Connection
objConn.open connStr

'Define recordset and SQL query
Set rs = objConn.execute("SELECT Fname FROM people")

'While loop, loops through all available results
DO WHILE NOT rs.EOF
'add names seperated by comma to getNames
getNames = getNames + rs.Fields(0) & ","
'move to next result before looping again
'this is important
rs.MoveNext
'continue loop
Loop

'Close connection and release objects
objConn.Close
Set rs = Nothing
Set objConn = Nothing

'Return Results via MsgBox
MsgBox getNames
	

Save and Run VBS File

NOTE: If you skip this step you will encounter an error: Provider cannot be found. It may not be properly installed

Because Windows 7 doesn’t have a driver that will run VBS files in 64 bit, you need to make it run your file in 32 bit. This can be accomplished by either opening your command prompt or by creating a shortcut to your file(without quotes):

“%windir%\SysWoW64\wscript.exe C:\Users\username\Desktop\example\example.vbs”

Output from the above samples

Conclusion

There are many creative reasons for connecting to an Access Database with VBS. Please comment with questions, suggestions or improvements.