• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

How to Connect to Access Database with VBScript

June 5, 2014 by Ryan McCormick 7 Comments

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

access-vbs-output

Conclusion

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

Related

Filed Under: Microsoft Access, VBScript Tagged With: Access Database, Data Connection, VBScript

Reader Interactions

Comments

  1. Brian says

    September 21, 2014 at 5:13 pm

    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

    Reply
  2. Mlondie says

    July 11, 2015 at 4:35 am

    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?

    Reply
    • Ryan McCormick says

      July 11, 2015 at 12:26 pm

      maybe post your code, lets see what you are trying to do.

      Reply
  3. Mike hancoski says

    August 4, 2015 at 11:23 am

    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!”

    Reply
    • Ryan McCormick says

      August 5, 2015 at 11:33 am

      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.

      Reply
  4. zunaid says

    September 18, 2015 at 2:11 pm

    Yes It worked for me. I had to create a shortcut and edit property – target – with- %windir%SysWoW64wscript.exe C:UsersusernameDesktopexampleexample.vbs

    Reply

Trackbacks

  1. SOLVED: Provider cannot be found. It may not be properly installed says:
    June 5, 2014 at 3:10 pm

    […] I am writing this one as part two of my post: How to Connect to Access Database with VBScript […]

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in

 

Loading Comments...