MSAccess UDF [updated]

[Jafian]

Hey GEOSoft, I have been using your functions a lot here, they have been really helpful but I have run into a few questions regarding your code and was wondering if you had any advice or suggestions for me.

I am using autoit to populate an employee database that I am running and am hoping to expand my program to be able to read and modify the database instead of just adding information to it. Now looking through the functions you provided the ways to pull information from the database are _accessGetVal, _accessQueryLike, and _accessQueryStr.

EmployeeID LastName FirstName

10000 Doe John

10001 Doe Jane

10002 User Test

_accessGetVal wants a field name as a search term, but if I use that I will only get values from the first record in the table (ID#10000 in this case.)

_accessQueryLike returns a chr(28) delimited array of the terms which will return the information I need but is a little tedious to split the string and pull the one value I need from it.

_accessQueryStr just doesn't make much sense to me. It asks for the column name and the search term, so if I give it a ColumnName of LastName, and a search term of "Doe", it just returns "Doe" right back to me. I guess it just seems a little redundant to have it return a value that you are putting into the function anyways or is there something I am missing?

Ideally I suppose, a function would be nice that would look like _accessReturnValue($adSource,$adTable,$searchField,$searchValue,$returnColumn) basically so someone can search LastName for "User" and have it return the Employee ID for example.

So _accessQueryLike seems to work for me right now, but in my example above, if I do not know the employee ID number is there anyway to return both people with the Doe last name? I have a LOT of people unfortunately with common last names and if there are duplicates of the search term it would be nice to be able to return all the values so I can further decide which record to use.

I really appreciate your input and all of the work you have put into making these functions accessable.

[GEOSoft]

Hey GEOSoft, I have been using your functions a lot here, they have been really helpful but I have run into a few questions regarding your code and was wondering if you had any advice or suggestions for me.

I am using autoit to populate an employee database that I am running and am hoping to expand my program to be able to read and modify the database instead of just adding information to it. Now looking through the functions you provided the ways to pull information from the database are _accessGetVal, _accessQueryLike, and _accessQueryStr.

EmployeeID LastName FirstName

10000 Doe John

10001 Doe Jane

10002 User Test

_accessGetVal wants a field name as a search term, but if I use that I will only get values from the first record in the table (ID#10000 in this case.)

_accessQueryLike returns a chr(28) delimited array of the terms which will return the information I need but is a little tedious to split the string and pull the one value I need from it.

_accessQueryStr just doesn't make much sense to me. It asks for the column name and the search term, so if I give it a ColumnName of LastName, and a search term of "Doe", it just returns "Doe" right back to me. I guess it just seems a little redundant to have it return a value that you are putting into the function anyways or is there something I am missing?

Ideally I suppose, a function would be nice that would look like _accessReturnValue($adSource,$adTable,$searchField,$searchValue,$returnColumn) basically so someone can search LastName for "User" and have it return the Employee ID for example.

So _accessQueryLike seems to work for me right now, but in my example above, if I do not know the employee ID number is there anyway to return both people with the Doe last name? I have a LOT of people unfortunately with common last names and if there are duplicates of the search term it would be nice to be able to return all the values so I can further decide which record to use.

I really appreciate your input and all of the work you have put into making these functions accessable.

I'll take a look at it, however it will be a few days from now.

[GEOSoft]

The AccessConstants.au3 file has been updated to allow the functions to work with Access 2007. As far as I can determine it has had no effect on functionality in the udf

[Private]

Hej GEOSoft !

I've read the posts (6 pages )

I'm more than happy with your initative to create something like this !

and I cannot wait to implement your features !!!

Thanks in advance !

[GEOSoft]

You're most welcome. Someday I may find time to get back to working at adding functions but for now all I wanted to do was get it working with Office 2007 which meant setting $adoProvider based on the version in use on the system.

Edited March 30, 2009 by GEOSoft

[PhejEJ]

sorry, i am a noob. can someone tell me how to use the _accessquerylike? i try to use it but i keep getting this error.

C:\Documents and Settings\Testing\Desktop\acess\Access.au3 (520) : ==> The requested action with this object has failed.:

$oRec.Open ("SELECT * FROM "& $adTable & " WHERE " & $adCol & " Like '%" & $Find & "%'", $oADO, $adOpenStatic, $adLockOptimistic)

$oRec.Open ("SELECT * FROM "& $adTable & " WHERE " & $adCol & " Like '%" & $Find & "%'", $oADO, $adOpenStatic, $adLockOptimistic)^ ERROR

thansk in advance.

[Jos]

This means that $oRec is not an object. Did you check for errors opening the Database?

Jos

[GEOSoft]

Post or PM me your test code and I'll take a look at it.

Oooooops. Almost forgot. What version of MSAccess are you using?

Edited April 13, 2009 by GEOSoft

[PhejEJ]

i am using 2003 version of access.

here is the code i have. sorry if this code is too newbie as i mention i am a newbie.

#Include <Access.au3>

dim $yo

$adSource = "somepath.mdb"

$adTable = "Transactions"

$adCol = "Serial"

$Find = "username"

$yo= _accessQueryLike($adSource,$adTable, $adCol,$Find, $adFull = 1)

MsgBox(0,"",$yo)

sorry for the trouble i might cost everyone that i am a newbie. if someone could give me an example i think it'll help alot.

thanks in advance.

[GEOSoft]

i am using 2003 version of access.

here is the code i have. sorry if this code is too newbie as i mention i am a newbie.

#Include <Access.au3>

dim $yo

$adSource = "somepath.mdb"

$adTable = "Transactions"

$adCol = "Serial"

$Find = "username"

$yo= _accessQueryLike($adSource,$adTable, $adCol,$Find, $adFull = 1)

MsgBox(0,"",$yo)

sorry for the trouble i might cost everyone that i am a newbie. if someone could give me an example i think it'll help alot.

thanks in advance.

There appears to be a bug in there after the last update. I'll look at it and post when it's fixed.

[BryonR]

What can I use to display a record? and have each of those fields stored in a diffrent var?

-Bryon

[sigil]

Never mind, I found it in the zip file.

Edited May 11, 2009 by sigil

[GregThompson]

So I need to use this UDF and specifically the AddRecord function. Maybe I'm crazy, but isn't this logic wrong? Here the author checks if the data is an array, if it isn't he makes it an array. Then further down, he checks the same data as to whether or not it's an array... ?

Func _accessAddRecord($adSource, $adTable, $rData,$adCol = 0)
   If NOT IsArray($rData) Then
      $rData = StringSplit($rData,'|')
  EndIf
   $oADO = 'ADODB.Connection'
   If IsObj($oADO) Then
      $oADO = ObjGet('',$oADO)
   Else
      $oADO = _dbOpen($adSource)
   EndIf
   If IsObj($oADO) = 0 Then Return SetError(1)
   If Not IsObj($oADO) Then Return SetError(2, 0, 0)
   $oRec = _dbOpenRecordset();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)
            MsgBox(0,$I,$rData[$I])
            .Fields.Item($I -1) = $rData[$I]
         Next
      Else
         .Fields.Item($adCol) = $rData
      EndIf
      .Update
      .Close
   EndWith
   $oADO.Close()
EndFunc    ;<===> _accessAddRecord()

[tbodine88]

Hello

The Access UDF is great.

Here is an addition. I've tried it and it seems to work :

<code>

;===============================================================================

; Function Name: _accessInsert()

; Description: Insert into a table

; Syntax: _accessInsert($adSource, $adTable, $adData)

; Parameter(s): $adSource - The full path/filename of the database to be opened

; $adTable - the name of the table to insert the data

; $rData - Data to be added to field (to add data to multiple fields this must be an array) see notes

; Requirements:

; Return Value(s): Success - @Error = 0 and record is added to table

; Failure - Sets @Error

; 1 = unable to create connection

; 2 = aData isn't an Array

; 3 = aTable isn't a string

; Author(s): Thomas Bodine

; Notes: $aData is one dimensional, and must have the same number of fields as the table has columns

; Modifications:

;===============================================================================

Func _accessInsert($adSource, $adTable, ByRef $adData)

If IsArray($adData) = 0 Then Return SetError(2,0,0)

If IsString($adTable) = 0 then Return SetError(3,0,0)

$oADO = 'ADODB.Connection'

If IsObj($oADO) Then

$oADO = ObjGet('', $oADO)

Else

$oADO = _dbOpen($adSource)

EndIf

If IsObj($oADO) = 0 Then Return SetError(1,0,0)

$sql = "INSERT INTO " & $adTable & " VALUES ("

For $i = 0 To UBound($adData) - 1

Select

Case IsNumber($adData[$i])

$sql = $sql & $adData[$i] & ","

Case IsString($adData[$i])

$sql = $sql & "'" & $adData[$i] & "',"

Case Else

MsgBox(48, "program Error", "unrecognized Data Type: " & $adData[$i], 60)

Exit

EndSelect

Next

$len = StringLen($sql)

$sql = StringLeft($sql, $len - 1) & ")"

$oADO.Execute($sql)

Return 1

EndFunc ;==>_accessInsert

<code/>

I need to add handlers for other data types.

Regards Tom Bodine

PS: I forgot how to quote in the forums :-(

[tbodine88]

I see that my code does the same as the add record code the original author put in.

Mine just uses SQL to do the same thing that GEOsoft used AODB update able record sets to do.

So never mind :-)

[GEOSoft]

PS: I forgot how to quote in the forums :-(

What you wanted wasn't a quote anyway. It's a codebox and all you have to do is select your code and then press the editor toolbar icon that looks like <>, second from the right. Quote is just to the left of that.

[DeltaRocked]

Hi Geo,

please find enclosed the code for modifying password protect mdb files. someone might find it useful .

Func _dbOpen($adSource, $adPwd)

$oADO = ObjCreate("ADODB.Connection")

$oADO.Provider = $adoProvider

If $adPwd <> '' Then $oADO.Properties("Jet OLEDB:Database Password") = $adPwd

$oADO.Open($adSource)

Return $oADO

EndFunc ;==>_dbOpen

and modify the functions to facilitate accessing mdb file which has a password.

I have done the required modifications

eg.

Func _accessUpdateRecord($adSource, $adPwd, $adTable, $adCol, $adQuery, $adcCol, $adData)

$adQuery = Chr(39) & $adQuery & Chr(39)

$oADO = 'ADODB.Connection'

If IsObj($oADO) Then

$oADO = ObjGet('', $oADO)

Else

$oADO = _dbOpen($adSource, $adPwd)

EndIf

....

endfunc

This would be handy for those who want to tweak around with password protected MDB files.

eg. sample code

#include <access.au3>

$val=_accessListTables(@ScriptDir&'\test.mdb', 'pwd')

MsgBox(0,'',$val)

regards

Slashh

[Gorby7]

GEOSoft, I saw where others in this thread had posted the same error I'm now getting:

C:\Program Files\AutoIt3\Include\Access.au3 (72) : ==> The requested action with this object has failed.:
.Fields.Item($I -1) = $rData[$I]
.Fields.Item($I -1) = $rData[$I]^ ERROR
>Exit code: 1    Time: 4.377

I didn't see where you had found out what the deal was with this error, any chance that's been figured out since then?

[adik2dmax666]

Will this UDF work if i don't have installed MS access on my pc?

[jazzyjeff]

I too am wondering if the UDF will work without access, or if it can be modified to work without Access installed? My thoughts are that it won't.

Also, does this support Access 2000?