tootxbl

MS Access 2013 Connection

20 posts in this topic

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 :)

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Change line 1 from

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

to

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

 

Edited by Exit

My UDFs, Abbrevs and Snippets
If you like my post, just click the like button :) 

Share this post


Link to post
Share on other sites

I assume the forum software removes the quotes :huh:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

Changed to single quotes and now it is OK. :D

Edited by Exit

My UDFs, Abbrevs and Snippets
If you like my post, just click the like button :) 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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"


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Yes, it does.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now