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.
Brian says
Was not able to connect to my Access database with VBscript in Windows 8. Thanks to this post I got it working by running cscript in c:windowssyswow64 directory.
Thanks Ryan
Mlondie says
I’ve tried everything you say about forcing 32bit but the error persists. I have verified that there’s nothing wrong with my script because it runs perfectly on another computer, but on my laptop it just won’t! Please help. No matter which database format I use, which Provider connection string I use, all the providers “cannot” be found. What’s wrong with my laptop?
Ryan McCormick says
maybe post your code, lets see what you are trying to do.
Mike hancoski says
I am getting a no read permission error, do you happen to have an example of how to access a secure mdb file
Code
connStr = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:SMCORE32.mdb”
‘connStr = “Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:SMCORE32.mdb”‘
Set objConn = CreateObject(“ADODB.Connection”)
objConn.open connStr
‘Define recordset and SQL query
Set rs = objConn.execute(“SELECT * FROM vendors”)
‘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
WScript.Echo “Hello World!”
Ryan McCormick says
It looks like you may need to pass a password in the connection string. I am writing this comment from my iPhone so I don’t have the ability to post a good example but I found this https://www.connectionstrings.com/access/ the site looks like it has some good examples of this.
zunaid says
Yes It worked for me. I had to create a shortcut and edit property – target – with- %windir%SysWoW64wscript.exe C:UsersusernameDesktopexampleexample.vbs