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

[kcvinu]

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

expandcollapse popup

#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 September 24, 2015 by kcvinu

[spudw2k]

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.

[kcvinu]

@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 September 24, 2015 by kcvinu
grammar

[spudw2k]

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.

[spudw2k]

@‌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 September 24, 2015 by spudw2k

[kcvinu]

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

[mLipok]

@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

[kcvinu]

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

[SnArF]

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

[kcvinu]

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

[spudw2k]

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.

[kcvinu]

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

[l4tran]

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 February 26, 2016 by l4tran

[kcvinu]

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.

[l4tran]

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 February 27, 2016 by l4tran

[kcvinu]

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 February 27, 2016 by kcvinu

[l4tran]

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

 

[kcvinu]

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.  

[gmmg]

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 & "';")

 Greetings,

gmmg

 

 

 

 

[Shioon]

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.