Append Query with VBA and SQL

[nxqviet]

Hi all,

I have this SQL code that will Append some record from one table to
another, from tblPrices to a table with a name defined by the SessionID
of the user "varName ". This "varName " will also be the criteria of
the append query. This code does not work for some reason, saying that
the syntax is error. Can some one help please.

Thank.

=================CODE Begin===============
'Append prices from temp table to New editing table
Dim varName As Variant
varName = SessionID.Value

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryAppendEdit")

' Define Criteria

strCriteria = strCriteria & "tblPrices.SessionID = " & Chr(34) &
varName


' Build the new SQL statement incorporating the string
strSQL = "INSERT INTO " & varName & "(ServiceID, ServiceName, Price) "
& _
"SELECT tblPrices.ServiceID, tblPrices.ServiceName, tblPrices.Price
FROM tblPrices" & _
"WHERE " & strCriteria

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendEdit"
DoCmd.SetWarnings True

==========End Code==================

 

[John Spencer]

IF SessionID is a string value then you need to to include quotes around it in
strCriteria and if it contains spaces or other "special" characters such as
dashes, plus signs, etc then you need to have brackets around it as a table name/

Also instead of creating an querydef, I would just execute the query with

Db.Execute strSQL, dbfailonerror


=================CODE Begin===============
'Append prices from temp table to New editing table
Dim varName As Variant
varName = SessionID.Value

' Declare variables
Dim db As DAO.Database

'Dim qdf As DAO.QueryDef -Not needed with change

Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryAppendEdit")

' Define Criteria
strCriteria = strCriteria & "tblPrices.SessionID = " _
& Chr(34) & varName & Chr(34)


' Build the new SQL statement incorporating the string
strSQL = "INSERT INTO [" & varName & _
"] (ServiceID, ServiceName, Price) " & _
"SELECT tblPrices.ServiceID, tblPrices.ServiceName, " & _
" tblPrices.Price " & _
" FROM tblPrices" & _
"WHERE " & strCriteria

Debug.print StrSQL
'print the statement in debug window so
'you can check it out if it is failing. Once working -
'delete these lines

db.Execute strSQL, dbFailOnError

'get rid of the rest of this

' Apply the new SQL statement to the query
'qdf.SQL = strSQL
' Open the query
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "qryAppendEdit"
'DoCmd.SetWarnings True

==========End Code==================