Error trying to open Access database

[larryc]

I'm just getting started trying to use Autoit to query an Access 2007 database, and I'm running into problems.

In the following code, I get a console error saying

c:\test.au3 (5) : ==> The requested action with this object has failed.:
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)^ ERROR

Here is the code:

Local $dbname = FileOpenDialog("Choose Access Database", @ScriptDir, "Access files (*.accdb)", 1)

$adoCon = ObjCreate("ADODB.Connection")
;$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname) ;Use this line if using MS Access 2003 and lower
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) ;Use this line if using MS Access 2007 and using the .accdb file extension
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3

$query = "select * from test"
$adoRs.Open($query, $adoCon)
MsgBox( 0, "Result", "Number of Records: " & $adoRs.RecordCount )
$adoCon.Close

Any ideas? If it matters, I'm running Windows 7 X64. MS Access 2007 runs fine; it's just autoit that's giving me trouble. Would appreciate any help/advice.

[larryc]

Ok, I solved it.

The connection string needs to look like:

$adoCon.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & $dbname & ";Uid=;Pwd=;")

Notice the addition of Uid and Pwd.

[GEOSoft]

It's not AutoIt. It's an object error from that line. You are using the wrong provider for 2007. OLEDB.4.0 was last used in Access 2003.

Try this function.

Func _adoProvider()
   Local $oProvider = "Microsoft.Jet.OLEDB.4.0; "
   Local $objCheck = ObjCreate("Access.application")
   If IsObj($objCheck) Then
      Local $oVersion = $objCheck.Version
      If StringLeft($oVersion, 2) == "12" Then $oProvider="Microsoft.ACE.OLEDB.12.0; "
   EndIf
   Return $oProvider
EndFunc