MS Access UDF

[aymhenry]

Starting create another Access UDF

Thanks to GEOSoft.

New Functions today 19 Mar. 2012

; New in Rev 2.1

; _AccessErrCode()

; _AccessErrMsg()

;_AccessSelectQuery()

;_AccessActionQuery()

;_AccessRecordMove()

;_AccessFetchData()

Modifications

; Fix bug in _AccessRecordList, bad return value

; Bug in Error Tape for _AccessRecordsCount

; --- Rev 02.1--------------

; Fix by (stealthf117jm) problem while using the function _AccessRecordAdd can not add NULL value.

;--------------------------

; _AccessOpen()

; _AccessClose()

; _AccessTableExists()

; _AccessTablesCount()

; _AccessTablesList()

; _AccessFieldExists

; _AccessFieldsList

; _AccessFieldsCount()

; _AccessRecordsCount()

; _AccessRecordList ()

; _AccessRecordAdd()

; _AccessRecordEdit()

; _AccessRecordDelete ()

Later

; _AccessCreateTable()

; _AccessUpdateTable($oNewDB, $oTable)

; _AccessTabelDelete()

; _AccessQueryLike()

; _AccessQueryStr()

Modification on Access UDF

Rev 01

- Fix bug in _AccessRecordList, bad return value.

- Fix Bug in Error Tape for _AccessRecordsCount

-Add another example, to show how to use UDF. PhoneBook Example.

The old version of PhoneBook could be found in the link.

PhoneBook_rev03.rar

AutoIT_DB_Rev02.rar

AccessUDF_rev2_1.rar

PhoneBook_rev3_UDF2_1.rar the same example with UDF Rev2.1

Edited May 19, 2012 by aymhenry

[PhoenixXL]

Nice UDF Thnx......

[aymhenry]

thanks, kindly feedback me, give proposal for improvement.

[ldub]

Thank you for this very useful code: it works great.

Personally, I would put global variables in the header (the path of the database, the name of the table or tables and field names), it would be more flexible and easier to manage. We could also automate it via code. What do you think?

[stealthf117jm]

Thanks for the UDF!

I found a problem while using the function _AccessRecordAdd. As far as I can tell it does not currently support adding blank/NULL data into a table field. I did some research, and according to MSDN (http://msdn.microsoft.com/en-us/library/ff845624.aspx) the DAO method Recordset.AddNew "sets the fields to default values, and if no default values are specified, it sets the fields to Null".

By making the change below on line 1082 of the Access.au3 it should now support adding NULL by either explicitly setting the array element to "" or leaving it unset. It still errors correctly when the table field is set to "Required" in Access and you attempt to do this.

;From:
$o_Rec.Fields($av_Fields[$nCnt][0]).Value = $av_Fields[$nCnt][1]

;To:
If NOT ($av_Fields[$nCnt][1] == "") Then $o_Rec.Fields($av_Fields[$nCnt][0]).Value = $av_Fields[$nCnt][1]

;Note:  I used
If NOT ($av_Fields[$nCnt][1] == "")
;instead of
If $av_Fields[$nCnt][1] <> ""
;because the later will detect 0 as "" and not allow you to add 0 to a field

[aymhenry]

Dear stealthf117jm

Function _AccessRecordAdd is not supported, see the supported function before.

I did not remove some code, so If I have not an enough time to complete, some else may do the job.

Sorry, for the misunderstanding.

I will consider your valuable modification in the following revision.

It is better to create a group of functions, similar to SQLite.

Edited May 16, 2012 by aymhenry

[Myicq]

Thanks for this great UDF.

Would it be possible to post a simple example on how to connect to a database (f.ex NorthWind Traders), execute a query and display result in a listbox ?

I am struggling a bit with _AccessSelectQuery if there is a WHERE restriction, I always get "too few parameters" error.

Thanks.

Edited August 7, 2012 by Myicq

[aymhenry]

Thanks for this great UDF.

Would it be possible to post a simple example on how to connect to a database (f.ex NorthWind Traders), execute a query and display result in a listbox ?

I am struggling a bit with _AccessSelectQuery if there is a WHERE restriction, I always get "too few parameters" error.

Thanks.

- Yes, you check the old revision of my PhoneBook Example. See

- For _AccessSelectQuery, I think you can apply SQL statment like :-

Select * from myTable Where myField=32;

See the example file.

please try and feedback.

- you can read the data form a .mdb to a variable and put it back to listbox. as normal.

[psychogr]

Seeker, thank you very much for this UDF

Works great even with new versions of access.

Keep it up

[demando]

Hi togehter

My OS is Windows 7 32bit

i downloaded the version ==> AccessUDF_rev2_1.rar and testet with a mdb Database. All works correctly.

When i try with an accdb Database i get following error in the _AccessOpen Function

 

C:DATAAccessUDF_rev2_1Access.au3 (272) : ==> The requested action with this object has failed.:
$o_doc = $o_object.OpenDatabase($s_FilePath, $Options, $ReadOnly, $Connect)
$o_doc = $o_object.OpenDatabase($s_FilePath, $Options, $ReadOnly, $Connect)^ ERROR

Does this UDF don't support Access 2007 Databases?

[water]

Add a COM error handler to your script and you will get more detailed error information.

Check the help file for ObjEvent for explanation and examples.

[demando]

Hi water

thank you for the quick answer.

Does this UDF general supports accdb Files?

I have included Com Error Handler

#include "Access.au3"

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ; COM-Fehlerbehandlung initialisieren
MsgBox (0, "Ex.1", "Example_AccessOpen()")
    Example_AccessOpen()

Func Example_AccessOpen()
    Local $o_DataBase = _AccessOpen(@ScriptDir & "\Test_Access_2007.accdb")
    If $o_DataBase = 0 Then
        MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\Test_Access_2007.accdb")
        Return
    Else
        MsgBox(0, "Information", "Database file was opened :-" & @CR & @ScriptDir & "\Test_Access_2007.accdb")
    EndIf

    _AccessClose($o_DataBase)
EndFunc   ;==>Example_AccessOpen

Func MyErrFunc()

    MsgBox(0, "AutoItCOM Test", "COM Fehler abgefangen !" & @CRLF & @CRLF & _
            "err.description ist: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number ist: " & @TAB & Hex($oMyError.number, 8) & @CRLF & _
            "err.lastdllerror ist: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline ist: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source ist: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile ist: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext ist: " & @TAB & $oMyError.helpcontext _
            )

    Local $err = $oMyError.number
    If $err = 0 Then $err = -1

    $g_eventerror = $err ; Zum Abfragen, nachdem die Funktion beendet ist
EndFunc   ;==>MyErrFunc

When i execute this script the err.descripton give following message:

Unrecognized databas format.

 

Edited September 8, 2013 by demando

[water]

I'm not familiar with the Access UDF. More with COM in general.

According to the error message the UDF doesn't support this file format.

[Emiel Wieldraaijer]

Correct this UDF only supports old type of Access Databases  2000-2003

[demando]

Is there another way to open Access 2007 and Access 2010 Databases (accdb Format) ?

[water]

Maybe you could open the database yourself and then work with the functions of the Access UDF.

Searching the forum for "accdb" returned >this result.

[kcvinu]

Can i get a version for accdb files ?

[argumentum]

well, the OP was last seen on 9 Aug 2012 , and I wanted to get the details of a DB in .accdb , so I added the ability to open them.
also expanded _AccessFieldsList() to show more detail.

So I'll cal this version "PhoneBook_rev3_UDF2_1b.zip" and sharing for the next person that may need it. It's not a full review of all the functions, just the ones I mentioned.

PhoneBook_rev3_UDF2_1b.zip

[kcvinu]

@argumentum , I have made an UDF for accdb files. 

[argumentum]

@argumentum , I have made an UDF for accdb files. 

the UDF you made say that it can:

1. _Start_Connection
2. _Close_Connection
3. _Create_Table
4. _Delete_Table
5. _Alter_Table
6. _Delete_FromTable
7. _Insert_Data - You can use this to update or delete data
8. _Get_Records

and I needed the _AccessFieldsList() from this UDF, which I expanded to include details.
( and later found the .accdb support for that, can be added with 4 extra lines of code )

Edited November 21, 2015 by argumentum
mistyped