asked on

trying to run pass through query in vba code..synyax error

2 tables:
"tbl_Import_history" local access table
dbo_t_dispensing_metrics_detail_schedules -- sql server linked table

running an update pass through query on sql table, for a specific field based on a selection of field names(field in sql table) in a list4 box.

is the following code correct ?

' no we update the sql server table
' DO THE UPDATE ON SQL TABLE ' PASS THROUGH QUERY
strSQL = ""
Dim strS As String
Dim strField As String

strField = [Forms]![frm_Choice].[List4].Value

strS = "Update dbo_t_dispensing_metrics_detail_schedules as Res " & _
"Inner join tbl_Import_Flag as rand " & _
"on Res.Account_Number = rand.Field1 " & _
"set Res." & strField & " = rand.Field2"

'Debug.Print strS
'turn warnings off, rune the query, and turn warnings back on
CurrentDb.Execute strS

MsgBox "Updates Completed", vbDefaultButton1

Open in new window

Still getting syntax errors. ?

Thanks
fordraiders

Gustav Brock

You can't mix an SQL table (with no dbo_ prefix) with a local table in a PT query:

t_dispensing_metrics_detail_schedules as Res Inner join tbl_Import_Flag

/gustav

Fordraiders

ASKER

gustav
Thanks but still do not work.
strS = "Update t_dispensing_metrics_detail_schedules as Res " & _
"Inner join tbl_Import_Flag as rand " & _
"on Res.Account_Number = rand.Field1 " & _
"set Res." & strField & " = rand.Field2"

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

Thanks, This solution worked great. Thanks