VBA Runtime Error 3075 when executing SQL update

[G1ZmO]

Looking for some help here please.

Using msgbox it looks like my SQL statement is correct here but it's throwing up the 3075 error (see attachments)

I've tried the SQL code as a 1 line like:

    strSQL1 = "UPDATE Item SET JobID = '" & Me.cboTargetJob.Value & "' " & "FROM Item INNER JOIN Job ON Item.JobID = Job.ID INNER JOIN UsedIT ON Item.ID = UsedIT.ItemID INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID INNER JOIN ItemCategory ON ItemType.ItemCategoryID = ItemCategory.ID WHERE Item.JobID = " & Me.cboSourceJob.Value & " AND ItemCategory.ID = " & Me.cboCategory.Value
    MsgBox strSQL1
    CurrentDb.Execute strSQL1, dbSeeChanges

and also as multiple lines like:

    strSQL1 = "'UPDATE Item set Item.JobID = '"
    strSQL2 = Me.cboTargetJob.Value & "' "
    strSQL3 = "FROM Item INNER JOIN Job ON Item.JobID = Job.ID INNER JOIN "
    strSQL4 = "UsedIT ON Item.ID = UsedIT.ItemID INNER JOIN "
    strSQL5 = "MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID INNER JOIN "
    strSQL6 = "ItemType ON MakeAndModel.ItemTypeId = ItemType.ID INNER JOIN   ItemCategory ON ItemType.ItemCategoryID = ItemCategory.ID "
    strSQL7 = "WHERE Item.JobID = " & Me.cboSourceJob.Value & " AND ItemCategory.ID = " & Me.cboCategory.Value & "'"
    
    strSQLfinal = strSQL1 + strSQL2 + strSQL3 + strSQL4 + strSQL5 + strSQL6 + strSQL7

 

[Minty]

In one part of your string you are referring to JobID as a string '8861' and then later as a numeric value 9507 .
It can't be both - so one or other is incorrect.

 

[static]

I would think it should be more like

s=s & "UPDATE Item  "
s=s & "INNER JOIN Job ON Item.JobID = Job.ID INNER JOIN "
s=s & "UsedIT ON Item.ID = UsedIT.ItemID INNER JOIN "
s=s & "MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID INNER JOIN "
s=s & "ItemType ON MakeAndModel.ItemTypeId = ItemType.ID INNER JOIN   ItemCategory ON ItemType.ItemCategoryID = ItemCategory.ID "
s=s & "set Item.JobID = " & Me.cboTargetJob.Value
s=s & " WHERE Item.JobID = " & Me.cboSourceJob.Value & " AND ItemCategory.ID = " & Me.cboCategory.Value & "'"

strSQLfinal = s

type stop after strSQLfinal
run the code
type ?strSQLfinal into the immediate window and hit return

copy the sql into the query builder
fix errors
update your code

 

[G1ZmO]

I tried that previously Minty and again now. The first shouldn't be a string anyway.

Its probably sometihng to do with VB/SQL single & double quote marks

 

[G1ZmO]

Static:
Ran it in the immediate window but unsure what to do thereafter.
Copied it into the window where the code is and ran that as strSQLfinal but got the same
surrounded the strSQLfinal with single quotes as below

    strSQL1 = "'UPDATE Item set Item.JobID = "
    strSQL2 = Me.cboTargetJob.Value & " "
    strSQL3 = "FROM Item INNER JOIN Job ON Item.JobID = Job.ID INNER JOIN "
    strSQL4 = "UsedIT ON Item.ID = UsedIT.ItemID INNER JOIN "
    strSQL5 = "MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID INNER JOIN "
    strSQL6 = "ItemType ON MakeAndModel.ItemTypeId = ItemType.ID INNER JOIN   ItemCategory ON ItemType.ItemCategoryID = ItemCategory.ID "
    strSQL7 = "WHERE Item.JobID = " & Me.cboSourceJob.Value & " AND ItemCategory.ID = " & Me.cboCategory.Value & "'"

but now getting error:

3078 - The MS database engine cannot find the input table or query "UPDATE Item set Item.JobID = 8861 .................

 

[Minty]

The syntax for an update query is as follows;

UPDATE yourTable INNER JOIN Your joins go here
SET yourtable.yourNumericfield = value , yourtable.field2 = 'Stringvalue'
WHERE your criteria goes here ;

Create a manual one with hard coded values that does what you want, then slowly add in your form variables until it either breaks or it works

edit - You don't need .Value on your control references as it's the default property, and in fact it can cause problems with certain controls.

 

[G1ZmO]

Ok I ran the following line in SQL management studio (which worked fine)

UPDATE Item set JobID = 8861 FROM Item INNER JOIN Job ON Item.JobID = Job.ID INNER JOIN UsedIT ON Item.ID = UsedIT.ItemID INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID INNER JOIN ItemCategory ON ItemType.ItemCategoryID = ItemCategory.ID WHERE Item.JobID = 9507 AND ItemCategory.ID = 13"

Then I copied the same line into my SQL string in VB as:

strSQL1 = "UPDATE Item set JobID = 8861 FROM Item INNER JOIN Job ON Item.JobID = Job.ID INNER JOIN UsedIT ON Item.ID = UsedIT.ItemID INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID INNER JOIN ItemCategory ON ItemType.ItemCategoryID = ItemCategory.ID WHERE Item.JobID = 9507 AND ItemCategory.ID = 13"

And got the following error "Syntax error (missing operator) in query expression '8861 FROM Item.......BLAH.......ItemCategory.ID'

It's missing the first bit of the query? i.e. UPDATE Item set JobID =

if I enclose the full query with single quotes like
strSQL1 = "'UPDATE......BLAH.....ItemCategory.ID = 13'"
then I get the error
The MS Access database engine cannot find the input table or query "UPDATE Item.....BLAH

 

[static]

Static:
Ran it in the immediate window but unsure what to do thereafter.

Ran what in the immediate window. You just run the code as normal. STOP acts like a breakpoint. While the code is paused you can query variables. Follow the instructions.

UPDATE Item set JobID = 8861 FROM Item

should be

UPDATE Item set JobID = 8861

You don't need FROM

 

[G1ZmO]

Same problem Static!

See the screenie. This is driving me bonkers. I can see nothing wrong with it!

Also, I needs the "FROM Item" if running it in SQL studio, maybe VBA is different

 

[Minty]

Ok I ran the following line in SQL management studio (which worked fine)

Do you actually mean SQL Server Management Studio, or the Access Query design view SQL window?

If you are building the query in SSMS it won't transfer to Access vba, there are subtle differences.

 

[G1ZmO]

SQL Server Management Studio
Damn, I thought that it'd just pass the query direct through to SQL server.
No doubt that's where I'm going wrong. Gah

 

[Minty]

If you do the same in the Access query designer, then switch to SQL view that will give you the syntax Access VBA expects. It's very similar but not exactly the same.

 

[G1ZmO]

GAAAAAh

This is what I have! All Access says is "Syntax error" but doesn't give a clue to what the error is!

UPDATE ITEM
(((INNER JOIN Job ON Item.JobID = Job.ID) 
INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID) 
INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID) 
INNER JOIN ItemCategory ON ItemType.ItemCategoryID = ItemCategory.ID
set JobID = 8861
WHERE Item.JobID = 9507 AND ItemCategory.ID = 13;

 

[Minty]

Is that a result of you building the query in the access query designer? (From scratch?)
Build it as a select query first to get the records you want.

 

[G1ZmO]

A fresh Monday morning try at this now

REbuilt the query in designer as a SELECT and retrieved the correct records.

SELECT Job.JobNumber, Item.ID AS ItemID, ItemType.ItemCategoryID, Item.JobID
FROM ((Item INNER JOIN Job ON Item.JobID = Job.ID) INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID) INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID
WHERE (((ItemType.ItemCategoryID)=13) AND ((Item.JobID)=9507));

Replaced the SELECT statement with UPDATE ITEM
Inserted the SET statement

UPDATE Item
FROM ((Item INNER JOIN Job ON Item.JobID = Job.ID) INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID) INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID
SET Item.JobID = 8861
WHERE (((ItemType.ItemCategoryID)=13) AND ((Item.JobID)=9507));

But getting syntax error in update statement

Took out the FROM as you mentioned previously but still syntax error

 

[Minty]

Try

UPDATE Item INNER JOIN Job ON Item.JobID = Job.ID INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID
SET Item.JobID = 8861
WHERE ItemType.ItemCategoryID=13) AND Item.JobID=9507 ;

I hate the number of brackets the query builder sometimes uses...

 

[G1ZmO]

Still syntax error (see attached)
Tried also without the extra bracket after 13

 

[Minty]

Okay - I must be suffering MondayMorningItis
Go back to your query builder create the select query , then change it to a update query and see what it gives you...

 

[G1ZmO]

Ok the following works as a select

SELECT Job.JobNumber, Item.ID AS ItemID, ItemType.ItemCategoryID, Item.JobID
FROM ((Item INNER JOIN Job ON Item.JobID = Job.ID) 
INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID) 
INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID
WHERE ItemType.ItemCategoryID=13 AND Item.JobID=9507;

converted to Update like so generates syntax error (tried with and without the FROM)

UPDATE Item
FROM ((Item INNER JOIN Job ON Item.JobID = Job.ID) 
INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID) 
INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID
SET Item.JobID = 8861
WHERE ItemType.ItemCategoryID=13 AND Item.JobID=9507;

Wondering if I should make the select into an SQL view and try a simple update on the view without all the inner joins in access....

 

[Minty]

Convert it in the query designer - right click and select query type then choose Update Query.