Sign in to follow this  
Followers 0
GEOSoft

MSAccess UDF [updated]

124 posts in this topic

#1 ·  Posted (edited)

There is more to come but here is what I have so far

; _accessCompactDB() *

; _accessCreateDB()

; _accessCreateTable()

; _accessDeleteTable()

; _accessListTables()

; _accessCountTables()

; _accessAddRecord()

; _accessUpdateRecord()

; _accessDeleteRecord()

; _accessClearTable()

; _accessCountRecords()

; _accessCountFields()

; _accessListFields()

; _accessQueryLike()

; _accessQueryStr()

; _accessSaveXML()

In progress:

_accessAppendField()

_accessModifyField()

_accessDeleteField()

_accessQueryNum()

_accessSortRecordset()

_accessCompactDB()

If you change or add to this UDF please post it or PM it to me for inclusion in the UDF. Please follow the UDF guidlines

Edit:

These functions have all been tested but not under all posible scenarios.

Enjoy

Edit #2 Functions marked with a * have been added

Edit #3 UDF Updated with new functions. Code cleanup, error handling and it now uses _adoOpen() and _adoOpenRecordset in most functions.

Edit #4 Attachment Removed. Download from

ADO.zip

EDIT #5 ***** Important Changes (script breakers)

(1) The functions have all been renamed. This was actually done a while back and anyone that did not follow the link on my site will have the wrong file and the wrong functions. My appologies. This also explains why many people were having some difficulties.

(2) The file ADO.zip is no longer valid Please download the new file by going to my site (in my sig) and in the left menu click Code>>My Extra UDFs>>Access.au3. At the bottom of the page is a download link.

(3) The constants have been removed from the au3 file. It now requires that you #include <AccessConstants.au3> which must be in your AutoIt3Include folder.

EDIT #6 *****IMPORTANT

If you have questions about this UDF please start a thread in the General Support forum instead of cluttering this thread.

This UDF is now out of development and I am no longer supporting it.

because people have a hard time understanding that i won't support this udf any longer; i've chosen to remove it entirely

Edited by GEOSoft
1 person likes this

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites



The UDF has been updated with new functions. I'll try to do the rest in the next day or so.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Download updated with added functions and more error handling. See Post #1


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Hi!

Do you have any examples of use ? :whistle:

Regards

Trolderik

Share this post


Link to post
Share on other sites

Nice UDF Trolderik, I will try\test it soon.

Regards,

RK


"When the power of love overcomes the love of power, the world will know peace"-Jimi Hendrix

Share this post


Link to post
Share on other sites

Hi!

Do you have any examples of use ? :whistle:

Regards

Trolderik

There are several in the file but I'll make some more and add an examples file

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

very usefull !

keep up the good work

Share this post


Link to post
Share on other sites

Hi

here is my function to compact mdb files

CODE

; Compact an access database

; required At least MDAC 2.1

; No access installation is needed

;

; $Destination must be different from $Source

;

; $Replace

; 0 => keep $Source and $Destination file

; 1 => replace $Source with $Destination ; keep $Destination file

; 2 => replace $Source with $Destination ; remove $Destination file

;

func _CompactMDB($Source,$Destination, $Replace=0)

If FileExists($Source) Then

if FileExists($Destination)=0 Then

$oMDB = ObjCreate("JRO.JetEngine")

If IsObj($oMDB) Then

$oMDB.CompactDatabase( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $Source, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $Destination)

EndIf

Switch $Replace

case 1

filecopy( $Destination, $Source, 1+8)

case 2

filemove( $Destination, $Source, 1+8)

EndSwitch

EndIf

EndIf

endFunc

Share this post


Link to post
Share on other sites

Hi

here is my function to compact mdb files

CODE

; Compact an access database

; required At least MDAC 2.1

; No access installation is needed

;

; $Destination must be different from $Source

;

; $Replace

; 0 => keep $Source and $Destination file

; 1 => replace $Source with $Destination ; keep $Destination file

; 2 => replace $Source with $Destination ; remove $Destination file

;

func _CompactMDB($Source,$Destination, $Replace=0)

If FileExists($Source) Then

if FileExists($Destination)=0 Then

$oMDB = ObjCreate("JRO.JetEngine")

If IsObj($oMDB) Then

$oMDB.CompactDatabase( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $Source, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $Destination)

EndIf

Switch $Replace

case 1

filecopy( $Destination, $Source, 1+8)

case 2

filemove( $Destination, $Source, 1+8)

EndSwitch

EndIf

EndIf

endFunc

Looks good I'll add it to the UDF. I'll just modify a couple of strings so that it works the same as the other functions. (Provider &etc.

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Hi

here is my function to compact mdb files

I like that, but did you know there is a simple .exe that will compact an access database for you? It is called jetcomp.exe. It has both a gui and command line use. I use it in a batch file to compact & backup. Syntax is simple for command line
jetcomp.exe -src:c:\path -dest:c:\path

Just thought you might like to know.

Sul

Share this post


Link to post
Share on other sites

I like that, but did you know there is a simple .exe that will compact an access database for you? It is called jetcomp.exe. It has both a gui and command line use. I use it in a batch file to compact & backup. Syntax is simple for command line

jetcomp.exe -src:c:\path -dest:c:\path

Just thought you might like to know.

Sul

Handy to know but it doe not appear to work on all mdb files

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Hey! I am wondering if autoit could work with an mssql db such as:

CREATE TABLE dbo.switchfinder (

id int IDENTITY NOT NULL ,

input1 (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

input2 (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

input3 text COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

input4 text COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

input5 text COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

input6 text COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

input7 text COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

input8 text COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

phone text COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

as the db table. and a db connection like this:

<?

$dbname = "TCMC_MAINDATA";

$dbserver = "MMEASHSQLV04";

$dbuser = "tcmc_web_user";

$dbpass = "Ack$$2webdata";

$table = "dbo.switchfinder"

include("include/config.php");

$db = mssql_connect("$dbserver", "$dbuser", "$dbpass");

mssql_select_db("$dbname",$db);

?>

this is an example of php/mssql db connection. Any ideas?

Jim

Share this post


Link to post
Share on other sites

Great UDF with many useful features.

I was wondering if there has been any through to the reports in access. Adding, deleting and updating tables and data is only part of what Access can do. If there were an easy way (may be one just I haven't figured it out yet ) to get to the reports without the need to actually open access on the system that would be great.

Just a thought.

Share this post


Link to post
Share on other sites

Great UDF with many useful features.

I was wondering if there has been any through to the reports in access. Adding, deleting and updating tables and data is only part of what Access can do. If there were an easy way (may be one just I haven't figured it out yet ) to get to the reports without the need to actually open access on the system that would be great.

Just a thought.

Right now I have not had the time to do more with this UDF but I have a couple of form functions done and when the forms are finished I'll be moving on to reports.

Thanks for your comments.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

built a function to dynamically create a listview from a db query.

Function: _createDBlistView()

Uses getRows() method which returns db recordset as an array.

maybe you can use some of it in your UDF.....

the demo gui:

#include <GUIConstants.au3>
#include "_DBlistView.au3"

Opt("GUIOnEventMode", 1)  ; OnEvent mode 
Dim $title="Access db Viewer" ;gui title

Dim $gui = GUICreate($title, 800, 600)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked")

$DB=@ScriptDir & "\northwind.mdb" ;modify to location of your .mdb //does'nt handle relationships

$Query="Select * From EmployeeTerritories Order by EmployeeID ASC" ;modify your query

$Number_of_Records_to_Display = 25 ;these variables are self explanitory...
$Listview_Left = 50
$Listview_Top = 50
$Listview_Width = 400
$Listview_Height = 400
$Listview_style = $GUI_SS_DEFAULT_LISTVIEW ;default is -1
$Listview_exStyle = $LVS_EX_FULLROWSELECT + $LVS_EX_GRIDLINES ;default is -1
;call the function
_createDBlistView($DB,$Query,$Number_of_Records_to_Display,$Listview_Left,$Listview_Top,$Listview_Width,$Listview_Height,$Listview_style,$Listview_exStyle)

GUISetState ()

While 1
  Sleep(1000)  ; Idle around
WEnd 

Func CLOSEClicked()
      Exit   
EndFunc
Edited by Will66

Share this post


Link to post
Share on other sites

Thanks @Will66

I'll see what I can find to use but it looks promising.

I'll be doing an update to this UDF soon. I'm just too busy at the moment.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

I may be doing something wrong or I found a bug? Either way, my code to add a table to the access database does not appear to work. I was hoping maybe someone could help me out?

Thanks!

ERROR:

C:\PROGRA~1\AutoIt3\Include\ADO.au3 (113) : ==> The requested action with this object has failed.:

$oADO.Execute ("CREATE TABLE " & $adTable & '(' & $F_Out & ')')

$oADO.Execute ("CREATE TABLE " & $adTable & '(' & $F_Out & ')')^ ERROR

Code that is generating the error:

Func CreateTables()

$adSource = "c:\test123.mdb"

$adTable = "TestBox"

_adoCreateTable($adSource, $adTable, 'EventID TEXT|Date TEXT|Time TEXT|Request TEXT|IP TEXT|ComputerName TEXT|MAC TEXT')

;_adoCreateTable($adSource, $adTable, "'EventID TEXT|Date TEXT|Time TEXT|Request TEXT|IP TEXT|ComputerName TEXT|MAC TEXT'")

EndFunc

Share this post


Link to post
Share on other sites

I got this working...

_adoCreateTable($adSource, $tableName, "EventID TEXT(3) | DateOfLease TEXT(10) | TimeOfLease TEXT(10) | RequestType TEXT(8) | IP TEXT(20) | ComputerName TEXT(40) | MAC TEXT(20)")

It seems like my choice of columns was not acceptable - when I changed the names things appeared to work correctly.

Thanks!

Share this post


Link to post
Share on other sites

I got this working...

_adoCreateTable($adSource, $tableName, "EventID TEXT(3) | DateOfLease TEXT(10) | TimeOfLease TEXT(10) | RequestType TEXT(8) | IP TEXT(20) | ComputerName TEXT(40) | MAC TEXT(20)")

It seems like my choice of columns was not acceptable - when I changed the names things appeared to work correctly.

Thanks!

Glad you fixed it. Since Date and Time are both valid Column types you can not use them as a field label you can use Date1 or _Date as labels.

Also be carefull of those Text field sizes. As a rule I use a field size 50% larger than the expected string length. If you use the field type MEMO MS recommends that they be placed last in the field list but I have never had a problem with using MEMO in other locations


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

I miss some functions for opening the Connection and RecordSet.

For the Connection, I miss the possibility for password

For the recordset, I miss the Cursor and Locktype, 0 and 1 when reading, 2 and 3 when updating.

See attached code as I have in my scripts.

Func _SQLadoConOpen($sPwd,$sDataSource)
    Local $iAdoCon
    $iAdoCon = ObjCreate("ADODB.Connection")
    $iAdoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password='" & $sPwd & "'; Data Source=" & $sDataSource)
    If @error then SetError(1)
    Return $iAdoCon
EndFunc
; =============================
Func _SQLadoConClose($sAdoCon)
    $sAdoCon.close
EndFunc
; =============================
Func _SQLadoRsOpen($iAdoCon,$sAdoSQL,$iCursorType = 0 ,$iLockType = 1, $iShowSQLsentence = 0)
    Local $iAdoRs
    If $iShowSQLsentence = 1 Then
        msgbox(0,"SQL",$sAdoSQL)
    EndIf
    $iAdoRs = ObjCreate ("ADODB.Recordset")
    $iAdoRs.CursorType = $iCursorType
    $iAdoRs.LockType = $iLockType
    $iAdoRs.Open($sAdoSQL, $iAdoCon)
    If @error then SetError(1)
    Return $iAdoRs
EndFunc
; =============================
Func _SQLadoRsClose($sAdoRs)
    $sAdoRs.close
EndFunc

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
Sign in to follow this  
Followers 0