?
Solved

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

Posted on 2003-03-13
28
Medium Priority
?
230 Views
Last Modified: 2013-11-25
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
Comment
Question by:ssteeves
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 3
  • +3
28 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8130547
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8130569
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
 
LVL 27

Expert Comment

by:Dabas
ID: 8130587
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:ssteeves
ID: 8130607
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
 
LVL 1

Author Comment

by:ssteeves
ID: 8130623
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
 
LVL 27

Expert Comment

by:Dabas
ID: 8130667
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
 
LVL 1

Author Comment

by:ssteeves
ID: 8130704
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
 
LVL 27

Expert Comment

by:Dabas
ID: 8130730
Try:

Db.Execute "Update [Temp] Set [Field1] = " &  Format([Field1],"00000")
0
 
LVL 1

Author Comment

by:ssteeves
ID: 8130823
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
 
LVL 27

Expert Comment

by:Dabas
ID: 8130949
I just tested this one in Access:

UPDATE Temp SET Temp.Field1 = Format([Field1],"00000");
0
 
LVL 1

Author Comment

by:ssteeves
ID: 8131000
It worked for the first record, but not for every record in the table...
0
 
LVL 1

Author Comment

by:ssteeves
ID: 8131013
Sorry, let me re-clarify that.  It only works on numeric values in the table.  I have alphanumeric data....
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8131051
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
 
LVL 1

Author Comment

by:ssteeves
ID: 8131094
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
 
LVL 27

Expert Comment

by:Dabas
ID: 8131217
You can try a stored procedure.
Will definitely work in SQL Server.

I do not know about Access
0
 
LVL 3

Expert Comment

by:Diveblue
ID: 8131684

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

Expert Comment

by:Dabas
ID: 8132137
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
 
LVL 3

Expert Comment

by:Diveblue
ID: 8132201
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
 
LVL 3

Expert Comment

by:Diveblue
ID: 8132213
assuming leftzeros adds leading zeroes up to 5 positions
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8132273
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
 
LVL 27

Expert Comment

by:Dabas
ID: 8132560
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
 
LVL 11

Accepted Solution

by:
supunr earned 400 total points
ID: 8132943
OK, how about this?

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

Good Luck!
0
 
LVL 11

Expert Comment

by:supunr
ID: 8132947
OK, how about this?

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

Good Luck!
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8133534
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
 
LVL 27

Expert Comment

by:Dabas
ID: 8133556
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
 
LVL 3

Expert Comment

by:QJohnson
ID: 8134321
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
 
LVL 1

Author Comment

by:ssteeves
ID: 8151395
Okay, I do have some excellent recomendations here.  I'll look them over today and see which I decide on...

Thanks,

ssteeves
0
 
LVL 1

Author Comment

by:ssteeves
ID: 8154340
Yup, this seems to work perfectly.  Thanks!

ssteeves
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question