INSERT Syntax Issue

[rokuk]

Hey, I'm having trouble for some reason with this query. It's telling me it can't find the object "Deletions" which is a valid table. Directly above this I do another operation with the same table referenced the same exact way. Is this a syntax thing?

'this works
Dim qryImportRecords As String
qryImportRecords = "INSERT INTO [Deletions] IN '" & _
                  strPathToCurrentDatabase & _ 
                  "' SELECT * FROM [table2$] WHERE NOT IsNull([table2$].field1)"
Cn.Execute qryImportDeletionRecords


'this doesn't work... ?!?
Dim qryTestAndTransfer As String
qryTestAndTransfer = "INSERT INTO tempTable SELECT * FROM [Deletions]"
Cn.Execute qryTestAndTransfer

I am going to add on some WHERE restrictions at the end here, but I took those off hoping it would solve my problem. But it didn't. What's wrong with this query - shouldn't it be putting everything from one table into the other as-is?! I've also tried this as a DoCmd.RunSQL, but still, same issue...

 

[WayneRyan]

Rokuk,

If you Insert Into [Deletions] In '...'

Shouldn't you:

INSERT INTO tempTable Select * From [Deletions] In '...'

Wayne

 

[rokuk]

hmm

I'm not sure about that.

I've just discovered that it works just fine when I run it from the query wizard in SQL view... but it doesn't work when being used with currentdb.execute. I have no clue why... and it's pretty damn frustrating because I'm trying to automate this so I don't HAVE to use the query wizard / execute button every time I want to do this ; )

I'm continuing to look... hmmmm

 

[boblarson]

Instead of Currentdb.Execute, try using the

Dim strSQL As String

strSQL = "YourSQLHere"
DoCmd.Run SQL strSQL

You will have to use DoCmd.SetWarnings False if you don't want the warning messages, but make sure you put DoCmd.SetWarnings True right after the RunSQL and also in an error handler as the first item.

 

[rokuk]

still no go

That's a good suggestion, and I had tried it previously but I just tried it again and it's still doing the same thing. With the warnings on, it tells me it's going to import 0 (zero) rows, but when I run the same exact query from the SQL view of the query wizard it tells me it's going to import the correct number of rows...

I even took out the string variable and:

DoCmd.RunSQL "INSERT INTO tempTable SELECT * FROM Deletions"

Still does the same thing... while "INSERT INTO tempTable SELECT * FROM Deletions" in Access SQL view runs it fine.

Is there a reason there would ever be any difference between running through VBA and running by the query wizard's SQL view?

 

[rokuk]

movin on!

I wanted to update this thread for the sake of completion.

I was not aware you need to end a SQL string in VBA with a semi-colon ";" - I had been NOT doing this in the SQL query editor in Access for the longest time, and apparently it puts it in for you if you forget. Well, I was not so much forgetting, but never knowing because it never caused a problem for me before.

How annoying to finally figure out that was causing one of my issues!!

But yes, things have been settled. Thank you for all the suggestions, they were quite helpful!