How to Connect to Access Database with VBScript

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.