Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  2. #2
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wejones' post='769719' date='08-Apr-2009 10:18']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.[/quote]
    DoCmd.SetWarnings False before the insert
    DoCmd.SetWarnings True after the insert
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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") & "#)"

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wejones' post='769719' date='08-Apr-2009 17:18'][/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 ?
    Francois

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •