Running an Update Query from Excel

[hatstand]

I’ve done a whole load of searching and come to a dead end regarding running an Update Query from Excel.

I have 35 fields in my Update Query, with each one having an ‘IIf’ formula. My Excel sheet if populated using SQl linked to Access. I then add figures required and run the Update query.

I would ideally like run some VBA that runs the Update Query. But it seams so complicated. Or am I just reading the wrong stuff.

 

[Trevor G]

This code will open a database and form, as it is using the DoCmd Command line you can substitute this for the OpenQuery and name the query which should run your update query. You will have to set the Reference to Access in the Excel VBA screen. Use Alt + F11 then Select Tools Menu > References > scroll down to Microsoft Access XX Object Library and click the box.

Sub DisplayForm()
Dim appAccess As Access.Application
Dim strDB As String

' Initialize string to database path.
Const strConPathToSamples = "M:\Access Files\"

strDB = strConPathToSamples & "Test ME Today.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB
' Open Orders form.
appAccess.DoCmd.OpenForm "frmEmployee1" 'Substitue this line to open the query or run command
End Sub

 

[hatstand]

That's brilliant. Very much appreciated.