PDA

View Full Version : [SOLVED] Connecting to Access 2010



JKwan
06-20-2014, 11:35 AM
I am trying to run the below code in a vbs file, for whatever the reason I cannot figure out why I am getting the error of "Provider not found"??? If I drop the code into Excel it runs without any problem.

Thanks



Option Explicit
Const gPSIDB = "C:\Temp\PersonalSafety.accdb"
Const adOpenDynamic = 2 ' Will see Addition, Changes or Deletions
Const adLockReadOnly = 1
Const adOpenStatic = 3 ' Will not see Addition, Changes or Deletions
Const adModeRead = 1
Const adModeWrite = 2
Dim objTemp
Dim sSQL
Dim objConnection

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = '" & gPSIDB & "'"
Set objTemp = CreateObject("ADODB.Recordset")
sSQL = "Select [CaseNumber] " & _
"From PersonalSafety Order By [CaseNumber]"

objTemp.Open sSQL, objConnection, adOpenStatic, adLockReadOnly
objTemp.MoveLast
Msgbox CStr(objTemp.Fields("CaseNumber").Value + 1)
Set objTemp = Nothing
Set objParent = Nothing

mancubus
06-20-2014, 04:29 PM
i dont remember a single quote before/after database name.
did you try removing them?

mancubus
06-20-2014, 04:32 PM
http://technet.microsoft.com/en-us/magazine/2008.04.heyscriptingguy.aspx

JKwan
06-20-2014, 09:20 PM
Thanks for the reply. With or without quotes do work with Excel but not in a vbs file. After digging more, I found my solution. Why it is not working because of 64 bit OS. There is no 32 bit connector. In order to make it work, I either need to create a shortcut to my vbs file using the syswow64 of wscript.exe. That is one way, I tried it and no problem, I was able to run my vbs file. Another way that I made it work was to put my vb code into an hta wrapper, I found this by accident. I had an hta app kicking around and it was able to connect to my Access db. I used this idea and holy cow, it works!

None the less, thank you for your suggestion!