ssteeves
asked on
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.OL
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
I've connected to an Access Database using ADO as follows:
Public Const varConnectString As String = "Provider=Microsoft.Jet.OL
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
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
AW
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
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
ASKER
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...
ASKER
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....
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....
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
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
ASKER
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?
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?
Try:
Db.Execute "Update [Temp] Set [Field1] = " & Format([Field1],"00000")
Db.Execute "Update [Temp] Set [Field1] = " & Format([Field1],"00000")
ASKER
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?
As I mentioned previously though, Can I create a Querydef in Access via Visual Basic, and then tell Access to run it?
I just tested this one in Access:
UPDATE Temp SET Temp.Field1 = Format([Field1],"00000");
UPDATE Temp SET Temp.Field1 = Format([Field1],"00000");
ASKER
It worked for the first record, but not for every record in the table...
ASKER
Sorry, let me re-clarify that. It only works on numeric values in the table. I have alphanumeric data....
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
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
ASKER
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...
You can try a stored procedure.
Will definitely work in SQL Server.
I do not know about Access
Will definitely work in SQL Server.
I do not know about Access
<<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).Ope
Set Qd = db.CreateQueryDef("","Upda
Qd.Execute dbFailOnError
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
Dabas
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).Ope
Set Qd = db.CreateQueryDef("","Upda
Qd.Execute dbFailOnError
Dim db as Database
Dim Qd as QueryDef
set db = DBEngine.Workspaces(0).Ope
Set Qd = db.CreateQueryDef("","Upda
Qd.Execute dbFailOnError
assuming leftzeros adds leading zeroes up to 5 positions
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK, how about this?
Db.Execute "Update [Temp] Set [Field1] = Right(""00000"" & [Field1],5)"
Good Luck!
Db.Execute "Update [Temp] Set [Field1] = Right(""00000"" & [Field1],5)"
Good Luck!
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?
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?
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!
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!
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?
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?
ASKER
Okay, I do have some excellent recomendations here. I'll look them over today and see which I decide on...
Thanks,
ssteeves
Thanks,
ssteeves
ASKER
Yup, this seems to work perfectly. Thanks!
ssteeves
ssteeves
If that is the case, then change like this:
Db.Execute "Update [Temp] Set [Field1] = " & LeftZeros([Field1],5)
AW