[SOLVED] Connect to MS Access

[DaLiMan]

Hi, I'm trying to connect to an Access database (2016) for some time now.
I tried several UDF's from the Forum without any luck.
Yesterday I found a piece of code from GreenCan LINK TO TOPIC which seems promising.
So I made an file DSN as suggested in the script, but I don't know how to get this working.
 

Below is another piece of code I came up with.....
This is also not working.

Does anyone have this working?
I the end need to read table data to an array [Id - Name]

Local $title
Local $dbname = "D:\Documents\1_Daniel\Access\Import_Fill_Empty.accdb"
Local $tblname = "newfile"
Local $query = "SELECT * FROM " & $tblname & " WHERE ID = 10"

Local $oShell = ObjCreate("shell.application") ; Get the Windows Shell Object
ConsoleWrite("oShell=" & $oShell & @CRLF)


$adoCon = ObjCreate("ADODB.Connection")
ConsoleWrite("adocon=" & $adoCon & @CRLF)
;$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
; Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
; Jet OLEDB:Database Password=MyDbPassword;
$adoRs = ObjCreate ("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open ($query, $adoCon)
$title = $adoRs.Fields("veld2").value    ;Retrieve value by field name
;$title = $adoRs.Fields(2).value         ;Retrieve value by column number
$adoCon.Close
MsgBox(0,"testing",$title)

 

Edited February 1, 2017 by DaLiMan

[mLipok]

Did you try also this:

 

??

 

[DaLiMan]

No, I did not. But I will later today.

Let's see if I get this working...

 

[spudw2k]

I've had some success with my ADODB UDF in my signature.  The most common issue I've seen is 64-bit related.

[DaLiMan]

2 hours ago, mLipok said:

Did you try also this:

 

I downloaded the file and copied the example. Can you help me set up a connection?
what do I need for the globals?
I have no password set for the database, so do I need to fill sUser?

 

Global $sDSN = '????'
Global $sDatabase = 'D:\Documents\1_Daniel\Access\Import_Fill_Empty.accdb'
Global $sServer = '????'
Global $sPort = '????'
Global $sUser = ''
Global $sPassword = ''

 

Edited December 29, 2016 by DaLiMan
typo

[DaLiMan]

3 hours ago, spudw2k said:

I've had some success with my ADODB UDF in my signature.  The most common issue I've seen is 64-bit related.

Hi spudw2k, Just tried your ADODB UDF.
Unfortunatly having errors...

Since I have W10(64bit) but O2016(32bit) I don't know if the 64bit issue could be the problem here.
Tried the #AutoIt3Wrapper_UseX64=N option without ant luck though. 
However the #error notification changes but the err.number remains the same?

 

[mLipok]

7 hours ago, DaLiMan said:

I downloaded the file and copied the example. Can you help me set up a connection?
what do I need for the globals?
I have no password set for the database, so do I need to fill sUser?

 

Global $sDSN = '????'
Global $sDatabase = 'D:\Documents\1_Daniel\Access\Import_Fill_Empty.accdb'
Global $sServer = '????'
Global $sPort = '????'
Global $sUser = ''
Global $sPassword = ''

 

Look in ADO_EXAMPLE.au3

Func _Example_MSAccess()

    Local $sMDB_FileFullPath = Default ;'Here put FileFullPath to your Access File'
    Local $sDriver = Default
    Local $sUser = Default
    Local $sPassword = Default

    Local $sConnectionString = _ADO_ConnectionString_Access($sMDB_FileFullPath, $sUser, $sPassword, $sDriver)

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from SOME_TABLE")
    _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSAccess

 

and look here:
https://www.connectionstrings.com/access/

 

Edited December 30, 2016 by mLipok

[DaLiMan]

 I think I downloaded the wrong file 

Now downloaded ADO 2.1.13 BETA and found the example file.
Having tested it I come across the same errors as before.

>"D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\..\AutoIt3.exe" "D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE.au3" /UserParams    
+>12:41:23 Starting AutoIt3Wrapper v.16.306.1237.0 SciTE v.3.6.2.0   Keyboard:00020409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0413)  CodePage:0  utf8.auto.check:4    # detect ascii high characters and if none found set default encoding to UTF8 and do not add BOM
+>         SciTEDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable   UserDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper
>Running AU3Check (3.3.14.2)  params:-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7  from:D:\Downloads\autoit-v3\install  input:D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE.au3
+>12:41:24 AU3Check ended.rc:0
>Running:(3.3.14.2):D:\Downloads\autoit-v3\install\autoit3_x64.exe "D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
###############################
ADO.au3 v.2.1.13 BETA (970) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC-stuurprogrammabeheer] De naam van de gegevensbron is niet gevonden en er is geen standaardstuurprogramma opgegeven
$oADO_Error.windescription:     Er is een uitzondering opgetreden.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  970
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################
###############################

Could it be the 64 bit problem spudw2k was talking about?

 

 

 

[mLipok]

Try to change this function in my UDF:
 

Func _ADO_ConnectionString_Access($sFileFullPath, $sUser = Default, $sPassword = Default, $sDriver = Default)

    If $sUser = Default Then
        $sUser = ''
    Else
        $sUser = 'Uid=' & $sUser & ';'
    EndIf

    If $sPassword = Default Then
        $sPassword = ''
    Else
        $sPassword = 'PWD=' & $sPassword & ';'
    EndIf

    If $sDriver = Default Then
        If StringRight($sFileFullPath,6) = '.accdb' then
            $sDriver = 'Microsoft Access Driver (*.mdb, *.accdb)'
        Else
            $sDriver = 'Microsoft Access Driver (*.mdb)'
        EndIf
    EndIf
    Local $sConnectionString = 'Driver={' & $sDriver & '};Dbq="' & $sFileFullPath & '";' & $sUser & $sPassword

    If Not StringRegExp($sConnectionString, '(?i)(Microsoft Access Driver \(*.mdb\)|Microsoft Access Driver \(*.mdb, *.accdb\))', $STR_REGEXPMATCH) Then
        $sConnectionString = StringReplace($sConnectionString, ';Dbq=', ' ;Data Source=')
    EndIf

    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $sConnectionString)
EndFunc   ;==>_ADO_ConnectionString_Access

 

[DaLiMan]

5 hours ago, mLipok said:

Try to change this function in my UDF:

Changed the code, no luck....
Still the same #error.

[mLipok]

Try this: http://stackoverflow.com/a/40510387

[mLipok]

Look also here:

There is working example which should work correctly with attached     bd-teste.accdb

I mean this should work after installing "AccessDatabaseEngine.exe"

[DaLiMan]

11 hours ago, mLipok said:

I mean this should work after installing "AccessDatabaseEngine.exe"

I downloaded and installed the 32bit (can't instal the 64bit... )
But this should not botter I guess because my office is 32 bit also.

Copied the example from the other topic and donloaded your example database.
Still having the same error...

 

>"D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\..\AutoIt3.exe" "D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "D:\Downloads\autoit-v3\_AccessRead_test2.au3" /UserParams    
+>09:43:32 Starting AutoIt3Wrapper v.16.306.1237.0 SciTE v.3.6.2.0   Keyboard:00020409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0413)  CodePage:0  utf8.auto.check:4    # detect ascii high characters and if none found set default encoding to UTF8 and do not add BOM
+>         SciTEDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable   UserDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper
>Running AU3Check (3.3.14.2)  from:D:\Downloads\autoit-v3\install  input:D:\Downloads\autoit-v3\_AccessRead_test2.au3
+>09:43:35 AU3Check ended.rc:0
>Running:(3.3.14.2):D:\Downloads\autoit-v3\install\autoit3_x64.exe "D:\Downloads\autoit-v3\_AccessRead_test2.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
_AccessRead_test2.au3 (15) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Er is een uitzondering opgetreden.

    err.description is:     [Microsoft][ODBC-stuurprogrammabeheer] De naam van de gegevensbron is niet gevonden en er is geen standaardstuurprogramma opgegeven
    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  15
    err.retcode is:     0x80004005

 

Do you know what Page2PagePro was talking about when he wrote this? (Where is this control panel?)
If you do NOT have two Data Sources shortcuts in Control Panel --> Administrative Tools, I'd recommend doing the following:

 

Also, I made a DSN file from Access.
Here is the data in de DSN file.
Looks like it uses the default driver just like in your UDF. SO why doesn't it work?

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb, *.accdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=D:\DOCUMENTS
DBQ=D:\DOCUMENTS\Database1.accdb

 

[mLipok]

please set Fake User and Password and then post here a result from: 

_ADO_ConnectionString_Access

[DaLiMan]

Hi and the best wishes for 2017. 

 

After starting up this morning I tested your latest reply.....guess what...It works!
I must have done something wrong yesterday. 

So I compiled the test script and copied it to another computer on which I did not install the AccessDatabaseEngine.exe
And indeed, having errors again. So just like you said, we really need to install this engine.
Which, to my opinion ,is strange because I have a full office installation including MS Access fulltime.
Still I wish there was another way to connect to Access w/o having to install anything.
Installing anything  would be an obstacle for if I wanted to distribute to other users, which I was thinking about for work.
Since not being an administrator I can't install anything and so can't anybody else.

However, for the current project at home this is fine.
Thank you very much for the help.

 

PS: If you know of another way to connect to MS Access w/o the installation of this engine I would be very interested! 

[mLipok]

On 31.12.2016 at 1:34 PM, mLipok said:

please set Fake User and Password and then post here a result from: 

_ADO_ConnectionString_Access

I'm still interested in showing your answer .

[DaLiMan]

6 minutes ago, mLipok said:

I'm still interested in showing your answer .

Here is the error from _ADO_Connectionstring_Access.
I hope this is what you mean? If you need anything else just say so. I'll try to give what you ask.

 

>"D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\..\AutoIt3.exe" "D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE.au3" /UserParams    
+>15:04:59 Starting AutoIt3Wrapper v.16.306.1237.0 SciTE v.3.6.2.0   Keyboard:00020409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0413)  CodePage:0  utf8.auto.check:4    # detect ascii high characters and if none found set default encoding to UTF8 and do not add BOM
+>         SciTEDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable   UserDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper
>Running AU3Check (3.3.14.2)  params:-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7  from:D:\Downloads\autoit-v3\install  input:D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE.au3
+>15:05:00 AU3Check ended.rc:0
>Running:(3.3.14.2):D:\Downloads\autoit-v3\install\autoit3.exe "D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
@@ Debug(29) : _ADO_ConnectionString_Access($sMDB_FileFullPath, $sUser, $sPassword, $sDriver) = Driver={Microsoft Access Driver (*.mdb, *.accdb)} ;Data Source="D:\Downloads\bd-teste.accdb";Uid=fake;PWD=nope;
>Error code: 0
###############################
ADO.au3 v.2.1.13 BETA (970) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC-stuurprogrammabeheer] De naam van de gegevensbron is niet gevonden en er is geen standaardstuurprogramma opgegeven
$oADO_Error.windescription:     Er is een uitzondering opgetreden.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  970
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

 

[mLipok]

Check this for "Driver" and "Provider":

Func _Example_MSAccess()

    Local $sConnectionString =  'Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq="D:\Downloads\bd-teste.accdb";Uid=fake;PWD=nope;'
    ;Local $sConnectionString =  'Provider=Microsoft.ACE.OLEDB.12.0;Data Source="D:\Downloads\bd-teste.accdb";Uid=fake;PWD=nope;'

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from SOME_TABLE")
    _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSAccess

And here are some links to read:

https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error-the-microsoftaceoledb120-provider-is-not-registered-on-the-local-machine?forum=vstsdb

http://www.mikesdotnetting.com/article/280/solved-the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine

http://www.layer2solutions.com/en/community/FAQs/BDLC/Pages/How-to-access-office-2010-data-from-sharepoint.aspx

 

[DaLiMan]

 

Errors from the driver with ADO.UDF

>"D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\..\AutoIt3.exe" "D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE2.au3" /UserParams    
+>12:24:27 Starting AutoIt3Wrapper v.16.306.1237.0 SciTE v.3.6.2.0   Keyboard:00020409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0413)  CodePage:0  utf8.auto.check:4    # detect ascii high characters and if none found set default encoding to UTF8 and do not add BOM
+>         SciTEDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable   UserDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper
>Running AU3Check (3.3.14.2)  params:-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7  from:D:\Downloads\autoit-v3\install  input:D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE2.au3
+>12:24:28 AU3Check ended.rc:0
>Running:(3.3.14.2):D:\Downloads\autoit-v3\install\autoit3.exe "D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE2.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
@@ Debug(25) : $sConnectionString = Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ="D:\Downloads\bd-teste.accdb";Uid=fake;PWD=nope;
>Error code: 0
###############################
ADO.au3 v.2.1.13 BETA (970) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xeb0 Thread 0x327c DBC 0x57a7acc                                                              Jet'.
$oADO_Error.windescription:     Er is een uitzondering opgetreden.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  970
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################
###############################
ADO.au3 v.2.1.13 BETA (971) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xeb0 Thread 0x327c DBC 0x57a7acc                                                              Jet'.
$oADO_Error.windescription:     Er is een uitzondering opgetreden.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  971
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

 

Errors from the provider with the ADO.UDF

>"D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\..\AutoIt3.exe" "D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE2.au3" /UserParams    
+>12:26:21 Starting AutoIt3Wrapper v.16.306.1237.0 SciTE v.3.6.2.0   Keyboard:00020409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0413)  CodePage:0  utf8.auto.check:4    # detect ascii high characters and if none found set default encoding to UTF8 and do not add BOM
+>         SciTEDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable   UserDir => D:\Downloads\autoit-v3\install\SciTE4AutoIt3_Portable\AutoIt3Wrapper
>Running AU3Check (3.3.14.2)  params:-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7  from:D:\Downloads\autoit-v3\install  input:D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE2.au3
+>12:26:22 AU3Check ended.rc:0
>Running:(3.3.14.2):D:\Downloads\autoit-v3\install\autoit3.exe "D:\Downloads\ADO 2.1.13 BETA\ADO_EXAMPLE2.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
@@ Debug(27) : 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source="D:\Downloads\bd-teste.accdb";Uid=;PWD=;' = Provider=Microsoft.ACE.OLEDB.12.0;Data Source="D:\Downloads\bd-teste.accdb";Uid=;PWD=;
>Error code: 0
###############################
ADO.au3 v.2.1.13 BETA (970) : ==> COM Error intercepted !
$oADO_Error.description is:     Could not find installable ISAM.
$oADO_Error.windescription:     Er is een uitzondering opgetreden.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  970
$oADO_Error.source is:  Microsoft Access Database Engine
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     5003170
###############################
###############################
ADO.au3 v.2.1.13 BETA (971) : ==> COM Error intercepted !
$oADO_Error.description is:     Could not find installable ISAM.
$oADO_Error.windescription:     Er is een uitzondering opgetreden.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  971
$oADO_Error.source is:  Microsoft Access Database Engine
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     5003170
###############################

 

Thanks for the links. Haven't been able to read them but I certainly will later this week.
 

[mLipok]

Did you solve your problem ?

btw.

On 30.12.2016 at 0:45 PM, DaLiMan said:

Could it be the 64 bit problem spudw2k was talking about?

What you mean ? Do you try to use AutoIt x64 ?