Yet another Access UDF( but different - *.accdb only)

[spudw2k]

Just to clarify.  Are you looking to automate the Access application, or access the query stored in the ACCDB and return the results of the query to AutoIt (in the form of an array or something)?

 

If the latter, see if you can do this:

_Get_Records("SELECT * FROM QueryName")

I tested it with my UDF after tweaking some code, but that worked for me.

Edited May 18, 2020 by spudw2k

[gmmg]

Hi @kcvinu,

I am using the Access UDF and want to open a password protected “accdb” database. I can't do that with the standard UDF.

My solution is, i adjusted the connection string in the function or is there a better solution?
$Connection_String = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & $DBPath & ";Jet OLEDB:Database Password=" & $PassWord

That still needs to be done nicely in the code or how can I integrate this into the UDF?

#Region - Function _Start_Connection
; #FUNCTION# ====================================================================================================================
; Name ..........: _Start_Connection
; Description ...: This function will start or open a new connection.
; Syntax ........: _Start_Connection($DBPath [, $UserName = ""[, $PassWord = ""]])
; Parameters ....: $DBPath              - Path of your Access database file.(with *.accdb extension and a ";" sign at the end")
;                  $UserName            - [optional] Username of your database . Default is "".
;                  $PassWord            - [optional] Password of your database. Default is "".
; Return values .: String. If connection is opneed then, it will be "OK". And if it is failed then, "Failed " & @error
; Author ........: spudw2k. I've made a little change to this function
; Modified ......: 2015 sep
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: _Start_Connection(@ScriptDir & "\TestDB.accdb;")
; ===============================================================================================================================
Func _Start_Connection($DBPath, $UserName = "", $PassWord = "")
    If Not IsObj($objConnection) Then Return -1
    $Connection_String = $sDataProvider & $DBPath & ";"
    If $UserName Then $Connection_String &= "User ID=" & $UserName & ";"
    If $PassWord Then $Connection_String &= "Password=" & $PassWord & ";"
    
    ; this connection string works with my pwd protected DB
    $Connection_String = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & $DBPath & ";Jet OLEDB:Database Password=" & $PassWord

    $objConnection.Open($Connection_String)
    If @error Then  $ConnStatus = "Failed " & @error
    Return $ConnStatus
EndFunc

KR,

Marcel

 

[gmmg]

I have changed the code in the Func _Start_Connection Function  from:

If $PassWord Then $Connection_String &= "Password=" & $PassWord & ";"

to

If $PassWord Then $Connection_String &= ";Jet OLEDB:Database Password=" & $PassWord & ";"

 

#Region - Function _Start_Connection
; #FUNCTION# ====================================================================================================================
; Name ..........: _Start_Connection
; Description ...: This function will start or open a new connection.
; Syntax ........: _Start_Connection($DBPath [, $UserName = ""[, $PassWord = ""]])
; Parameters ....: $DBPath              - Path of your Access database file.(with *.accdb extension and a ";" sign at the end")
;                  $UserName            - [optional] Username of your database . Default is "".
;                  $PassWord            - [optional] Password of your database. Default is "".
; Return values .: String. If connection is opneed then, it will be "OK". And if it is failed then, "Failed " & @error
; Author ........: spudw2k. I've made a little change to this function
; Modified ......: 2015 sep
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: _Start_Connection(@ScriptDir & "\TestDB.accdb;")
; ===============================================================================================================================
Func _Start_Connection($DBPath, $UserName = "", $PassWord = "")
    If Not IsObj($objConnection) Then Return -1
    $Connection_String = $sDataProvider & $DBPath & ";"
    If $UserName Then $Connection_String &= "User ID=" & $UserName & ";"
    ;If $PassWord Then $Connection_String &= "Password=" & $PassWord & ";"
    If $PassWord Then $Connection_String &= ";Jet OLEDB:Database Password=" & $PassWord & ";"

    ;$Connection_String = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & $DBPath & ";Jet OLEDB:Database Password=123456789"
    ;$Connection_String = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & $DBPath & ";Jet OLEDB:Database Password=" & $PassWord & ";"

    $objConnection.Open($Connection_String)
    If @error Then  $ConnStatus = "Failed " & @error
    Return $ConnStatus
EndFunc
#EndRegion

KR,

Marcel