MS Access UDF


17 posts in this topic

#1 ·  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



#2 ·  Posted

Nice UDF Thnx......


My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Share this post


Link to post
Share on other sites

#3 ·  Posted

thanks, kindly feedback me, give proposal for improvement.

Share this post


Link to post
Share on other sites

#4 ·  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

#5 ·  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

#6 ·  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

#7 ·  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

I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites

#8 ·  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

#9 ·  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

#10 ·  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

#11 ·  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.


My UDFs and Tutorials:

UDFs:
Active Directory (2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (NEW 2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

Share this post


Link to post
Share on other sites

#12 ·  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

#13 ·  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.


My UDFs and Tutorials:

UDFs:
Active Directory (2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (NEW 2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

Share this post


Link to post
Share on other sites

#14 ·  Posted

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


Best regards,Emiel Wieldraaijer

Share this post


Link to post
Share on other sites

#15 ·  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

#16 ·  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.


My UDFs and Tutorials:

UDFs:
Active Directory (2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (NEW 2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

Share this post


Link to post
Share on other sites

#17 ·  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