Jump to content
Sign in to follow this  
GEOSoft

MSAccess UDF [updated]

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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 by GEOSoft

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

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. :whistle:

Edited by 2tim3_16

Share this post


Link to post
Share on other sites

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:

#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

Share this post


Link to post
Share on other sites

$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


Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

$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

Share this post


Link to post
Share on other sites

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()

Share this post


Link to post
Share on other sites

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. :whistle:

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

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

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! :whistle: ) 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.

Share this post


Link to post
Share on other sites

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! :whistle: ) 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.

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

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.

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

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

_adoSQLSelect for Select queries

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()

Share this post


Link to post
Share on other sites

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 by GEOSoft

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'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 by 2tim3_16

Share this post


Link to post
Share on other sites

I figured out how to do it. Cuts the running time of my script to 1/3 it's previous time! :P 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 :)

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

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 by 2tim3_16

Share this post


Link to post
Share on other sites

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 by 2tim3_16

Share this post


Link to post
Share on other sites

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

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...