Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

MS Access UDF

DAO ADO msaccess udf database

  • Please log in to reply
15 replies to this topic

#1 aymhenry

aymhenry

    Seeker

  • Active Members
  • 29 posts

Posted 06 May 2012 - 02:02 AM

Starting create another Access UDF

Thanks to GEOSoft.
http://www.autoitscript.com/forum/topic/...ccess%20phone__fromsearch__1#e

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.
http://www.autoitscript.com/forum/topic/...database/page__view__findpost_


Attached File  PhoneBook_rev03.rar   17.8KB   210 downloads
Attached File  AutoIT_DB_Rev02.rar   17.65KB   210 downloads



Attached File  AccessUDF_rev2_1.rar   18.03KB   1391 downloads

Attached File  PhoneBook_rev3_UDF2_1.rar   18.66KB   724 downloads the same example with UDF Rev2.1

Edited by aymhenry, 19 May 2012 - 10:04 AM.








#2 PhoenixXL

PhoenixXL

    be what you are, its always the BEST..

  • MVPs
  • 1,561 posts

Posted 06 May 2012 - 04:05 AM

Nice UDF Thnx......

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. |


#3 aymhenry

aymhenry

    Seeker

  • Active Members
  • 29 posts

Posted 06 May 2012 - 04:07 PM

thanks, kindly feedback me, give proposal for improvement.

#4 ldub

ldub

    Wayfarer

  • Active Members
  • Pip
  • 67 posts

Posted 10 May 2012 - 06:15 AM

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?

#5 stealthf117jm

stealthf117jm

    Seeker

  • Active Members
  • 5 posts

Posted 14 May 2012 - 11:13 PM

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

#6 aymhenry

aymhenry

    Seeker

  • Active Members
  • 29 posts

Posted 15 May 2012 - 07:24 PM

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, 16 May 2012 - 10:19 PM.


#7 Myicq

Myicq

    Universalist

  • Active Members
  • PipPipPipPipPip
  • 270 posts

Posted 07 August 2012 - 02:03 PM

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, 07 August 2012 - 02:21 PM.


#8 aymhenry

aymhenry

    Seeker

  • Active Members
  • 29 posts

Posted 08 August 2012 - 06:52 AM

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
http://www.autoitscript.com/forum/topic/87717-writting-phone-book-using-dao-access-database/page__view__findpost__p__629787

- 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.

#9 psychogr

psychogr

    Seeker

  • Normal Members
  • 1 posts

Posted 11 April 2013 - 12:48 PM

Seeker, thank you very much for this UDF
Works great even with new versions of access.

Keep it up :)

#10 demando

demando

    Seeker

  • Active Members
  • 22 posts

Posted 08 September 2013 - 02:57 PM

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:\DATA\AccessUDF_rev2_1\Access.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?



#11 water

water

    ?

  • MVPs
  • 15,095 posts

Posted 08 September 2013 - 03:00 PM

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.


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

#12 demando

demando

    Seeker

  • Active Members
  • 22 posts

Posted 08 September 2013 - 03:08 PM

Hi water

thank you for the quick answer.

 

Does this UDF general supports accdb Files?

 

I have included Com Error Handler

AutoIt         
#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, 08 September 2013 - 03:45 PM.


#13 water

water

    ?

  • MVPs
  • 15,095 posts

Posted 08 September 2013 - 04:45 PM

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.


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

#14 Emiel Wieldraaijer

Emiel Wieldraaijer

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 703 posts

Posted 08 September 2013 - 07:20 PM

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


Best regards,Emiel WieldraaijerPosted Image

#15 demando

demando

    Seeker

  • Active Members
  • 22 posts

Posted 09 September 2013 - 02:50 AM

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



#16 water

water

    ?

  • MVPs
  • 15,095 posts

Posted 09 September 2013 - 05:29 AM

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.


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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users