MS Access UDF


17 posts in this topic

Posted (edited)

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

Share this post


Link to post
Share on other sites



Posted

Nice UDF Thnx......

Share this post


Link to post
Share on other sites

Posted

thanks, kindly feedback me, give proposal for improvement.

Share this post


Link to post
Share on other sites

Posted

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?

Share this post


Link to post
Share on other sites

Posted

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
1 person likes this

Share this post


Link to post
Share on other sites

Posted (edited)

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

Share this post


Link to post
Share on other sites

Posted (edited)

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

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

Seeker, thank you very much for this UDF

Works great even with new versions of access.

Keep it up :)

Share this post


Link to post
Share on other sites

Posted

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?

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted (edited)

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

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

Can i get a version for accdb files ?

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

  • Recently Browsing   0 members

    No registered users viewing this page.