VBA SQL, stop append confirmation, syntax for variables

Topic

New Reply

Is there property setting or alternative way to stop the confirmation for an append?

I am parsing through thousands of records, building a string for each group, and inserting the info into a table.

I am running the following “insert into” in VBA:

strinsertsql = “INSERT INTO PubTable (MFBIndex, UFSiteString, SumUFSiteString, ” & _
“NumberUFSiteString ) ” & _
“values (” & strmfbidx & “, ” & “‘” & strpass & “‘” & “, ” & sumid & “, ” & count & “);”
DoCmd.RunSQL strinsertsql

I get a msgbox with each execution and don’t want to hit OK each time. Any better way to do this or just a property I can set. I can’t seem to find one on my own.

Additional question: the ” & xxxx & ” format for the values. Is this for all variables, ie string, date, number? Does anyone have a source for various permutations of acceptable Values syntax for variables in access

Reply | Quote

Replies

Is there property setting or alternative way to stop the confirmation for an append?

I am parsing through thousands of records, building a string for each group, and inserting the info into a table.

I am running the following “insert into” in VBA:

strinsertsql = “INSERT INTO PubTable (MFBIndex, UFSiteString, SumUFSiteString, ” & _
“NumberUFSiteString ) ” & _
“values (” & strmfbidx & “, ” & “‘” & strpass & “‘” & “, ” & sumid & “, ” & count & “);”
DoCmd.RunSQL strinsertsql

I get a msgbox with each execution and don’t want to hit OK each time. Any better way to do this or just a property I can set. I can’t seem to find one on my own.

DoCmd.SetWarnings False before the insert
DoCmd.SetWarnings True after the insert

Reply | Quote

Insert a line

DoCmd.SetWarnings False

above the RunSQL line, and a line

DoCmd.SetWarnings True below. Alternatively, you can change

DoCmd.RunSQL strinsertsql

to

CurrentDb.Execute strinsertsql

Number values can be used “as is”; text values must be enclosed in single quotes or you can concatenate with Chr(34), and date values must be enclosed in # characters. If there’s a chance that your database will be used by people with non-US date settings, you must convert the date to US format.

For example:

Code:

Dim lngCount As Long
Dim strName As String
Dim dtmDate As Date
dim strSQL As String

lngCount = 329
strName = "John"
dtmDate = #04/08/2009#

strSQL = "INSERT INTO tblData (Field1, Field2, Field3) VALUES (" & _
 lngCount & ", '" & strName & "', #" & Format(dtmDate, "mm/dd/yyyy") & "#)"

Reply | Quote

Use :
strinsertsql = “INSERT INTO PubTable (MFBIndex, UFSiteString, SumUFSiteString, ” & _
“NumberUFSiteString ) ” & _
“values (” & strmfbidx & “, ” & “‘” & strpass & “‘” & “, ” & sumid & “, ” & count & “);”
DoCmd.SetWarnings False
DoCmd.RunSQL strinsertsql
DoCmd.SetWarnings True
to eliminate the message box

For your additional question, do you mean for the name of the variables ?

Reply | Quote

thanks everyone, worked fine.

On the second question, I was looking at the building of the SQL from the wrong perspective. I missed the connection between what I was getting in a msgbox trap and the way it was executing. My error was in missing that one has to build the values portion of the SQL in pieces with concatenation. Seem I totally ignored the concatenation.

I was trying to make

“values (strmfbidx, strpass, sumid, count);” give me “Values (100, ‘1,2,3,4,5’, 2300, 50);”. I was getting “Values (100,1,2,3,4,5, sumid,count);” .

Don’t know why I didn’t make the connection until I read the responses: hence the tagline.

Reply | Quote