kcvinu

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

18 posts in this topic

#1 ·  Posted (edited)

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

My Contributions

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)

 

Share this post


Link to post
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.

1 person likes this

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

@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

My Contributions

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)

 

Share this post


Link to post
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.

1 person likes this

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

Share this post


Link to post
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.


My Contributions

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)

 

Share this post


Link to post
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


ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button:

Spoiler

Last update: 2016-12-23
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 Components * TeamViewer API -UDF * JavaManagement UDF * VIES over SOAPPOP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF * WinSCP UDF *

My contribution to others projects: * _sql.au3 UDF  *

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 * Best coding practices * 

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 *

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 *

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

 

Share this post


Link to post
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. :)


My Contributions

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)

 

Share this post


Link to post
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


My scripts: _ConsoleWriteLog | _FileArray2D

 

 

 

Share this post


Link to post
Share on other sites

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


My Contributions

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)

 

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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


My Contributions

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)

 

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

Share this post


Link to post
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. :)


My Contributions

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)

 

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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

My Contributions

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)

 

Share this post


Link to post
Share on other sites

Doesn't work. Return the same error.

Even if it did, wouldn't I need to _Start_Connection() again?  That is just the same as this...

$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

 

Share this post


Link to post
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. :) 


My Contributions

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)

 

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

  • Similar Content

    • Queener
      By Queener
      Using the MSSQL.udf
      I asked this question once, but haven't had a chance to reply to my old post so posting a new one instead of bouncing the old post.
      I'm trying to retrieve data from my database in Microsoft SQL Server 2014. When I tried the code below; empty message box return and no error approaches.
      $Connection = _MSSQL_Con($ServerAddress, $ServerUsername, $ServerPassword, $ServerDBName) $Testquery = _MSSQL_Query($Connection, "SELECT User_ID From Banker where Manager like '%Jason%'") msgbox(0, "Test", $Testquery) exit
    • Bowmore
      By Bowmore
      A few months ago someone posted a function that scanned a PC registry and returned a list of all the available ADODB connections installed. I've been searching on and off all afternoon trying to locate it. Does anyone remember which topic this was in? As far as my failing memory goes it was a post part way through an ADO related topic.
      Thanks
       
    • mLipok
      By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       

      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       

       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
       
       
    • mLipok
      By mLipok
      Currently I'm working on MS SQL >> PostgreSQL migration.
      Here are some of interesting Video tutorials about PostgreSQL
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
    • mLipok
      By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      Support topic is here: http://www.autoitscript.com/forum/index.php?showtopic=180850
       
      This UDF is modifed version of _sql.au3 UDF.

      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH