aymhenry Posted May 6, 2012 Share Posted May 6, 2012 (edited) Starting create another Access UDFThanks 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 UDFRev 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.rarAutoIT_DB_Rev02.rarAccessUDF_rev2_1.rarPhoneBook_rev3_UDF2_1.rar the same example with UDF Rev2.1 Edited May 19, 2012 by aymhenry Link to comment Share on other sites More sharing options...
PhoenixXL Posted May 6, 2012 Share Posted May 6, 2012 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. Link to comment Share on other sites More sharing options...
aymhenry Posted May 6, 2012 Author Share Posted May 6, 2012 thanks, kindly feedback me, give proposal for improvement. Link to comment Share on other sites More sharing options...
ldub Posted May 10, 2012 Share Posted May 10, 2012 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? Link to comment Share on other sites More sharing options...
stealthf117jm Posted May 14, 2012 Share Posted May 14, 2012 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 1 Link to comment Share on other sites More sharing options...
aymhenry Posted May 15, 2012 Author Share Posted May 15, 2012 (edited) Dear stealthf117jmFunction _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 Link to comment Share on other sites More sharing options...
Myicq Posted August 7, 2012 Share Posted August 7, 2012 (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 August 7, 2012 by Myicq I am just a hobby programmer, and nothing great to publish right now. Link to comment Share on other sites More sharing options...
aymhenry Posted August 8, 2012 Author Share Posted August 8, 2012 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. Link to comment Share on other sites More sharing options...
psychogr Posted April 11, 2013 Share Posted April 11, 2013 Seeker, thank you very much for this UDF Works great even with new versions of access. Keep it up Link to comment Share on other sites More sharing options...
demando Posted September 8, 2013 Share Posted September 8, 2013 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? Link to comment Share on other sites More sharing options...
water Posted September 8, 2013 Share Posted September 8, 2013 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: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
demando Posted September 8, 2013 Share Posted September 8, 2013 (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 September 8, 2013 by demando Link to comment Share on other sites More sharing options...
water Posted September 8, 2013 Share Posted September 8, 2013 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: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Emiel Wieldraaijer Posted September 8, 2013 Share Posted September 8, 2013 Correct this UDF only supports old type of Access Databases 2000-2003 Best regards,Emiel Wieldraaijer Link to comment Share on other sites More sharing options...
demando Posted September 9, 2013 Share Posted September 9, 2013 Is there another way to open Access 2007 and Access 2010 Databases (accdb Format) ? Link to comment Share on other sites More sharing options...
water Posted September 9, 2013 Share Posted September 9, 2013 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: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
kcvinu Posted April 15, 2015 Share Posted April 15, 2015 Can i get a version for accdb files ? Spoiler My Contributions Glance GUI Library - A gui library based on Windows api functions. Written in Nim programming language. UDF Link Viewer --- A tool to visit the links of some most important UDFs Includer_2 ----- A tool to type the #include statement automatically Digits To Date ----- date from 3 integer values PrintList ----- prints arrays into console for testing. Alert ------ An alternative for MsgBox MousePosition ------- A simple tooltip display of mouse position GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function Access_UDF -------- An UDF for working with access database files. (.*accdb only) Link to comment Share on other sites More sharing options...
argumentum Posted November 20, 2015 Share Posted November 20, 2015 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 Follow the link to my code contribution ( and other things too ). FAQ - Please Read Before Posting. Link to comment Share on other sites More sharing options...
kcvinu Posted November 21, 2015 Share Posted November 21, 2015 @argumentum , I have made an UDF for accdb files. argumentum 1 Spoiler My Contributions Glance GUI Library - A gui library based on Windows api functions. Written in Nim programming language. UDF Link Viewer --- A tool to visit the links of some most important UDFs Includer_2 ----- A tool to type the #include statement automatically Digits To Date ----- date from 3 integer values PrintList ----- prints arrays into console for testing. Alert ------ An alternative for MsgBox MousePosition ------- A simple tooltip display of mouse position GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function Access_UDF -------- An UDF for working with access database files. (.*accdb only) Link to comment Share on other sites More sharing options...
argumentum Posted November 21, 2015 Share Posted November 21, 2015 (edited) @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_Recordsand 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 microera 1 Follow the link to my code contribution ( and other things too ). FAQ - Please Read Before Posting. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now