SQL in Access: INSERT INTO not working

[abcrf]

I'm probably doing something wrong, since I'm pretty inexperienced with SQL, but I'm trying to insert values into one table from another, where 3 other column values are equivalent.

Dim vInsertLOI As String
vInsertLOI = "INSERT INTO TBL_DocReview_byStudy([Date of LOI]) " _
& "SELECT [DATE SIGNED] " _
& "FROM [TBL_INVITATION LETTERS] " _
& "INNER JOIN TBL_DocReview_byStudy " _
& "ON [TBL_INVITATION LETTERS].[DATE SIGNED]=TBL_DocReview_byStudy.[Date of LOI] " _
& "AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study " _
& "AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB"

and then later

CurrentDb.Execute vInsertLOI, dbFailOnError And dbSeeChanges

It's not throwing any errors, but when I open up the target table, nothing has been added. I'm thinking that maybe it is not recognizing any pair of records where the columns in question match up (though there are many).

Thanks for your help!

[zmbd]

On the line following your string vInsertLOI, please insert
(somewhere right before your 1.CurrentDb.Execute )
Debug.Print "rslvd_vInsertLOI==" & vInsertLOI

You can either step thru the code, or simply attempt to run it at this point.
Press <ctrl><g> after running/stepping the code to see the string
You should have something like:

rslvd_vInsertLOI==INSERT INTO TBL_DocReview_byStudy([Date of LOI]) SELECT [DATE SIGNED] (...)

Double check that the string is resolving correctly.

Another hint is to build the basic select query first, make sure that it is returning the desired records. It's usually simple to convert the query to an action query afterwards and then use the SQL from the query builder in the code.

Also you should get into the habit of setting an object for the current database to ensure that you are actually referring to the correct object and to help with memory overhead...:

Dim zDB as DAO.Database
Dim vInsertLOI As String
'(...)
set zDB = CurrentDB
'(...)
 vInsertLOI = "INSERT INTO TBL_ (...)
'
'(...)
zDB.Execute vInsertLOI (...)
'
'(...)
If not zDB is Nothing Then set zDB=Nothing 
'
'(...)

-z

[abcrf]

Returns:

rslvd_vInsertLOI==INSERT INTO TBL_DocReview_byStudy([Date of LOI]) SELECT [DATE SIGNED] FROM [TBL_INVITATION LETTERS] INNER JOIN TBL_DocReview_byStudy ON [TBL_INVITATION LETTERS].[DATE SIGNED]=TBL_DocReview_byStudy.[Date of LOI] AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB

So it's working fine, the problem is in the SQL. Might it make sense to reclassify this as a SQL Question?

[Rabbit]

Change it into a select and run it in a query to see if it actually returns rows.

[abcrf]

It throws runtime error 3065 (cannot execute a select query). I'm guessing that I'm misunderstanding or messing up the syntax of the INNER JOIN...ON...AND, because that's the only thing I haven't used before.

[Rabbit]

That's probably because you're trying to run it in VBA. Run it in a query instead.

[zmbd]

Open query editor
cancel add tables dialog
switch to SQL view
Insert just the select portion of your insert action query

SELECT [DATE SIGNED] 
FROM [TBL_INVITATION LETTERS] 
INNER JOIN TBL_DocReview_byStudy 
    ON [TBL_INVITATION LETTERS].[DATE SIGNED]=TBL_DocReview_byStudy.[Date of LOI] 
        AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study 
            AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB

Do any records return?
No return = no insert :)

This is what I meant by building your select query first.
The query editor doesn't do too bad a job building the underlying SQL and once the select query returns the desired records correctly you can change it to the append/insert, switch to the SQL view, and copy the string for use in VBA (or save it and call it from code - I tend to hide action queries in the vba away from little fingers :D )

[abcrf]

Okay, I've got it returning rows (user error, as it ever was - the syntax was fine) but it's inserting the rows into new records rather than the records referred to in the ON portion of the SELECT statement. That makes sense, I suppose, but I can't add a WHERE clause to direct the insertion, since INSERT INTO doesn't support WHERE. Any ideas how I get this to insert correctly?

[Rabbit]

What you're talking about is an UPDATE query. Not an INSERT query.

[abcrf]

Fair enough. With the update query, though, I'm getting RT error 3075 "syntax error (missing operator) in query expression..."

Referring to:

Dim vUpdateLOI As String
vUpdateLOI = "UPDATE TBL_DocReview_byStudy " _
& "SET TBL_DocReview_byStudy.[Date of LOI]=[TBL_INVITATION LETTERS].[DATE SIGNED] " _
& "FROM [TBL_INVITATION LETTERS] INNER JOIN TBL_DocReview_byStudy " _
& "ON [TBL_INVITATION LETTERS].[LAST NAME, FIRST NAME]=TBL_DocReview_byStudy.[Member] " _
& "AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study " _
& "AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB " _
& "WHERE [TBL_INVITATION LETTERS].[LAST NAME, FIRST NAME]=TBL_DocReview_byStudy.[Member] " _
& "AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study " _
& "AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB"

[zmbd]

once again, you need to debug.print the string to insure it is resolving correctly.

This error is quite common with a mis-spaced text or missing comma in the SQL

I also tend to close all of my SQL with a semicolon, although Access doesn't seem to care, it is a good habit to get in to doing as many interpreters are looking for this closing terminator.

[abcrf]

Yeah, I did that, and it's printing the string exactly as expected.

[zmbd]

then post the resolved string.

[abcrf]

rslvd_vUpdateLOI==
UPDATE TBL_DocReview_byStudy 
SET TBL_DocReview_byStudy.[Date of LOI]=[TBL_INVITATION LETTERS].[DATE SIGNED] 
FROM [TBL_INVITATION LETTERS] 
  INNER JOIN TBL_DocReview_byStudy 
    ON [TBL_INVITATION LETTERS].[LAST NAME, FIRST NAME]=TBL_DocReview_byStudy.[Member] 
      AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study 
      AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB 
WHERE [TBL_INVITATION LETTERS].[LAST NAME, FIRST NAME]=TBL_DocReview_byStudy.[Member] 
  AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study 
  AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB

[zmbd]

+ You really should not use a comma (line6 and 9, etc...) in your field names.
I don't care if it is enclosed in square-brackets, this has caused me no end of issues when I inherit or am asked to help with issues in databases.... keep in mind, I'm a Chemist not a DBA/Programmer :)
-alphanumeric:
+Access 2007 reserved words and symbols
+Access reserved words and symbols
+AllenBrowne- Problem names and reserved words in Access

I personally avoid spaces in the field names too, either using the underscore or the capitalized word method. This comes from the old school of programing and naming; however, it certainly makes life much easier. I never use a non-alphanumeric in field names... once again, old school; however, symbols have been the root source of many a hard to find issue.

rslvd_vUpdateLOI==
UPDATE TBL_DocReview_byStudy 
SET TBL_DocReview_byStudy.[Date of LOI]=[TBL_INVITATION LETTERS].[DATE SIGNED] 
FROM [TBL_INVITATION LETTERS] 
  INNER JOIN TBL_DocReview_byStudy 
    ON [TBL_INVITATION LETTERS].[LAST NAME, FIRST NAME]=TBL_DocReview_byStudy.[Member] 
      AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study 
      AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB 
WHERE [TBL_INVITATION LETTERS].[LAST NAME, FIRST NAME]=TBL_DocReview_byStudy.[Member] 
  AND [TBL_INVITATION LETTERS].STUDY=TBL_DocReview_byStudy.Study 
  AND [TBL_INVITATION LETTERS].[DC TYPE]=TBL_DocReview_byStudy.CDB

[abcrf]

Yes, I'm with you. Unfortunately, I'm building on a project that someone else started, and their naming was pretty messy. Commas, spaces, etc. I'd try to go through and standardize it, but it will be pretty involved. I'll start by at least standardizing for these tables, though, to see if that changes the outcome.

[abcrf]

It's now:

Dim vUpdateLOI As String
vUpdateLOI = "UPDATE TBL_DocReview_byStudy " _
& "SET TBL_DocReview_byStudy.DateLOI = TBL_LOI.DateSigned " _
& "FROM TBL_LOI INNER JOIN TBL_DocReview_byStudy " _
& "ON TBL_LOI.MemberName = TBL_DocReview_byStudy.Member " _
& "AND TBL_LOI.Study = TBL_DocReview_byStudy.Study " _
& "AND TBL_LOI.CDB = TBL_DocReview_byStudy.CDB " _
& "WHERE TBL_LOI.MemberName = TBL_DocReview_byStudy.Member " _
& "AND TBL_LOI.Study = TBL_DocReview_byStudy.Study " _
& "AND TBL_LOI.CDB = TBL_DocReview_byStudy.CDB"

But, no change. Still the syntax error (missing operator), and still prints the following as the string"

rslvd_vUpdateLOI==UPDATE TBL_DocReview_byStudy
SET TBL_DocReview_byStudy.DateLOI = TBL_LOI.DateSigned
FROM TBL_LOI
  INNER JOIN TBL_DocReview_byStudy
    ON TBL_LOI.MemberName = TBL_DocReview_byStudy.Member
      AND TBL_LOI.Study = TBL_DocReview_byStudy.Study
      AND TBL_LOI.CDB = TBL_DocReview_byStudy.CDB
WHERE TBL_LOI.MemberName = TBL_DocReview_byStudy.Member
  AND TBL_LOI.Study = TBL_DocReview_byStudy.Study
  AND TBL_LOI.CDB = TBL_DocReview_byStudy.CDB

A potentially useful piece of information: the error message telling me there's a missing operator spefically quotes lines 4-7 of the vba code.

[Rabbit]

The syntax of an UPDATE query with a JOIN in Access is thusly:

UPDATE Table1 
INNER JOIN Table2 
ON Table1.Field2 = Table2.Field2 
SET Table2.Field1 = Table1.Field1;