Solved: How to run update query on Access table from Excel VBA

[Philip Brown]

Hello,

I want to run the following update query in Excel:

UPDATE WTMData SET WTMData.HALOT = "myvariable", WTMData.BDB = "myvariable", WTMData.AlternateGloss = "myvariable", WTMData.GlosserInitials = "apb", WTMData.GlossDate = Date() & " " & Time(), WTMData.Parsing = "myvariable"
WHERE (((WTMData.HALOT) Is Null) AND ((WTMData.BDB) Is Null) AND ((WTMData.AlternateGloss) Is Null) AND ((WTMData.GlosserInitials) Is Null) AND ((WTMData.GlossDate) Is Null) AND ((WTMData.Parsing)="myvariable") AND ((WTMData.Lemma)="myvariable") AND ((WTMData.WTMOccurrences)<100) AND ((WTMData.WTMParsing) Not Like "myvariable" And (WTMData.WTMParsing) Like "myvariable") AND ((WTMData.[H/A])="myvariable"));

How would I run it from within Excel?

Thanks!

Philip Brown

[matthewspatrick]

One way would be to do it in code:

[VBA] Sub DoItInAccess()

Dim acApp As Object

Set acApp = CreateObject("Access.Application")
With AcApp
.OpenCurrentDatabase "c:\yourfolder\subfolder\file.mdb"
.DoCmd.SetWarnings False
.RunSQL "UPDATE WTMData SET WTMData.HALOT = " & _
"""myvariable"", WTMData.BDB = ""myvariable"", " & _
"WTMData.AlternateGloss = ""myvariable"", " & _
"WTMData.GlosserInitials = ""apb"", " & _
"WTMData.GlossDate = Date() & "" "" & Time(), " & _
"WTMData.Parsing = ""myvariable"" " & _
"WHERE (((WTMData.HALOT) Is Null) AND " & _
"((WTMData.BDB) Is Null) AND " & _
"((WTMData.AlternateGloss) Is Null) AND " & _
"((WTMData.GlosserInitials) Is Null) AND " & _
"((WTMData.GlossDate) Is Null) AND " & _
"((WTMData.Parsing)=""myvariable"") AND " & _
"((WTMData.Lemma)=""myvariable"") AND " & _
"((WTMData.WTMOccurrences)<100) AND " & _
"((WTMData.WTMParsing) Not Like ""myvariable"" " & _
"And (WTMData.WTMParsing) Like ""myvariable"") " & _
"AND ((WTMData.[H/A])=""myvariable""));"
.DoCmd.SetWarnings True
.CurrentDB.Close
.Quit
End With
Set acApp = Nothing

End Sub
[/VBA]

[Philip Brown]

It looks to me like .RunSQL is not an Excel VBA method. It is an Access VBA method. When I try to run the code in Excel, I get a 438 error indicating that the .RunSQL method is not supported.

What is the equivalent to .RunSQL in Excel?

Thanks.
Philip

[matthewspatrick]

It looks to me like .RunSQL is not an Excel VBA method. It is an Access VBA method. When I try to run the code in Excel, I get a 438 error indicating that the .RunSQL method is not supported.

What is the equivalent to .RunSQL in Excel?

Thanks.
Philip

Doh!

Should have been:





[VBA]

Sub DoItInAccess()

Dim acApp As Object

Set acApp = CreateObject("Access.Application")

With AcApp

.OpenCurrentDatabase "c:\yourfolder\subfolder\file.mdb"

.DoCmd.SetWarnings False

.DoCmd.RunSQL "UPDATE WTMData SET WTMData.HALOT = " & _

"""myvariable"", WTMData.BDB = ""myvariable"", " & _

"WTMData.AlternateGloss = ""myvariable"", " & _

"WTMData.GlosserInitials = ""apb"", " & _

"WTMData.GlossDate = Date() & "" "" & Time(), " & _

"WTMData.Parsing = ""myvariable"" " & _

"WHERE (((WTMData.HALOT) Is Null) AND " & _

"((WTMData.BDB) Is Null) AND " & _

"((WTMData.AlternateGloss) Is Null) AND " & _

"((WTMData.GlosserInitials) Is Null) AND " & _

"((WTMData.GlossDate) Is Null) AND " & _

"((WTMData.Parsing)=""myvariable"") AND " & _

"((WTMData.Lemma)=""myvariable"") AND " & _

"((WTMData.WTMOccurrences)<100) AND " & _

"((WTMData.WTMParsing) Not Like ""myvariable"" " & _

"And (WTMData.WTMParsing) Like ""myvariable"") " & _

"AND ((WTMData.[H/A])=""myvariable""));"

.DoCmd.SetWarnings True

.CurrentDB.Close

.Quit

End With

Set acApp = Nothing

End Sub

[/VBA]

[Philip Brown]

Thanks, Patrick.

Before I got your last post, I got this to work:

sqlString = _
"UPDATE WTMData SET WTMData.HALOT = " & sHALOTDef & ", " & _
"WTMData.BDB = " & sBDBDef & ", " & _
"WTMData.AlternateGloss = " & sAltDef & ", " & _
"WTMData.GlosserInitials = ""apb"", " & _
"WTMData.GlossDate = Date() & "" "" & Time() " & _
"WHERE (((WTMData.HALOT) Is Null) AND " & _
"((WTMData.BDB) Is Null) AND " & _
"((WTMData.AlternateGloss) Is Null) AND " & _
"((WTMData.GlosserInitials) Is Null) AND " & _
"((WTMData.GlossDate) Is Null) AND ((WTMData.Parsing) Is Null) AND " & _
"((WTMData.Lemma)=" & sLemma & ") AND " & _ "((WTMData.WTMOccurrences)<100) AND " & _
"((WTMData.WTMParsing) Not Like ""np*"") AND " "((WTMData.[H/A])=""H""));"

Set dbs = OpenDatabase("C:\mydatabase.mdb")
With dbs
.Execute sqlString, dbFailOnError
.Close
End With
Set dbs = Nothing

Any reason I shouldn't be doing it this way?

[matthewspatrick]

No, jut an instance of there being more than one way of doing it

[Philip Brown]

Thanks for taking the time to help!
Merry Christmas to you!

[matthewspatrick]

You're welcome, and to you as well.