Jump to content

Yet another Access UDF( but different - *.accdb only)


kcvinu
 Share

Recommended Posts

Hi all,

This is an UDF for working with Access(2007 above) databases. I would like to say thanks to @spudw2k for his wonderful ADODB udf. That is my inspiration. And i borrowed three functions from him. The difference of this UDF is that it is using SQL statements as parameters. So if you know the SQL, then you can easily work with this UDF.

Functions in this UDF

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

This is an example. This example uses an access database named Test. It is packed with the zip file

#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.14.0
    Author:         kcvinu
    Date :          sep 2015

    Script Function: Examples of Access_UDF
    Template AutoIt script.

#ce ----------------------------------------------------------------------------


#include "Access_UDF.au3"
#include <Array.au3>    ; only for displaying data
Example1()
Func Example1()
    Local $MsgBox1 = MsgBox(4, "Access UDF Examples", " Starting Connection example, Ready ?")
    If $MsgBox1 = 6 Then
        ; We are starting a connection
        Local $Connection = _Start_Connection(@ScriptDir & "\Test.accdb;")
        ConsoleWrite($Connection & " Started" & @CRLF) ; Look for the function status
    Else
        Exit
    EndIf
    Local $MsgBox2 = MsgBox(4, "Access UDF Examples", " Starting Create Table example, Ready ?")
    If $MsgBox2 = 6 Then
        ; We need to create a new table
        Local $CreateTable = _Create_Table("CREATE TABLE TestTable(Column1 Text, Column2 Text);")
        ConsoleWrite($CreateTable & " Table Created" & @CRLF)
    Else
        Exit
    EndIf
    Local $MsgBox3 = MsgBox(4, "Access UDF Examples", " Starting Alter Table example, Ready ?")
    If $MsgBox3 = 6 Then
        ; We need to add a column to that table
        Local $AlterTable = _Alter_Table("ALTER TABLE TestTable ADD Column3 int;")
        ConsoleWrite($AlterTable & " Table Alterd" & @CRLF)
    Else
        Exit
    EndIf
    Local $MsgBox4 = MsgBox(4, "Access UDF Examples", " Starting Insert data example, Ready ?")
    If $MsgBox4 = 6 Then
        ; Insert some data
        Local $InsertData = _Insert_Data("INSERT INTO Table1([Col1], [Col2], [Col3]) VALUES ('AutoIt', 'Coding is Fun', 'AutoIt Rocks');")
        ConsoleWrite($InsertData & " Data inserted" & @CRLF)
    Else
        Exit
    EndIf
    Local $MsgBox5 = MsgBox(4, "Access UDF Examples", " Starting Update table example, Ready ?")
    If $MsgBox5 = 6 Then
        ; Let us update the table
        Local $UpdateData = _Insert_Data("UPDATE Table1 SET Col1 = 'Autoit Is Great', Col2 = 'Coding is Really Fun' WHERE Col3 = 'AutoIt Rocks';")
        ConsoleWrite($UpdateData & " Table Updated" & @CRLF)
    Else
        Exit
    EndIf
    Local $MsgBox6 = MsgBox(4, "Access UDF Examples", " Starting Get records example, Ready ?")
    If $MsgBox6 = 6 Then
        ; Now, collect some data
        Local $GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Rocks' ;")
        _ArrayDisplay($GetData)
    Else
        Exit
    EndIf
    Local $MsgBox7 = MsgBox(4, "Access UDF Examples", " Starting Delete from Table example, Ready ?")
    If $MsgBox7 = 6 Then
        ; Let us delete the whole data from that table, but not the table
        Local $DeleteAll = _Delete_FromTable("DELETE FROM Table1;")
        ConsoleWrite($DeleteAll & " All data deleted from Table" & @CRLF)
    Else
        Exit
    EndIf
    Local $MsgBox8 = MsgBox(4, "Access UDF Examples", " Starting Delete the entire table example, Ready ?")
    If $MsgBox8 = 6 Then
        ; Now, we are going to delete the entire table
        Local $DeleteTable = _Delete_Table("DROP TABLE TestTable;")
        ConsoleWrite($DeleteTable & " Table deleted" & @CRLF)
    Else
        Exit
    EndIf
    ; Last but not least, close the connection.
    _Close_Connection()
    ConsoleWrite("Examples Over...." & @CRLF)


EndFunc   ;==>Example1

Here is the files. 

Access UDF.rar

Access UDF.zip

Edited by kcvinu
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

I like the idea of having a raw SQL statement capability.  Good stuff.  My only question (having not looked at the code in your attachments), looking at your example above, is there a reason you broke out functions for _Create..., _Alter..., _Insert..., instead of having a single function to execute a SQL statement?

i.e. _SQL_Execute("CREATE TABLE....), _SQL_Execute("ALTER TABLE....), etc.

Link to comment
Share on other sites

@spudw2k Thank you for the reply. Your question is good. But I thought this is a UDF. As per your idea, it only contains a few functions. So i thought that to add them as separate functions. Apart from that, newbies won't have difficulty to use it. At last, i have shrink some features to one function. That is _Insert_Data(). You can insert and update and delete data with function. After writing that function, i thought to rename the function. But since i am a lazy man, i did nothing on it. Anyway, i request you to look the _Get_Records() function in main file. I am waiting for your opinion. :)

Edited by kcvinu
grammar
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

A UDF isn't necessarily defined by it's complexity or simplicity.  It's really just a matter of defining functions that are not embedded within the AutoIt interpreter.  Having said that, I will say that a well written UDFs/functions are as modular as possible.  I'll admit, my UDF is a bit old and has had a few updates made since it's inception, so it's probably not as modularized as I would like...but if it ain't broke... :)  Perhaps I'll revisit it sometime.

 

I'll be happy to look at the _Get_Records() func in your code and provide you feedback.

Link to comment
Share on other sites

@kcvinu  Wow, that makes it easy!  I did not know about the .GetRecords method.  I did a quick performance test and that method is about 50% faster!

Good find.  I may have to implement it into my code done. ;)

Edited by spudw2k
Link to comment
Share on other sites

@spudw2k . When i started writing this UDF, i read your UDF. Especially _GetRecords function. It uses 2 ReDim statements. I then decided to search for better ways. Then i downloaded MSDN's ADO object section partially. Especially recordset's methods and properties. Then i got the .GetRows(). So no need to redim the array. very faster.

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

@kcvinu

I appreciate your efforts and progress.

 

If you want to walk the right path, start from: read, understand and use principles mentioned / discussed in the following links:

  1. Best coding practices (wiki)
  2. Best Coding Practices (disscusion)
  3. UDF-Spec (wiki)
  4. UDF-Spec Questions (disscusion)
  5. Global Vars (disscusion)

 

When you finish this first group of 5 points, then it be good time to rewrite your UDF.

This will be also good time, if you try also to follow:

  1. XMLWrapperEx - especialy the ADO_CONSTANTS.au3, COM Handler and all changes I made recently, and all next wich willl happen in future , as this is a good for you as a tutorial "How UDF can be re-written"
  2.  _sql.au3 UDF  as this UDF is strongly ADO related - try to focus on my refreshed version
  3. my all last (max 3 months back) post related to ADO and COM Error Handler also SQL

 

Since my speech I made here in this thread, I can say that:

If you come in trouble, I will be happy to answer under this conditions:

  • The question will be related to the above topics, but at the same time will be referred to the UDF which you presented here.
  • I knew the answer.

 

I made this statement, as I see your work here in this topic is more or less related to my work (I mean ADO).

Best regards,
mLipok

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Link to comment
Share on other sites

@mLipok , Thanks a lot. I will sure check the 5 points you stated above. Actually. i am ignorant about ADO just two weeks ago. But then i got a book named Programming ADO by MS Press. After reading few chapters, i had the confidence to play with ADO. So i decided to reverse engineer spudw2k's ADODB UDF. Yet, ididnt complete the book. I know i have to go more from here. And i am happy with help offered by you. Next time i will sure ask you about ADO doubts. Thank you. :)

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

  • 2 weeks later...

The download file contains an access database file called Test.accdb. Did you check that the access file is there in your script directory ?

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

Just where I was looking for but, i doesn't work.

When i start the example script I get error number 80020009  scriptline 113   source adodb.connection.

 

i.m using Windows 10 with office 2013

I've run into similar issues (can't remember the specific error code) with my code.  It ended up being an x64 issue.  I had to run the script using the x86 version.

Link to comment
Share on other sites

@spudw2k Just check it with x64 compiler. And please let me know the error code. 

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

  • 4 months later...

Hello, great work.

I ran into a problem while using this UDF

Here is the Code....

$Connection = _Start_Connection(@ScriptDir & "\Test.accdb;")
$InsertData = _Insert_Data("INSERT INTO Table1([Col1], [Col2], [Col3]) VALUES ('AutoIt', 'Coding is Fun', 'AutoIt Rocks');")
 $GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Stone' ;")

msgbox(0,'',ubound($GetData))
 $GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Rocks' ;")

msgbox(0,'',ubound($GetData))
_Close_Connection()

Basically I want to modify the Where clause to accept other conditions but I got an error the second time I ran _Get_Records().  Error message "ADODB COM Error  err.description is:  Operation is not allowed when the ojbect is open."  How do I close the object when _Get_Records() returns nothing? 

Anybody know how to fix this without using _Close_Connection() and _Start_Connection() again....

Edited by l4tran
Link to comment
Share on other sites

Hi @l4tran

Start connection and close connection is mandatory. Let me tell you some points.

1. Please post code in appropriate manner. There is a "<>" sign in the upper side of the typing area. It is for adding your code. 

2. After using _Insert_Data, you need to check for error. Just test your $InsertData variable. It will tell you what happened. 

I hope this will help you. :)

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

I using the code from your example.  I don't have any problems with _Insert_Data().

#include "Access_UDF.au3"
#include <Array.au3>
$Connection = _Start_Connection(@ScriptDir & "\Test.accdb;")
$InsertData = _Insert_Data("INSERT INTO Table1([Col1], [Col2], [Col3]) VALUES ('AutoIt', 'Coding is Fun', 'AutoIt Rocks');")
$GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Stone' ;")
msgbox(0,'',ubound($GetData))
$GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Rocks' ;")
msgbox(0,'',ubound($GetData))
_Close_Connection()

 The first _Get_Records() function Where Col3 = 'Autoit Stone' doesn't return an array because it doesn't exist in the table but it leaves the object open, the second time I run _Get_Records() it will throw an error.  See the error message above.

The only way I can make it work is to call _Close_Connection() and _Start_Connection every time _Get_Records() doesn't return an array.

$GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Stone' ;") 
if not(isArray($GetData)) then
    _Close_Connection()
    _Start_Connection(@ScriptDir & "\Test.accdb;")
endif

 

Edited by l4tran
Link to comment
Share on other sites

There is a workaround for this. Just change the Get_Records function in the UDF where 

If Not $objRecordSet.RecordCount Or ($objRecordSet.EOF = True) Then Return -3
If Not $objRecordSet.RecordCount Or ($objRecordSet.EOF = True) Then
    Close_Connection()
    Return -3
 EndIf

 

Edited by kcvinu
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

I think you need to start the connection again. Um. this udf needs some work to fix this. I will fix this when i get free time. THanks for noticing this. :) 

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

  • 3 years later...

Hello kcvinu,

I use the _Get_Records Function from the Access.udf. Have you an idea how i get the column names from the DB Table in the array, (not Col0, Col1 etc.)?

 

Example : _Get_Records("SELECT * FROM TerminalID WHERE PCName ='" & $computer & "';")

image.png.63fd2ccd4448e844ec962e35b099146d.png

 Greetings,

gmmg :)

 

 

 

 

Link to comment
Share on other sites

  • 6 months later...

So years later... 

I have an access database running 2007 format. I have a table and a query that is in the accdb file. Can I call that query that is in the DB instead of creating a query in Auto IT. if so how. 

I am not seeing it in the UDF. 

Link to comment
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
 Share

×
×
  • Create New...