Reference Access Function From VB

[JimHorton]

I'm using the .execute method from VB, to run an Update query in an Access .mdb. This query contains a (public) function as one of the 'update to' fields. VB bombs with 'Undefined function 'xxx'. How do I reference this function? Thanks,<br>
--Jim

 

[DougP]

What does the function do.<br>
I suppose you know that you could probably recreate that function in VB and leave Access out of the equation except for the (Tables) portion of Access.<br>
Also you can copy and paste the Function code from Access into VB and it will run as is. So you don't have to re-write the whole thing in VB.<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimHorton]

I've tried to paste it to VB, but to no avail. The function references an Access Table field. Here is a simplified version...<br>
<br>
Query SQL: (Runs perfect in Access):<br>
Update tblTest Set Field1 = DoStuff([Field1])<br>
<br>
Module (In Access mdb)<br>
Public Function DoStuff( strIndata as string) as String<br>
' (This is, of course simplified, the real procedure CAN'T be performed Inline in the query,must be in function) <br>
DoStuff = strInData & &quot;Some stuff I did in this function&quot;<br>
Exit Function<br>
<br>
The mdb is, at times, used by the users via the Access Interface, so the function needs to be in available in Access as well. <br>
Thanks for any insight...<br>
--Jim

 

[DougP]

What do you mean an Acess table field.<br>
That's not a problem for VB<br>
Do you mean a textbox on a Access form, now that would be a problem.<br>
Give me the exact code that causes the problem and I'll make it work.<br>
or e-mail it to me and I send it back fixed... <p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimHorton]

Doug,<br>
Thanks, I've sent a demo,<br>
--Jim

 

[JimHorton]

To whomever might also have been wanting an answer to this situation<br>
I finally found it on my own after some tinkering, here it is:<br>
<br>
OLD WAY: ( .execute failed if query used a function within the Access Db)<br>
Dim db As Database, qd As QueryDef, pth As String<br>
pth = app.path<br>
Set db = DBEngine(0).OpenDatabase(&quot;pth & &quot;\testdb.mdb&quot<br>
Set qd = db.QueryDefs(&quot;qryTest&quot 'Update query uses an Access function<br>
qd.Execute ' &quot;Undefined function xxxx&quot; was the error<br>
<br>
NEW WAY (works well)<br>
Dim db As Database, qd As QueryDef, pth As String, ac As Application<br>
pth = App.Path<br>
Set ac = New access.Application<br>
ac.OpenCurrentDatabase pth & &quot;\testdb.mdb&quot;, False<br>
ac.Visible = False<br>
Set qd = ac.DBEngine(0)(0).QueryDefs(&quot;qrytest&quot<br>
qd.Execute<br>
MsgBox &quot;You've updated &quot; & qd.RecordsAffected & &quot; records&quot;<br>
ac.CloseCurrentDatabase<br>
Set ac = Nothing<br>
<br>