Jump to content
Sign in to follow this  
GEOSoft

MSAccess UDF [updated]

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

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

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


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

Hej GEOSoft ! :D

I've read the posts (6 pages :D )

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

and I cannot wait to implement your features !!!

Thanks in advance ! :o

Share this post


Link to post
Share on other sites

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

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

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

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

-Bryon

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


tbodine

Share this post


Link to post
Share on other sites

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


tbodine

Share this post


Link to post
Share on other sites

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.

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

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

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