RunSQL Won't Work

[Geoff Codd]

Hi there,

I have the following code

Function RvS_Reports()

Dim vDBName As Variant
Dim vRvS_Exports
Dim vQuery As Variant
Dim vQuery1 As Variant
Dim sSQL1 As String

vDBName = "C:\RvS Reporting Reconciliation\BE_RvS_Exports.mdb"

Set vRvS_Exports = CurrentDb().OpenRecordset("tblRvS_Exports")

Do Until vRvS_Exports.EOF

DoCmd.OpenForm "frmPlease_Wait_Message", acNormal, "", "", acReadOnly, acNormal
DoCmd.RepaintObject acForm, "frmPlease_Wait_Message"

vQuery = "[" + vRvS_Exports!Query_Name + "]"
vQuery1 = "[" + vRvS_Exports!Query_Name + "]" + ".*"

'WONT WORK
sSQL1 = "SELECT """ & vQuery1 & """ " & _
" INTO """ & vQuery & """ " & _
" IN """ & vDBName & """ " & _
" FROM """ & vQuery & """ ;"

'WORKS
sSQL1 = "SELECT [qryQ2000_Reporting_Summary_(1)].* " & _
" INTO [qryQ2000_Reporting_Summary_(1)] " & _
" IN 'C:\RvS Reporting Reconciliation\BE_RvS_Exports.mdb'" & _
" FROM [qryQ2000_Reporting_Summary_(1)];"

DoCmd.RunSQL sSQL1

vRvS_Exports.MoveNext
Loop
vRvS_Exports.Close

DoCmd.Close acForm, "frmPlease_Wait_Message"

End Function

I cant' seem to get the SQL Statemnet to work, it is suppose to run the same statement for each query listed in a table.

Any ideas anyone.

Thanks is advance
Geoff

 

[namliam]

Try:
sSQL1 = "SELECT " & vQuery1 & " " & _
" INTO " & vQuery & " " & _
" IN """ & vDBName & """ " & _
" FROM " & vQuery & "] ;"

Regards

 

[Geoff Codd]

Thanks, Works a treat. Could you explain when and why I should use quotes and how many.

Thanks
Geoff

 

[namliam]

"Hello""" & "crazy""" & " person" will end up like:
Hello "crazy" person

Thus your "SELECT """ & vQuery1 & """ "
will end up beeing "SELECT "[qryQ2000_Reporting_Summary_(1)].*" "
which will generate the error.

Is that enough of an explenation ?

Regards

 

[modest]

You should only use quotes when you're setting a value equal to something (NAME = "Jack" or NAME like "Jac*")

When referencing the name of a field, form, query, table, report, macro or so on you don't need quotes. If the name has spaces you use brackets "[" and "]" to encapsulate the name. This is Access's naming schema, but as a general rule you should use brackets at all times to help readers distinguish objects in your code, as well as to produce more adaptable code.

-modest

 

[Rich]

You should only use quotes when you're setting a value equal to something (NAME = "Jack" or NAME like "Jac*")

When referencing the name of a field, form, query, table, report, macro or so on you don't need quotes.
-modest

I think you're getting confused between sql and vba

 

[Liv Manto]

If you wish to check the syntax, before running the sql , put it in a messagebox.

 

[modest]

I think you're getting confused between sql and vba

The SQL generated from VBA is read differently from the Query Builder. I remember running into that problem myself before.

And the best way to check the code is to put in a msgbox as suggested by Live


why can't access just be a little more like oracle *wishful thinking*
-modest