QBE SQL to VBA Module 1

[DevilsSlide]

I know enough Access VBA Programming to be dangerous, but not enough to make a living at it! - So none of you have to worry about me taking a living out of your mouths. I'm somewhat better at Excel VBA, than Access VBA (but not by much)

What I'm trying to do is Update a field that has been converted from another database (Paradox) that did not have any form of enforced integrity on a Phone Number field (I have other fields, but this help will get me going in the right direction). Truth is for the last two years there has been no incentive to improve the manual process I've been using, now there is. I have no doubt that there would be a better way to do most of what I want to do, but for now the more lines I use the more I look like the Hero.

What I'm trying to do is create a module(s) that will run about a total of 157 Update Queries. Once I get the first one I can build additional 156 to do full the job. I've build QBE Queries to handle what I've seen as the most common formats. I'm using a front end that links to the back-end converted MDB. So I don't think that I need to use the Path Statement to the DB where it is already a linked to the back-end table, as it may very from user to user.

Below is the QBE SQL statement that I need and the Module that I've started, but I get a Runtime 424 error when I try to run it. CAN SOMEONE HELP PLEASE!!!

' QBE SQL Statement
UPDATE Phone SET Phone.tPhoneID = "XX", Phone.[Area/CountryCode] = Mid([Phone]![Number],3,3), Phone.[Number] = Right([Phone]![Number],8), Phone.ImportantInfo = ""
WHERE (((Phone.tPhoneID) Is Null) AND ((Phone.Number) Like "Z ### ###-####&quot AND ((Phone.ImportantInfo) Like "Auto-Converted *&quot);

' My Module
Sub Test300()

' Dim dbs As database
' Dim qdf As QueryDef
' Set dbs = OpenDatabase("E:\Phones to Test\PhoneTest.mdb&quot

DoCmd.SetWarnings False

dbs.Execute "UPDATE Phone SET Phone.tPhoneID = 'XX', Phone.[Area/CountryCode] = Mid([Phone]![Number],3,3), Phone.[Number] = Right([Phone]![Number],8), Phone.ImportantInfo = """ _
& "WHERE (((Phone.tPhoneID) Is Null) AND ((Phone.Number) Like 'Z ### ###-####') AND ((Phone.ImportantInfo) Like 'Auto-Converted *'));"

End Sub

 

[chrissie1]

i think docmd.runquery will work better


so

private sub test300()
docmd.setwarnings false
docmd.runquery "UPDATE Phone SET Phone.tPhoneID = 'XX', Phone.[Area/CountryCode] = Mid([Phone]![Number],3,3), Phone.[Number] = Right([Phone]![Number],8), Phone.ImportantInfo = '' WHERE (((Phone.tPhoneID) Is Null) AND ((Phone.Number) Like 'Z ### ###-####') AND ((Phone.ImportantInfo) Like 'Auto-Converted *'))"

End Sub
"What a wonderfull world" - Louis armstrong

 

[DevilsSlide]

Chrissie1 ,

thank you - Thank You - THANK YOU! You are my Hero!

That was exactly the nudge (OK - it was a give me question) that I needed to get me in the right direction. I've only created the one Module Command so far but it worked perfectly. I should now be able to Copy, Paste and Modify the other 150+ commands that I need.

The only thing that I needed to change was from "docmd.runquery" to "DoCmd.RunSQL"

Again Thank You for your Help
John