VBA & SQL syntax error...again...

Well, I've made a little progress in my attempt to create an append query w/ VBA having a variable destination db. The first portion of code seems to work for now....That is, at least I don't get errors from it.

However, when I run the code I get a syntax error (run-time 3134) in the INSERT INTO statement, but the de-bugger doesn't highlight that portion, instead it highlights the portion in w/ asterisks below. Since that portion depends on the INSERT INTO portion (strSQL = "INSERT INTO...) I'm guessing that's where the problem lies, but I can't seem to fix it.

Here's the code:
____________________

Dim qdf As QueryDef
Dim strSQL As String
Dim ServerPath As Variant

ServerPath = DLookup("[FilePath]", "tblFilePath", "[FilePath] = '" & Me.FilePath & "'")

If IsNull(ServerPath) Then
MsgBox ("Unknown directory.")

Else
strSQL = "INSERT INTO Main Table (Report Number, Rating, Pass " & _
"Fail, Non Rated, Chief Insp Review " & _
"Inspection Type, TEC, Date " & _
"Shift, Time, Inspector Number " & _
"Supervisor Rank, Supervisor Name, Location " & _
"Equipment Type, Equipment SN, Workcenter " & _
"Description, Main Assessee) " & _
"IN '" & ServerPath & "' " & _
"SELECT Report Number, Rating, Pass, Fail, Non Rated " & _
"Chief Insp Review, Inspection Type " & _
"TEC, Date, Shift, Time, Inspector Number " & _
"Supervisor Rank, Supervisor Name, Location, " & _
"Equipment Type, Equipment SN, Workcenter, Description, Main Assessee" & _
"FROM [Main Table] " & _
"WHERE [Main Table].Date <Now()"

Set qdf = CurrentDb.QueryDefs("X")
***qdf.SQL = strSQL***
qdf.Close
End If
____________________________

I've tried to check this against other samples of VBA in hopes of finding an easy mistake like extra spaces, missing quotes (at least where I know they belong...), etc.

In the attachment, open the file TestX.

Thanks.

First of all, just by looking at the statement: Any field that has spaces in it needs to be in brackets. For instance, [This field has spaces in it]

I will paste the SQL string that was produced by your code. try to paste it in to a new query and try to view in design. you will have an alot of errors.

INSERT INTO Main Table (Report Number, Rating, Pass Fail, Non Rated, Chief Insp Review Inspection Type, TEC, Date Shift, Time, Inspector Number Supervisor Rank, Supervisor Name, Location Equipment Type, Equipment SN, Workcenter Description, Main Assessee) IN 'd:\Documents and Settings\adam.brunkow\Desktop\Blank QA 2K6\TestArchiveX.mdb' SELECT Report Number, Rating, Pass, Fail, Non Rated Chief Insp Review, Inspection Type TEC, Date, Shift, Time, Inspector Number Supervisor Rank, Supervisor Name, Location, Equipment Type, Equipment SN, Workcenter, Description, Main AssesseeFROM [Main Table] WHERE [Main Table].Date <Now()

sam

I included the brackets in the code and still get errors. How do you take the VBA to SQL?


strSQL = "INSERT INTO Main Table ([Report Number], Rating, Pass " & _
"Fail, [Non Rated], [Chief Insp Review] " & _
"[Inspection Type], TEC, Date " & _
"Shift, Time, [Inspector Number] " & _
"[Supervisor Rank], [Supervisor Name], Location " & _
"[Equipment Type], [Equipment SN], Workcenter " & _
"Description, [Main Assessee]) " & _
"IN '" & ServerPath & "' " & _
"SELECT [Report Number], Rating, Pass, Fail, [Non Rated] " & _
"[Chief Insp Review], [Inspection Type] " & _
"TEC, Date, Shift, Time, [Inspector Number] " & _
"[Supervisor Rank], [Supervisor Name], Location, " & _
"[Equipment Type], [Equipment SN], Workcenter, Description, [Main Assessee]" & _
"FROM [Main Table] " & _
"WHERE [Main Table].Date <Now()"

click on one row to add a break. i added one on: Set qdf = CurrentDb.QueryDefs("X")
the row turns brown and there is a circle in the margin of the main window. this will cause your code to execute up until that point, it will stop and wait for you. so after you added the break, you click on the Form's button to execute the code. The VBE will open and your break will be highlighted. then you type in to the Immediate Window (go to view if you don't see it) and enter the following: ? strSQL then press enter (so its question mark, space, and the variable name) you will then see the entire value for that variable on the next line. you copy that and paste it into a new Query.


I would like to point out a few things:
1. i made this mistake as well in the beggining. Make sure your FieldNames dont' have spaces. this will help your SQL coding
2. try to run your code in sql if you have problems in VBA. (for example, without to much inspection i can see that there is no space between ]From
i don't think that this is a problem in your case, but i don't think you were aware of it. If you see the entire string as it is handed to the compiler you can dedect a lot of these mistakes
3. I am not sure what the answer to this is. I would like to point your attention to the Var (not string) ServerPath. you placed it between two single quotes. i am not sure about that. but look it up in the help file.

sam

Thanks for the tips. I'll try working on the SQL and see how that works. I realized about half-way through building this database about using spaces in names for Access. I can't go back and change it, however, because the database is built and in use. I'm afraid it would take to long and the chances of messing something else up would be to great.

As far as the single quotes go....I'm not exactly what those are for, either, but I'm not getting an error in that area...right now. I was before, but so far it looks like it's been fixed. If I come across another problem in that area later, I'll be sure to check it out.

I just did the Immediate Window "thing" to view the SQL produced by the VBA. Is there a way to do the exact opposite? That would cure a lot of problems.

if i understand correctly, you would like to rewrite your SQL statement so that it makes sense, right?!! (atleast it makes sense to Access)
so what i like to do is start a new Query in design View, and then change to SQL View, paste in the SQL Code generated by VBA, and try to view it again in Design View. Your code will produce some errors. what i like to do next is read and read it and read it. actually, what i do is erase parts of it, and strip it down to the basics and then i paste in more and more code so that i can isolate the error. I don't know of any better way.

sam

That's kinda what I'm in the middle of right now. I'll probably end up working this through the weekend. Tomorrow's gonna be a busy day at work.

Thanks again for your help. I'll post again when I either make new progress or new problems.

TestQry SQL:

INSERT INTO [Main Table] ( [Report Number], Rating, Pass, Fail, [Non Rated], [Chief Insp Review], [Inspection Type], TEC, [Date], Shift, [Time], [Inspector Number], [Supervisor Rank], [Supervisor Name], Location, [Equipment Type], [Equipment SN], Workcenter, Description, [Main Assessee] ) IN 'C:\Documents and Settings\Owner\Desktop\TestXxxxxxxxxxxxxx.mdb'
SELECT [Main Table].[Report Number], [Main Table].Rating, [Main Table].Pass, [Main Table].Fail, [Main Table].[Non Rated], [Main Table].[Chief Insp Review], [Main Table].[Inspection Type], [Main Table].TEC, [Main Table].Date, [Main Table].Shift, [Main Table].Time, [Main Table].[Inspector Number], [Main Table].[Supervisor Rank], [Main Table].[Supervisor Name], [Main Table].Location, [Main Table].[Equipment Type], [Main Table].[Equipment SN], [Main Table].Workcenter, [Main Table].Description, [Main Table].[Main Assessee]
FROM [Main Table]
WHERE ((([Main Table].Date)<Now()));

It's working, but...DoCmd.RunSQL???

Well, I have this thing pretty much figured out, except I couldn't get the SQL to run. So I ended up putting in a DoCmd.OpenQuery for the built-in "X" query. This works alright, but I would like to do this properly. Below is the code as it works now.

_______________________

Private Sub Command0_Click()

Me.Refresh

Dim qdf As QueryDef
Dim strSQL As String
Dim ServerPath As Variant

ServerPath = DLookup("[FilePath]", "tblFilePath", "[FilePath] = '" & Me.FilePath & "'")

If IsNull(ServerPath) Then
MsgBox ("Unknown directory.")

Else
strSQL = "INSERT INTO [Main Table] ( [Report Number], Rating, Pass, Fail, [Non Rated], [Chief Insp Review], [Inspection Type], TEC, [Date], Shift, [Time], [Inspector Number], [Supervisor Rank], [Supervisor Name], Location, [Equipment Type], [Equipment SN], Workcenter, Description, [Main Assessee] ) " & _
"IN '" & ServerPath & "' " & _
"SELECT [Main Table].[Report Number], [Main Table].Rating, [Main Table].Pass, [Main Table].Fail, [Main Table].[Non Rated], [Main Table].[Chief Insp Review], [Main Table].[Inspection Type], [Main Table].TEC, [Main Table].Date, [Main Table].Shift, [Main Table].Time, [Main Table].[Inspector Number], [Main Table].[Supervisor Rank], [Main Table].[Supervisor Name], [Main Table].Location, [Main Table].[Equipment Type], [Main Table].[Equipment SN], [Main Table].Workcenter, [Main Table].Description, [Main Table].[Main Assessee]" & _
"FROM [Main Table] " & _
"WHERE ((([Main Table].Date)<Now()))"

Set qdf = CurrentDb.QueryDefs("X")
qdf.SQL = strSQL
qdf.Close
End If

DoCmd.OpenQuery "X", acViewNormal, acEdit

End Sub
_____________________


P.S. How do you post the code in it's own little window with the scroll bars? It really cleans up a post, IMO.

Updated Attachment

Here are the updated DBs.

Instead of creating a temporary query definition you could just put

DoCmd.RunSQL (strsql)

J.

Well, I finally got it all worked out. Thanks for the help. I'll make up a sample db for this and post it to the Sample DB forum.




So just to sum things up...The original problem was that other units will be using this db, and natuarlly their file locations for the destination db would be different than ours.

This code sets the DestinationDB property of an APPEND query as a variable based on a table's field. The form has record navigaotors disabled so there's only one record in the table (which can be typed over in the form in case people move their archive db.)

The rest of it is just the SQL in VBA for the APPEND query.

Fixed. Thanks for the help.

http://www.access-programmers.co.uk/forums/showthread.php?t=106630