Connecting to Access 2010

[JKwan]

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]

i dont remember a single quote before/after database name.
did you try removing them?

[mancubus]

http://technet.microsoft.com/en-us/m...iptingguy.aspx

[JKwan]

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!