MSAccess UDF [updated]

[GEOSoft]

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 March 15, 2012 by GEOSoft

[GEOSoft]

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

[GEOSoft]

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

[Trolderik]

Hi!

Do you have any examples of use ?

Regards

Trolderik

[rbhkamal]

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

Regards,

RK

[GEOSoft]

Hi!

Do you have any examples of use ?

Regards

Trolderik

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

[jpam]

very usefull !

keep up the good work

[Krem]

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

[GEOSoft]

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.

[sulfurious]

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

[GEOSoft]

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

[gseller]

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

[ChuckS]

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.

[GEOSoft]

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.

[Will66]

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 April 22, 2007 by Will66

[GEOSoft]

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.

[duijver]

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

[duijver]

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!

[GEOSoft]

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

[Wooltown]

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