MSAccess UDF [updated]

[duijver]

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

Thanks! That is very good to know.

[2tim3_16]

First of all, thanks for the UDF! I've got two questions about the _adoAddRecord function.

I'm not sure if this is me or your code, but I can only get it to add data to the first field in a record. Maybe I'm not understanding your function, but I thought that by using 0, 1, 2, etc. you could specify which field to place the data in. So for instance,

_adoAddRecord($adSource, $adTable, $rData, 2)

would place the data in the 3rd field. Is this not correct? Also, it appears from your description that you can also substitute a field name. But no matter what I try, with a field index number or a field name, the data always goes in the first field.

My second question is about adding data to multiple fields in a single record. I gathered from your code that it's something like

$rData="Data1|Data2|Data3"

but that gives me the following error:

C:\Program Files\Life Action\ADO.au3 (285) : ==> The requested action with this object has failed.: 
.Fields.Item($I -1) = $rData[$I] 
.Fields.Item($I -1) = $rData[$I]^ ERROR
->18:22:01 AutoIT3.exe ended.rc:1

One other note, _adoUpdateRecord has

$aADO.Close()

instead of

$oADO.Close()

in the @Error section. I had to fix that before I could use the UDF.

Thanks for your help!

[GEOSoft]

First of all, thanks for the UDF! I've got two questions about the _adoAddRecord function.

I'm not sure if this is me or your code, but I can only get it to add data to the first field in a record. Maybe I'm not understanding your function, but I thought that by using 0, 1, 2, etc. you could specify which field to place the data in. So for instance,

_adoAddRecord($adSource, $adTable, $rData, 2)

would place the data in the 3rd field. Is this not correct? Also, it appears from your description that you can also substitute a field name. But no matter what I try, with a field index number or a field name, the data always goes in the first field.

My second question is about adding data to multiple fields in a single record. I gathered from your code that it's something like

$rData="Data1|Data2|Data3"

but that gives me the following error:

C:\Program Files\Life Action\ADO.au3 (285) : ==> The requested action with this object has failed.: 
.Fields.Item($I -1) = $rData[$I] 
.Fields.Item($I -1) = $rData[$I]^ ERROR
->18:22:01 AutoIT3.exe ended.rc:1

One other note, _adoUpdateRecord has

$aADO.Close()

instead of

$oADO.Close()

in the @Error section. I had to fix that before I could use the UDF.

Thanks for your help!

I haven't got that code even handy at the moment but I'll have a look at it. From memory I added a string Split in there after the description was written so that might be causing confusion. I do know that when I pass it a record (with 16 fields) that I just pas it an array with 16 elements making sure that the fields with no data are just empty elements. As soon as I get a chance, probably tomorrow, I'll pull up the archive and have a look at it again.

For the

$oADO.Close()

I fixed that but I might not have changed the upload. I'll take care of that. Thanks. Edited July 2, 2007 by GEOSoft

[2tim3_16]

Thanks, GEOSoft. I just tried using a simple array for $rData, and it's dropping the first element, putting the second element in the first field, the third element in the second field, and so on. But if I add an extra element at the beginning of the array, I get an error, I assume because the number of elements doesn't match the number of fields. It appears that the same thing is happening when I separate the values with a pipe; I believe it is assuming that $rData[0] contains the first value rather than the number of elements, thus causing an error because the number of elements and the number of fields don't match. So I can't put any data in the last field in a table.

Also, using _adoCreateTable, if you specify a field type of TEXT but do not specify a field length, it creates a MEMO field instead. Not sure if that's your code or if that's Microsoft though.

Edited July 3, 2007 by 2tim3_16

[Will66]

Hi GEOSoft,

I'm not actually using your UDF on this project but though this is appropriate place to ask:

When a field contains a date autoit seems to return the date without the forward slashes, eg: 20070710000000 instead of 2007/07/10 00:00:00

I've looked at the functions in <date.au3> but none appear to handle this format.

I'm not sure why autoit strips out the slashes?, the db field is set to date/Time with no formatting mask set.

Change $DB and $SQL variable values to work against any access db.

See line 62:

expandcollapse popup

#include <GUIConstants.au3>
#include <Date.au3>
#include <IE.au3>
_IEErrorHandlerRegister ()
Dim $oIE = _IECreateEmbedded ()
Dim $sHtml
Const  $adUseClient = 3;

Opt("GUIOnEventMode", 1) ; OnEvent mode
Opt("GUICoordMode",2)
Dim $title="Access db Viewer";gui title
Dim $DB=@ScriptDir & "\db\railway_reservations_db2.mdb";modify to location of your .mdb 
Dim $gui = GUICreate($title, 800, 600)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked")
Dim $GUIActiveX = GUICtrlCreateObj($oIE, 10, 10,750, 550)
_IENavigate ($oIE, "about:blank")
Dim $oBody = _IETagNameGetCollection($oIE, "body", 0)
GUISetState ()
start()
While 1
  Sleep(1000) ; Idle around
WEnd

    Func CLOSEClicked()
      Exit   
  EndFunc
  
  Func start()
    Out("<html><head>")
    Out("<style>body{color:black;border:0;background:#C0C0C0;margin:0} td{padding:3px;background:#FFF;font-size:9px;font-weight:heavy}</style>")
    Out("</head><body>")
    Dim  $MyDate,$MyTime
    $DisplayNum = 0;
    $recdisp = 10; //records per page
    $oCon = CreateConnection(); 
    $Rec = ObjCreate("ADODB.Recordset");
    $Rec.PageSize = $recdisp;
    $Rec.CursorLocation = $adUseClient; 
    $SQL  = "SELECT *FROM trains;";
    $Rec.Open( $SQL, $oCon);
    if $Rec.EOF Or $Rec.BOF Then
        Out("<p align=center><b>Sorry, There is no trains ... <a href=insert.html>add new train</a></b></p>");
    Else
        $headers=""
        $fields=""
        
        Out("<table border=""1""><tr>")
        For $tablefield in $Rec.fields
        Out("<td>" & $tablefield.name & "</td>")
    next
    Out("</tr>")
        $data=$Rec.GetRows()
        $iRows = UBound($data, 1)-1
        $iCols = UBound($data, 2)-1
    ;MsgBox(0,"",$iCols)
        For $rows=0 to $iRows
            Out("<tr>")
                For $cols = 0 to $iCols                 
                    $temp = $data[$rows][$cols]
                    If $temp = "" Then $temp="--"
                    If $Rec.Fields($cols).type=7 Then;if date/time field format appropriatley eg vbscript's FormatDateTime(CDate(date),0)
                        
                    EndIf
                    
                    Out("<td>" & $temp & "</td>") 
                Next
            Out("</tr>")
        next
        Out("</table>")
        
    EndIf
    Out("</body></html>")
    _IEDocWriteHTML($oIE, $sHtml)
    _IEAction($oIE,"refresh")
    $Rec.Close();
    $oCon.Close();
;msgbox(0,"",_IEDocReadHTML  ($oIE) )
    
    EndFunc
  
  Func CreateConnection() 

    $Conn = ObjCreate("ADODB.Connection")
    $Conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $DB & ";");    
    $Conn.CursorLocation = $adUseClient;
    return $Conn;
    EndFunc
  
  
  Func Out($temp)
      $sHtml &= $temp & @CRLF
  EndFunc

[SmOke_N]

$sString = '20070710000000'
MsgBox(0, '', _ConvertMyProblem($sString))
Func _ConvertMyProblem($sString)
    Local $aString[6] = ['/','/',' ',':',':',''], $sHold
    Local $aSRE = StringRegExp($sString, '(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})', 3)
    If IsArray($aSRE) = 0 Then Return SetError(1, 0, '')
    For $iCC = 0 To UBound($aSRE) -1
        $sHold &= $aSRE[$iCC] & $aString[$iCC]
    Next
    Return $sHold
EndFunc

[Will66]

$sString = '20070710000000'
MsgBox(0, '', _ConvertMyProblem($sString))
Func _ConvertMyProblem($sString)
    Local $aString[6] = ['/','/',' ',':',':',''], $sHold
    Local $aSRE = StringRegExp($sString, '(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})', 3)
    If IsArray($aSRE) = 0 Then Return SetError(1, 0, '')
    For $iCC = 0 To UBound($aSRE) -1
        $sHold &= $aSRE[$iCC] & $aString[$iCC]
    Next
    Return $sHold
EndFunc

Thanx SmOke:

If $Rec.Fields($cols).type=7 Then ;if date/time field format appropriatley eg vbscript's FormatDateTime(CDate(date),0)

$temp = _ConvertMyProblem($temp)

$temp = _DateTimeFormat( $temp,2)

EndIf

[2tim3_16]

Hi again, GEOSoft. I've been running into errors with _adoUpdateRecord now. I haven't been able to figure out what's causing them, and it doesn't tell me what the error is, just that the function failed. It doesn't fail all the time, and I haven't yet found a pattern so that I can deduce what is causing the error. Anyway, I rewrote the function as follows, and now it's working correctly.

Func _adoUpdateRecord2($adSource,$adTable,$adCol,$adQuery,$adcCol,$adData)
    $adQuery = Chr(39) & $adQuery & Chr(39)
    $oADO = 'ADODB.Connection'
    If IsObj($oADO) Then
        $oADO = ObjGet('',$oADO)
    Else
        $oADO = _adoOpen($adSource)
    EndIf
    If IsObj($oADO) = 0 Then Return SetError(1)
    $oRec = _adoOpenRecordset();ObjCreate("ADODB.Recordset")
    If IsObj($oRec) = 0 Then Return SetError(2)
    
    $oRec.CursorLocation = $adUseClient
    $oRec.Open ("UPDATE " & $adTable & " SET " & $adTable & ".[" & $adcCol & "] = '" & $adData & "' WHERE (((" & $adTable & ".[" & $adCol & "])=" & $adQuery & "))", $oADO,  $adOpenStatic, $adLockOptimistic)
    
    If @Error = 0 Then
    Else
        $oADO.Close()
        Return SetError(3,0,0)
    EndIf
    $oADO.Close()
EndFunc   ;<===> _adoUpdateRecord()

[GEOSoft]

Also, using _adoCreateTable, if you specify a field type of TEXT but do not specify a field length, it creates a MEMO field instead. Not sure if that's your code or if that's Microsoft though.

It should just create a text field of Maximum length (255). Perhaps there is a problem with Access versions. I use 2003 but I'll try it on my wifes system (2000) and see what happens. Other wise just use TEXT(255).

[2tim3_16]

Is there any way to open an ADO connection, perform multiple functions, then close the connection, rather than opening and closinng the connection for every function? I'm using this UDF at work with a backup script I've written, and when checking timestamps on 30,000 files (yeah, some people have a lot of "stuff" on their laptops! ) opening and closing the database for every file seems a bit ridiculous. I'd like to be able to just do something like call _adoOpen($adSource) at the beginning of my script and _adoClose ($adSource) at the end of the script.

[GEOSoft]

Is there any way to open an ADO connection, perform multiple functions, then close the connection, rather than opening and closinng the connection for every function? I'm using this UDF at work with a backup script I've written, and when checking timestamps on 30,000 files (yeah, some people have a lot of "stuff" on their laptops! ) opening and closing the database for every file seems a bit ridiculous. I'd like to be able to just do something like call _adoOpen($adSource) at the beginning of my script and _adoClose ($adSource) at the end of the script.

Sure it is. I'm not home for a while yet but as soon as I get back I'll work on it.

[2tim3_16]

Have you had a chance to look in to this yet?

[GEOSoft]

Have you had a chance to look in to this yet?

I'm not home from vacation yet and I don't have the code or the reference with me.

[2tim3_16]

Based on _adoQueryLike and _adoUpdateRecord, I made the following more customizable functions:

_adoSQLSelect for Select queries

expandcollapse popup

Func _adoSQLSelect($adSource, $adTable, $adCol, $adCriteria, $adFull = 1)
    Local $I, $Rtn
    $oADO = 'ADODB.Connection'
    If IsObj($oADO) Then
        $oADO = ObjGet('',$oADO)
    Else
        $oADO = _adoOpen($adSource)
    EndIf
    If IsObj($oADO) = 0 Then Return SetError(1)
    $oRec = _adoOpenRecordset();ObjCreate("ADODB.Recordset")
    If IsObj($oRec) = 0 Then Return SetError(2)
    
    If $adCriteria = "" Then
        $adSQL = "SELECT " & $adCol & " FROM " & $adTable
    Else
        $adSQL = "SELECT " & $adCol & " FROM " & $adTable & " WHERE " & $adCriteria
    EndIf
    
    $oRec.Open ($adSQL, $oADO, $adOpenStatic, $adLockOptimistic)
    If $oRec.RecordCount < 1 Then
        Return SetError(1)
    Else
        SetError(0)
        $oRec.MoveFirst()
        Do
            If $adFull = 1 Then
                
                For $I = 0 To _adoCountFields($adSource,$adTable)-1
                    $Rtn = $Rtn & $oRec.Fields($I).Value & Chr(28);;<<====== Separate the fields with a non-printable character
                Next
                
            EndIf
            $Rtn = $Rtn & Chr(29);;<<====== Separate the records with a non-printable character
            $oRec.MoveNext()
        Until $oRec.EOF
        
        $oRec.Close()
        $oADO.Close()
        If $adFull = 1 Then Return StringSplit(StringTrimRight($Rtn, 2),Chr(29))
        Return StringSplit(StringTrimRight($Rtn, 1),Chr(29))
    EndIf
EndFunc   ;<===> _adoSQLSelect()

_adoExecuteSQL for Make Table, Append, Update, or Delete queries

Func _adoExecuteSQL($adSource,$adSQL)
    $oADO = 'ADODB.Connection'
    If IsObj($oADO) Then
        $oADO = ObjGet('',$oADO)
    Else
        $oADO = _adoOpen($adSource)
    EndIf
    If IsObj($oADO) = 0 Then Return SetError(1)
    $oRec = _adoOpenRecordset();ObjCreate("ADODB.Recordset")
    If IsObj($oRec) = 0 Then Return SetError(2)
    
    $oRec.CursorLocation = $adUseClient
    $oRec.Open ($adSQL, $oADO,  $adOpenStatic, $adLockOptimistic)
    
    If @Error = 0 Then
    Else
        $oADO.Close()
        Return SetError(3,0,0)
    EndIf
    $oADO.Close()
EndFunc   ;<===> _adoExecuteSQL()

[GEOSoft]

Thanks. I'll add them into the first post now and add them to the download when I get home.

EDIT: Ooooooooooops. I don't have the functions in the first post so I'll add them to the zip when I get home. If I get time later I might just download the zip, make the changes and then FTP it back up.

Edited July 27, 2007 by GEOSoft

[2tim3_16]

I'm not home from vacation yet and I don't have the code or the reference with me.

I figured out how to do it. Cuts the running time of my script to 1/3 it's previous time! I'll try to remember to post my changes on Monday.

EDIT: On second thought, I'll just post it now so I don't forget.

ADO.au3

Edited July 27, 2007 by 2tim3_16

[GEOSoft]

I figured out how to do it. Cuts the running time of my script to 1/3 it's previous time! I'll try to remember to post my changes on Monday.

Thats good news. No hurry because the way this trip is going I'll be lucky to be home a year from Monday

[2tim3_16]

Occasionally, some of my users are getting a fatal error on .AddNew in the _adoAddRecord function. Is there a way to trap this error, rather than having it kill the script?

Func _adoAddRecord($adTable, $rData, $adCol)
    If NOT IsArray($rData) Then
        $rData = StringSplit($rData,'|')
    EndIf

    If IsObj($oADO) = 0 Then Return SetError(1)
    If Not IsObj($oADO) Then Return SetError(2, 0, 0)
    $oRec = _adoOpenRecordset();ObjCreate("ADODB.Recordset")
    If IsObj($oRec) = 0 Then Return SetError(2)
    With $oRec
        .Open ("SELECT * FROM " & $adTable , $oADO, $adOpenStatic, $adLockOptimistic)
        .AddNew
        If IsArray($rData) Then
            For $I = 1 To Ubound($rData) -1;$rData[0]
                $rData[$I] = StringStripWs($rData[$I],1)
                .Fields.Item($I -1) = $rData[$I]
            Next
            
        Else
            .Fields.Item($adCol) = $rData
        EndIf
        .Update
        .Close
    EndWith
EndFunc  ;<===> _adoAddRecord()

EDIT: Evidently, the Users had lost "Write" permissions to the folder where the database was located. I corrected that. Odd that it's only been a problem on one computer, and we have my script on over 100. And that it hasn't always been a problem. Anyway, I'd still be interested in a way to trap such errors in the future (not just on .AddNew, but on .Open, .Update, etc.) if it's possible.

Edited July 30, 2007 by 2tim3_16

[2tim3_16]

I'm not very familiar with using ADO, but I'm wondering if it's possible to also add functions to do imports/exports, such as:

DoCmd.TransferText acImportDelim, "tblTempNewNamesSpec", "tblTempNewNames", FileSpec, True

I have a database that's in Access just because I didn't have any other options. But if I could change it over to using AutoIt, then I wouldn't need to be using another Office license on that computer. The GUI's to replace my forms would be easy enough...

EDIT: :"> Suddenly I feel a bit dumb... why do I need to do imports/exports like that when I can read/write the data directly using AutoIt? Oh well, sometimes I'm a bit slow.

Edited July 31, 2007 by 2tim3_16

[2tim3_16]

Added the following to trap the ADO errors:

Global $oADOError = ObjEvent("AutoIt.Error","_adoCOMError")
Func _adoCOMError() 
    _FileWriteLog($Error_Log, $oADOError.description)
            
    Local $err = $oADOError.number
    If $err = 0 Then $err = -1
    
    SetError($err)
Endfunc