MS Access 2013 Connection

[tootxbl]

Hi,

I've been working with AutoIT and MS Access for a number of years now. However, my experience has been based on working with the .mdb file format.

I now work for a new company and I have built a database using the accdb format for the first time. I am trying to connect to it using AutoIT but I cannot get my old sample scripts to connect to the database and return data using a query.

I have a backend database on a server which is password protected. I have a client based database that connects to the password protected backend database. In my previous experience I used user level security to protect the server database so connecting to it and working with the data was never a problem. This is my first time using accdb and a password encrypted file.

Here is my sample code:

1. $db = \\192.168.10.10\access\server.accdb
2. $tbl = "ltbl_LIST_ActionLog"
3. Global $Server_Access="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $db; & "; Jet OLEDB:Database Password=password;"
4. $dbAccess = ObjCreate ("ADODB.Connection")
5. $dbAccess.Open ($Server_Access & ";")
6. $actionData = $dbAccess.Execute("SELECT " & $tbl & ".ID, " & $tbl & ".Description FROM " & $tbl & " WHERE (((" & $tbl & ".ID)=515));")
7. while not $actionData.EOF
8.  $action = $actionData.fieldS("ID").value
9.  $desc = $actionData.fieldS("Description").value
10.  $actionData.MoveNext
11. wend

This is the setup I used to use for connecting to my mdb files. The only things I have tweaked are the provider details and added in the password parameter. The error I get is "==> Variable must be of type "Object".:" against line 7.

I'm assuming its my connection that is causing the problem but I can't find how to error check the connectivity.

Also, is there any chance I can connect to the front end database and read the data from the linked table without having to use a password? I assume this wouldn't work. I have tried it and get the same error but I don't know if that's because its not possible or because I don't have my connection methods correct.

Any help on this would be great

[water]

Add a COM error handler to your script so you can see what goes wrong.

[Exit]

Change line 1 from

1.$db = \\192.168.10.10\access\server.accdb

to

1.$db = '\\192.168.10.10\access\server.accdb'

 

Edited July 24, 2015 by Exit

[water]

I assume the forum software removes the quotes

[Exit]

Yes, I  put double quotes around the URL, but they got away. Forum bug?

Changed to single quotes and now it is OK.

Edited July 24, 2015 by Exit

[water]

I think so.
I think the OPs problem is caused by something else. Lets see what a COM error handler shows.

[tootxbl]

Thanks guys. Yes, I have the quotes in my script. I guess they either got removed here or I removed them by mistake when I changed the path to be something a bit simpler

Right, I have never used COM error handling so I have given it a shot and I get the following:

Number: 80020009

Description: Exception occurred

When I click OK this pops up again. When I click OK on the second instance I get this:

Number: 000000A9

Description: Variable must be of type 'Object'

Every time I click OK the message reappears. I assume this is coming up against the While statement on line 7.

I'm not really sure what this tells me...?

I have also tried adding the iEventError box shown in the help file immediately after the "Open" command and it does tell me that I get an error there.

 

[water]

80020009 is a general error. This could be everything from wrong password to a typo in the connection string.
Lets see where the first error occurs.

; Add your COM error handler code here
    
    $db = "\\192.168.10.10\access\server.accdb"
    $tbl = "ltbl_LIST_ActionLog"
    Global $Server_Access="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $db; & "; Jet OLEDB:Database Password=password;"
    MsgBox(0, "Debug", "Before ObjCreate")
    $dbAccess = ObjCreate ("ADODB.Connection")
    MsgBox(0, "Debug", "Before Open")
    $dbAccess.Open ($Server_Access & ";")
    MsgBox(0, "Debug", "Before Execute")
    $actionData = $dbAccess.Execute("SELECT " & $tbl & ".ID, " & $tbl & ".Description FROM " & $tbl & " WHERE (((" & $tbl & ".ID)=515));")
    Exit

 

[tootxbl]

Ok, the help file had me setup COM errors with message boxes. I have updated it to use the console instead and output the line numbers so I have a better idea of what lines are the problem.

Line 5 (opening the DB) is returning 80020009 Exception occurred

Line 6 (the query) is returning 80020009 Exception occurred

Lines 7-11 are returning 000000A0 Variable must be of type 'Object'

I assume the query is failing due to not opening the DB and the object failures are due to the query not running...

So yeah, I guess this says opening the DB is the problem right?

Apologies for seeming simple. COM handling is new to me.

 

[water]

Two things to try:

1. Change $dbAccess.Open ($Server_Access & ";") to $dbAccess.Open($Server_Access) because the connection string already ends with a semicolon
2. Copy the database to a local drive and try to open the local copy

[tootxbl]

No joy

I have switched the query to look at the client side database instead and use a local table in that (not a link to a table in the password protected server DB). There is no password protection on this client DB so it should be easy access. I thought removing the password aspect may simplify things.

I also noted that I do not have a semicolon on the end of my db access string. I had remmed out the password part of that string in line 3 in my original post so I think my open command was correct in having the semicolon. Good spot though as I would have got an error anyway having it on both lines 3 and 5

However, to make it clearer I have now deleted the password aspect of the code as I don't need one to open the client DB. The modified code is below:

1. $db = "C:\client\Client.accdb"
2. $tbl = "tbl_CONFIG_DBVersion"
3. Global $Server_Access="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $db
4. $dbAccess = ObjCreate ("ADODB.Connection")
5. $dbAccess.Open ($Server_Access & ";")
6. $actionData = $dbAccess.Execute("SELECT " & $tbl & ".ID, " & $tbl & ".VersionNotes FROM " & $tbl & " WHERE (((" & $tbl & ".ID)=1));")
7. while not $actionData.EOF
8.  $action = $actionData.fieldS("ID").value
9.  $desc = $actionData.fieldS("VersionNotes").value
10.  $actionData.MoveNext
11. wend

I still get the same errors on the same lines. Exception on 5 and 6, object issue on 7-11. 

[water]

Could you please run the following script from this wiki article to make sure the provider is installed on your machine?
"List all installed OLE DB providers"

[water]

BTW: Did you tell us which operating sytem and which version of AutoIt you run?

[tootxbl]

Apparently I do have OLEDB 12 - HKCR\CLSID\{3BE786A0-0366-4F5C-9434-25CF162E475E}|Microsoft.ACE.OLEDB.12.0|Microsoft Office 12.0 Access Database Engine OLE DB Provider

I'm on Windows 8.1 and appear to be running AutoIt 3.3.12.0

[water]

Is anything of the following true for your environment?

There are some reported problems with the "Microsoft Access Database Engine 2010 Redistributable" package that seems to arise when using 64 bit windows and 64 bit office 2010 or above.

[tootxbl]

Hmm. I am running the full office 2013 suite. Maybe I just need to give up Thanks for the help water

[water]

Which bitness of Office and Windows do you run? 32, 64?

[water]

As the search function of the forum is still quite lame I used Google to search this site and found the following post:

Could you add

#AutoIt3Wrapper_UseX64=n

at the top of your script?

Edited July 24, 2015 by water

[tootxbl]

Does #AutoIt3Wrapper do anything when you run the script from SciTe?

 

Erm, I would say 64bit for office and Windows. I am using x86... that's 64bit right?

[water]

Yes, it does.