AccessCom.au3

[randallc]

AccessCom.zip

old?

Hi,

I thought i'd post this as a separate thread, now somebody is using it!

Started by stumpii, i think...

Best, Randall

example;

expandcollapse popup

;AccessExs.au3 0_28
Local $o_Con, $o_Con2, $o_Rs
;~ #include "_GUICtrlListView.au3" ;_108
;~ #include "Array2D.au3"
#include "_ArrayView2D1D.au3"
;DATA========================================================
Global $s_dbname = "c:\test.mdb", $s_Tablename = "table1", $s_Tablename2 = "table2", $Fieldname0 = "txt1", $formatT = "Text(50)", $data0 = "Blah Blah Blah"
Global $Fieldname1 = "num1", $formatN = "Number", $data1 = 99, $Fieldname2 = "date1", $formatD = "Date", $data2 = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2)
#include"AccessCOM.au3"
Local $s_db2name = "c:\test2.mdb", $s_Table2name = "Table8"
;CREATE========================================================
If Not FileExists($s_dbname) Then _CreateDB ($s_dbname, "", "")
;CONNECT========================================================
_AccessConnectConn ($s_dbname, $o_Con, 0)
If _TableExists ($o_Con, $s_dbname, $s_Tablename) Then _DropTable ($s_dbname, $s_Tablename, $o_Con)
_CreateTable ($s_dbname, $s_Tablename, $o_Con)
;~ _CreateField($s_dbname, $s_Tablename, "ROWID", $formatN, $o_Con)
_CreateField ($s_dbname, $s_Tablename, $Fieldname0, $formatT, $o_Con)
_CreateField ($s_dbname, $s_Tablename, $Fieldname1, $formatN, $o_Con)
_CreateField ($s_dbname, $s_Tablename, $Fieldname2, $formatD, $o_Con)
; Example of how to add data to a single field of a table
_AddData ($s_dbname, $s_Tablename, $Fieldname0, "OPEN", $o_Con)
_AddData ($s_dbname, $s_Tablename, $Fieldname0, $data0, $o_Con)
_AddData ($s_dbname, $s_Tablename, $Fieldname1, $data1, $o_Con)
_AddData ($s_dbname, $s_Tablename, $Fieldname2, $data2, $o_Con)
;DISPLAY1========================================================
$query = "SELECT * FROM " & $s_Tablename & " IN '" & $s_dbname & "'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_Con)
;~ _ArrayViewQueryTable($ar_Rows,$query)
_ArrayView2D1D ($ar_Rows, $query & "$s_Tablename Array", 1)
; Example of how to list all of the Fields in a particular table
; ******************************************************
;~ $str = _FieldNames($s_dbname, $s_Tablename, $o_Con)
;~ MsgBox(0, "Fields in " & $s_Tablename, $str)
; Example of how to read the info from all records in one field of a table as a string
; ******************************************************
;~ $query = "SELECT * FROM " & $s_Tablename
;~ $strData = _ReadOneField($query, $s_dbname, $Fieldname0, $o_Con)
;~ MsgBox(0, $Fieldname0, $strData)
;DISPLAY2========================================================
;_AddRecord($s_dbname, $s_Tablename1, $ar_array, ByRef $o_adoCon, $ar_FieldFormatsF, $i_adoMDB = 1, $USRName = "", $PWD = "")
$s_Row = "col1|260|25/12/2006"
_AddRecord ($s_dbname, $s_Tablename, $s_Row, $o_Con)
$query = "SELECT * FROM " & $s_Tablename & " IN '" & $s_dbname & "'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
;~ $query = "SELECT RowID, * FROM " & $s_Tablename &" IN '"&$s_dbname&"'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_Con)
;~ _ArrayViewQueryTable($ar_Rows,$query)
_ArrayView2D1D ($ar_Rows, $query & "$s_Tablename Array", 1)
;DISPLAY2========================================================
;_DeleteRecord($s_dbname, $s_Tablename1, $i_DeleteCount, ByRef $o_adoCon,  $i_adoMDB = 1, $USRName = "", $PWD = "")
_DeleteRecord ($s_dbname, $s_Tablename, 2, $o_Con)
$query = "SELECT * FROM " & $s_Tablename & " IN '" & $s_dbname & "'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
;~ $query = "SELECT RowID, * FROM " & $s_Tablename &" IN '"&$s_dbname&"'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_Con)
;~ _ArrayViewQueryTable($ar_Rows,$query)
_ArrayView2D1D ($ar_Rows, $query & "$s_Tablename Array", 1)
;DISPLAY3========================================================
$sQuery = "DELETE FROM " & $s_Tablename & " IN '" & $s_dbname & "' WHERE " & $Fieldname0 & " = 'Col1'"
;~ _ExecuteMDB($s_dbname,$addConn, $sQuery ,$i_adoMDB = 1,$USRName = "", $PWD = "")
_ExecuteMDB ($s_dbname, $o_Con, $sQuery)
$query = "SELECT * FROM " & $s_Tablename & " IN '" & $s_dbname & "'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
;~ $query = "SELECT RowID, * FROM " & $s_Tablename &" IN '"&$s_dbname&"'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_Con)
;~ _ArrayViewQueryTable($ar_Rows,$query)
_ArrayView2D1D ($ar_Rows, $query & "$s_Tablename Array", 1)

Edited December 15, 2006 by randallc

[MagnumXL]

Well I feel like a dumbass being the only one to EVER reply here buy hey! If I wasn't a dumbass would i really need to ask?

When i try to retrieve the fields of a table using:

$str = "TEST EMPLOYEE"
$test = _GetFieldNames($s_dbname,$str,$o_Con)

I get:

Like it only sees the first word of the tables name. What am i doing wrong?

[randallc]

hi,

1. I can't remember SQL queries well these days, but table name with space probably needs "`Test `" with the extra strange quotes?

2. There is a more detailed access script on the forum now somewhere, but I have not the link right now.

Best, Randall

[MagnumXL]

hi,

1. I can't remember SQL queries well these days, but table name with space probably needs "`Test `" with the extra strange quotes?

2. There is a more detailed access script on the forum now somewhere, but I have not the link right now.

Best, Randall

Yes that was exactly it. I just added the ` in 2 places in my test script and it worked like a charm!

For any who read this, that character is the one on the ~ (tilde) key in the upper left of a US standard keyboard.

Thanks Randallc you are teh Mad Genius!

[MagnumXL]

This was originally a question about datatypes for this UDF. I ended up answering my own questions and decided to put them here to help others.

With the _CreateField function several datatypes are possible however the words to use are not listed in the UDF.

Here is what I know so far...

"Long" and "Integer" = long integer.

"Number" or "Numeric" = Decimal

"Date" or "Time" = Date/Time

"Short" = Integer

One quirk that I noticed is that using the word "Text" actually results in the "Memo" datatype. By trial and error I found that you can just use "Text(n)" where n is the fieldsize that you want for your text. This works like a charm.

Hope this helps someone in the future.

Edited July 16, 2008 by MagnumXL

[glasglow]

Inside the _AddRecord Function I changed around line 266 to this:

For $x = 1 To $ar_array - 1
        $o_adoRs.Fields($x + 1).Value = $ar_array[$x]
    Next

With the original the way it was I was getting COM errors (Mismatch)

I'M SORRY THIS ISN"T WORKING EITHER... IT WILL ONLY INSERT THE FIRST 2 COLUMNS

OK OK here is what I got.. same function.. same lines

For $x = 0 To UBound($ar_array) - 1
        $o_adoRs.Fields ($x + 1).Value = $ar_array[$x]
    Next

Edited September 13, 2008 by glasglow