Building SQL statements

[Guest]

'strTest is built in the following fashion:

strTest = _
""" Insert Into """ & " & " & " strTable " & " & " & """ Values (""" & " & "
& _
"Chr$(34) " & "&" & " rst!fields(0) " & "&" & " Chr$(34) " & "&" & " "", ""
" & "&" &_
"Chr$(34) " & "&" & " rst!fields(1) " & "&" & " Chr$(34) " & "&" & " "", ""
" & "&" &_
" Chr$(34) " & "&" & " rst!fields(2) " & "&" & " Chr$(34) " & "&" & " "")"" "

msgbox strTest 'correctly produces the following string:

"Insert Into " & strTable & " Values (" &
Chr$(34) & rst!fields(0) & Chr$(34) & ", " &
Chr$(34) & rst!fields(1) & Chr$(34) & ", " &
Chr$(34) & rst!fields(2) & Chr$(34) & ")"


'Given rst and strTable = "myTable",
' is there a way to resolve the string into an executable sql statment like
the
' following?

Insert Into myTable Values ("xyz", "1a2b3c", "123")


Thanks for your help

 

[Steve Schapel]

Mark,

There are a number of problems with your SQL statement. Apart from the
huge overcomplicatrion and the large number of redundant ""s and stuff.
First of all, the Fields property of a recordset is a property so you
should be using a . rather than a ! as I am pretty sure rst!fields(0)
willnot compute. And secondly, you haven't designated the fields in the
destination table. In other words, your stated desired outcome "Insert
Into myTable Values ("xyz", "1a2b3c", "123")" is not valid. So, try
something like this...

strTest = "INSERT INTO " & strTable ( [OneField], [AnotherField],
[NextField] ) & _
" VALUES ( '" & rst.fields(0) & "', '" & rst.fields(1) & "', '" &
rst.fields(2) & "' )"

 

[Guest]

Steve - Thanks for the comments.

What I'm going for is to build/create a sql statement in a function/class
given a recordset (rst) (and addtional fields or replacement fields). It
would be sent back to the calling program via a string. The calling program
would resolve the variables (e.g., strTable and rst.fields(0) ) and execute
the statement with each new record for output.

The first five lines of the original post was biulding the string to be sent
back to the calling program. The middle part (i.e., msgbox) shows the string
that would be sent back. The calling program would then somehow resolve
(resolving these variables in a string is what I don't know how to make it
do, it may not be possible) the variables in the string and execute it.

The idea is the have the string sent back once, and have the string
"resolved" in the calling program with each new record being inserted into
the table.

The alternative is for the function to resolve all the variable references
and send back the executable string (i.e., "Insert Into myTable (f1, f2, f3)
Values ("xyz", "1a2b3c", "123")" for each record for output; I was just
trying to avoid the function call rebuilding the string with each new record
hoping to reduce processing time.

Thank you for any additonal comments you may have.

Mark,

There are a number of problems with your SQL statement. Apart from the
huge overcomplicatrion and the large number of redundant ""s and stuff.
First of all, the Fields property of a recordset is a property so you
should be using a . rather than a ! as I am pretty sure rst!fields(0)
willnot compute. And secondly, you haven't designated the fields in the
destination table. In other words, your stated desired outcome "Insert
Into myTable Values ("xyz", "1a2b3c", "123")" is not valid. So, try
something like this...

strTest = "INSERT INTO " & strTable ( [OneField], [AnotherField],
[NextField] ) & _
" VALUES ( '" & rst.fields(0) & "', '" & rst.fields(1) & "', '" &
rst.fields(2) & "' )"

--
Steve Schapel, Microsoft Access MVP

'strTest is built in the following fashion:

strTest = _
""" Insert Into """ & " & " & " strTable " & " & " & """ Values (""" & " & "
& _
"Chr$(34) " & "&" & " rst!fields(0) " & "&" & " Chr$(34) " & "&" & " "", ""
" & "&" &_
"Chr$(34) " & "&" & " rst!fields(1) " & "&" & " Chr$(34) " & "&" & " "", ""
" & "&" &_
" Chr$(34) " & "&" & " rst!fields(2) " & "&" & " Chr$(34) " & "&" & " "")"" "

msgbox strTest 'correctly produces the following string:

"Insert Into " & strTable & " Values (" &
Chr$(34) & rst!fields(0) & Chr$(34) & ", " &
Chr$(34) & rst!fields(1) & Chr$(34) & ", " &
Chr$(34) & rst!fields(2) & Chr$(34) & ")"


'Given rst and strTable = "myTable",
' is there a way to resolve the string into an executable sql statment like
the
' following?

Insert Into myTable Values ("xyz", "1a2b3c", "123")


Thanks for your help

 

[Steve Schapel]

Mark,

The expression I gave you returns a string. This string can be passed
to another routine elsewhere for execution as a SQL string, using the
CurrentDb.Execute method or the DoCmd.RunSQL method, as long as the
Recordet rst has been set in that remote routine, and also strTable has
a value. Sorry to be thick, but I can't see what the problem is.