• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Executing Query (On Access Table) From Visual Basic using ADO Link

Hi,

I've connected to an Access Database using ADO as follows:

Public Const varConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Database.mdb;Persist Security Info=False"
Dim Db As New ADODB.Connection
Db.Open varConnectString

Now, I want to do an update on a table.  However, the update query has a function that I have created:

Db.Execute "Update [Temp] Set [Field1] = LeftZeros([Field1],5)"

This works fine in Access.  When I try it in Visual Basic, it tells me "Undefined Function 'Leftzeros'".  But the function is there.  If I test the function from the Debug window, it works fine.  How can I fix this?

Thanks,

ssteeves
0
ssteeves
Asked:
ssteeves
  • 9
  • 9
  • 3
  • +3
1 Solution
 
Arthur_WoodCommented:
the function LeftZeros DOES NOT exist in Access, but appears to be a function defined in your VB program - correct?

If that is the case, then change like this:

Db.Execute "Update [Temp] Set [Field1] = " &  LeftZeros([Field1],5)

AW
0
 
Arthur_WoodCommented:
if the function ONLY exists in your Access MDB, then you are out of luck, as you cannot refer to a USER defined function in executing an ADO command, form outside.

AW

0
 
DabasCommented:
I have the feeling that the opposite is true.

The LeftZeros function is in Access as part of a module, which cannot be seen from VB.

Would it be too hard for you to copy the whole function and paste it into your VB code window?

Dabas
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ssteevesAuthor Commented:
Yes, this is a function that I have written in my VB program.  However, the [Field1] is a field in the table in Access.  If I do it like you mentioned, (ie, Field1 outside of the quotes), it doesn't know what [Field1] is. I get a message "External Name Not Defined", and [Field1] is highlighted...
0
 
ssteevesAuthor Commented:
The function is declared like this:

Public Function LeftZeros(Value As String, Length As Integer)

I've tried placing this function in a module, and right in the form.  Neither seems to work....
0
 
DabasCommented:
You have a problem here:

Fields1 is understood by ADO as it is part of your query.

The moment you pass it on to a function within your program, the program does not know what the value of Fields1 is!

Would you mind pasting the contents of that function, then I might be able to come up with a solution for you

Dabas
0
 
ssteevesAuthor Commented:
Sure,  the function just adds leading zeros to the field...

Private Function LeftZeros(Value As String, Length As Integer)
Do While Len(Value) < Length
  Value = "0" & Value
Loop
LeftZeros = Value
End Function

Any ideas?  Could I remotly create a query in the access database, and then execute it, all from VB?
0
 
DabasCommented:
Try:

Db.Execute "Update [Temp] Set [Field1] = " &  Format([Field1],"00000")
0
 
ssteevesAuthor Commented:
No, that doesn't even work from Access...

As I mentioned previously though, Can I create a Querydef in Access via Visual Basic, and then tell Access to run it?
0
 
DabasCommented:
I just tested this one in Access:

UPDATE Temp SET Temp.Field1 = Format([Field1],"00000");
0
 
ssteevesAuthor Commented:
It worked for the first record, but not for every record in the table...
0
 
ssteevesAuthor Commented:
Sorry, let me re-clarify that.  It only works on numeric values in the table.  I have alphanumeric data....
0
 
DabasCommented:
AAAAhhhhh

How big is the table?

If it is not tens of thousands of records you could create a recordset and traverse through the records adjusting them one by one.

It should only be 5 lines of code and should not take long to traverse through your table.

If you need help with this, ask again

Dabas
0
 
ssteevesAuthor Commented:
That I can do, but it seems wrong.  I really don't want to do it that way.  There MUST be a way to do what I need through the power of SQL...I just need to find it...
0
 
DabasCommented:
You can try a stored procedure.
Will definitely work in SQL Server.

I do not know about Access
0
 
DiveblueCommented:

<<As I mentioned previously though, Can I create a Querydef in Access via Visual Basic, and then tell Access to run it? >>

Yes, but you'll be using DAO for that part:

Dim db as Database
Dim Qd as QueryDef
set db = DBEngine.Workspaces(0).OpenDatabase("C:\mydb.mdb",, True)

Set Qd = db.CreateQueryDef("","Update [Temp] Set [Field1] =  LeftZeros([Field1],5)"

Qd.Execute dbFailOnError

0
 
DabasCommented:
I also thought about DAO and QueryDef, but it will not help, as LeftZeros is a function in the VB code, and the Query will not know how to execute when it gets to execute.

Dabas
0
 
DiveblueCommented:
I was under the impression that LeftZeros was a function in the access db. if that's not the case...


Dim db as Database
Dim Qd as QueryDef
set db = DBEngine.Workspaces(0).OpenDatabase("C:\mydb.mdb",, True)

Set Qd = db.CreateQueryDef("","Update [Temp] Set [Field1] =  Format$([Field1]," & chr(34) & "00000" & chr(34) & ")"

Qd.Execute dbFailOnError
0
 
DiveblueCommented:
assuming leftzeros adds leading zeroes up to 5 positions
0
 
QJohnsonCommented:
Does this work:

Update [Temp] Set [Field1] = String(5 - Len(Trim[Field1]),"0") & Trim([Field1])

I think this has a fairly high chance of success in Access as the String() function should be recognized.

The only other alternative is a bit inelegant but would sure work - run the following query five times.

Update [Temp] Set [Field1] = "0" & [Field1] Where Len([Field1]) < 5

after five executions, all the Field1 contents will be of length 5 even if they were empty strings at the start.
0
 
DabasCommented:
DiveBlue:

You should read all of the previous answers, as I suggested the format option too.
ssteeves mentioned it did not work, as Field1 can be alphanumerical.

Dabas
0
 
supunrCommented:
OK, how about this?

Db.Execute "Update [Temp] Set [Field1] = Right(""00000" & [Field1],5)"

Good Luck!
0
 
supunrCommented:
OK, how about this?

Db.Execute "Update [Temp] Set [Field1] = Right(""00000"" & [Field1],5)"

Good Luck!
0
 
QJohnsonCommented:
Dabas,

   I did read those replies.  The fact that the field can have both numbers and characters in it means it has to be text data.  That's why your Format() solution can't work.  And it's why my concatenation solu;tion (and that of others recommended here, too) certainly can.

   He's got so many good solutions here now that he will probably have a hard time picking, don't you agree?
0
 
DabasCommented:
QJohnson:

Apologies:
I wrote my remark to DiveBlue's format solution, not to you.
(Yours had not registered on my computer yet)

I believe supunr's last solution is the best and simplest.
If it works, he should be allocated the full 400 A grade points!
0
 
QJohnsonCommented:
Yep, he gets my vote, too.

Sorry I mis-interpretted your remarks as aimed at me.  Hope it was no problem to you.  It was a long enough day already, eh?

0
 
ssteevesAuthor Commented:
Okay, I do have some excellent recomendations here.  I'll look them over today and see which I decide on...

Thanks,

ssteeves
0
 
ssteevesAuthor Commented:
Yup, this seems to work perfectly.  Thanks!

ssteeves
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 9
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now