Results 1 to 8 of 8
  1. #1
    Kaye960 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    9

    Appending records to a table with increasing id numbers

    I need to be able to append multiple records to a table with an ID# that is 1 more than the previous number. I have created a query that will do this for 1 record, but if I have multiple records to append, I get an error. I think I need to do a loop, but I can't get it to work.
    Code:
    Private Sub Command18_Click()
    On Error GoTo ErrorHandler
    Dim strSql As String
    Dim rs As DAO.Recordset
    strSql = "INSERT INTO dbo_tblLocations ( LocationID, Division, LocationName, DisplayType, DisplayColor, Address1, City, State, ZIP, Country, Contact, EmailAddress, [Phone#], Rooms ) " & vbCrLf & _
    "SELECT Nz(DMax(""LocationID"",""dbo_tblLocations""))+1 AS LocationID, Left([tblUpload]![Network],2) AS Div, UCase(""EB - "" & [tblUpload]![Name]) AS Name, [tblUpload]![Type] & "" "" & [tblUpload]![Size] AS Type, tblUpload.Color, tblUpload.Address, tblUpload.City, tblUpload.State, tblUpload.Postcode, IIf([tblUpload]![Country]=""United States"",""USA"",IIf([tblUpload]![Country]=""Canada"",""CAN"",IIf([tblUpload]![Country]=""Aruba"",""ARU"",IIf([tblUpload]![Country]=""Carribean"",""CAR"",IIf([tblUpload]![Country]=""Puerto Rico"",""PR"",""""))))) AS Country, tblUpload.Contact, tblUpload.[Contact Email], tblUpload.[Contact Phone], tblUpload.[Room Count] " & vbCrLf & _
    "FROM tblUpload INNER JOIN tblAbbreviations ON tblUpload.State = tblAbbreviations.Name;"
    Set rs = CurrentDb.OpenRecordset("tblUpdate")
    With rs
      If Not .BOF And Not .EOF Then
      .MoveLast
            .MoveFirst
             While (Not .EOF)
            DoCmd.RunMacro mcrUploadNewLocations
             .MoveNext
            Wend
            End If
          .Close
    End With
    ExitSub:
        Set rs = Nothing
        Exit Sub
    ErrorHandler:
        Resume ExitSub
    End Sub

    Any help would be greatly appreciated.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Things I see:

    "Name", "Type" and "Size" are reserved words in Access and shouldn't be used as object names. Plus they are not very descriptive....

    Shouldn't have/don't need : "vbCrLf" in the string strSQL.
    Shouldn't use spaces or special characters (#) in object names


    "strSQL" is an action query (because of the "INSERT INTO") and doesn't create a recordset. (The other two action query actions are "UPDATE" and "DELETE".)
    So you can't use " With rs"



    If you create a query
    Code:
    SELECT Nz(DMax("LocationID","dbo_tblLocations"))+1 AS LocationID, Left([tblUpload]![Network],2) AS Div, UCase("EB - " & [tblUpload]![Name]) AS Name, [tblUpload]![Type] & "" & [tblUpload]![Size] AS Type, tblUpload.Color, tblUpload.Address, tblUpload.City, tblUpload.State, tblUpload.Postcode, IIf([tblUpload]![Country]="United States", USA", If([tblUpload]![Country]="Canada","CAN", IIf([tblUpload]![Country]="Aruba","ARU",     If([tblUpload]![Country]="Carribean","CAR", IIf([tblUpload]![Country]="Puerto Rico","PR",""))))) AS Country, tblUpload.Contact, tblUpload.[Contact Email], tblUpload.[Contact Phone], tblUpload.[Room Count] FROM tblUpload INNER JOIN tblAbbreviations ON tblUpload.State = tblAbbreviations.Name;"
    and execute it, are records returned?
    (this is the SELECT part of strSQL)

  3. #3
    Kaye960 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    9
    Thank you for your response, it looks like I was totally clueless when doing this. I have been fighting it for a while and got really confused. The select query works and I have changed the names where I was able to. So, I have my recordset created, now, how do I run my append query against the recordset, so that all of the records get appended?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    why are you putting carriage return/line feeds into your SQL statement? That's totally unnecessary
    You are setting up strSQL but you are not executing it anywhere in your code
    What does your macro mcrUploadNewLocations do?

    If this code works for a single record addition, how are you setting the starting and ending points for your location ID?

    Code:
    strSql = "INSERT INTO dbo_tblLocations ( LocationID, Division, LocationName, DisplayType, DisplayColor, Address1, City, State, ZIP, Country, Contact, EmailAddress, [Phone#], Rooms ) " & vbCrLf & _
    "SELECT Nz(DMax(""LocationID"",""dbo_tblLocations""))+1 AS LocationID, Left([tblUpload]![Network],2) AS Div, UCase(""EB - "" & [tblUpload]![Name]) AS Name, [tblUpload]![Type] & "" "" & [tblUpload]![Size] AS Type, tblUpload.Color, tblUpload.Address, tblUpload.City, tblUpload.State, tblUpload.Postcode, IIf([tblUpload]![Country]=""United States"",""USA"",IIf([tblUpload]![Country]=""Canada"",""CAN"",IIf([tblUpload]![Country]=""Aruba"",""ARU"",IIf([tblUpload]![Country]=""Carribean"",""CAR"",IIf([tblUpload]![Country]=""Puerto Rico"",""PR"",""""))))) AS Country, tblUpload.Contact, tblUpload.[Contact Email], tblUpload.[Contact Phone], tblUpload.[Room Count] " & vbCrLf & _
    "FROM tblUpload INNER JOIN tblAbbreviations ON tblUpload.State = tblAbbreviations.Name;"
    Set rs = CurrentDb.OpenRecordset("tblUpdate")
    This query is bulk inserting records into 1 table from another, I don't believe your increment will work this way.

    You'd have to:
    a. Cycle through the source records one by one and append them to the new table one at a time with an incremented locationID
    or
    b. Append all the records first, then update the locationID after the bulk insert. You could do this pretty easily with vba as long as you knew the sort order you wanted.

  5. #5
    Kaye960 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    9
    I have been working on this and here is what I have now
    Code:
    Private Sub Command 18_Click
    Dim strSQL As String
    Dim rs As DAO.Recordset
    strSQL = "tblUpload"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    With rs
     If Not .BOF And Not .EOF Then       
          .MoveLast
            .MoveFirst
    While (Not .EOF)
      ‘This is where I need to cycle through the records and append them to the new table
    .MoveNext
            Wend
             End If
         .Close
    End With
    ExitSub:
        Set rs = Nothing
         Exit Sub
    End Sub
    So I have the recordset, I just need to figure out how to Cycle through the source records one by one and append them to the new table one at a time with an incremented locationID. That is what I was trying to do originally, I was just going about it all wrong.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What are the field names you want to append to and what is the index of the fields in your source recordset?

    you can build your SQL statement the same way you already have

    sSQL = "INSERT INTO <TableName> (Fieldname1, FieldName2, etc...) VALUES (
    ssql = ssql & "'" & rst.fields(fieldindex) & "'," 'for a text field
    ssql = ssql & rst.fields(fieldindex) & "," 'for a number field
    ssql = ssql & "#" & rst.fields(fieldindex) & "#," 'for a date field
    etc.
    ssql = ssql & ")"

    where fieldindex is the index in your recordset, just remember recordset indexes start at 0, not 1 so your first field in your recordset would be rst.fields(0)

    you just need a record counter as you go along after that
    incrementing it with i = i + 1 every time you read a new record and make that part of your append statement.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Something like this:
    --- UNTESTED CODE -----
    Code:
    Private Sub Command18_Click()
        On Error GoTo HandleErr
    
        Dim rs As DAO.Recordset
        Dim strSQL As String
        Dim sCountry As String
        Dim LocID As Long
    
        strSQL = "tblUpload"
    
        'get initial LocID number
        LocID = Nz(DMax("LocationID", "dbo_tblLocations"), 1)
    
        Set rs = CurrentDb.OpenRecordset(strSQL)
        With rs
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                While (Not .EOF)
    
                    'get Country abbreviation
                    sCountry = IIf(![Country] = "United States", "USA", IIf(![Country] = "Canada", "CAN", IIf(![Country] = "Aruba", "ARU", IIf(![Country] = "Carribean", "CAR", IIf(![Country] = "Puerto Rico", "PR", "")))))
    
                    'create SQL Append query
                    strSQL = "INSERT INTO dbo_tblLocations ( LocationID, Division, LocationName, DisplayType, DisplayColor,"
                    strSQL = strSQL & " Address1, City, State, ZIP, Country, Contact, EmailAddress, [Phone#], Rooms )"
                    strSQL = strSQL & " VALUES( " & LocID & ", " & Left(![Network], 2) & ", '" & UCase("EB - " & ![Name]) & "', '"
                    strSQL = strSQL & ![Type] & " " & ![Size] & "', " & !Color & ", '" & !Address & "', '" & !City & "', '"
                    strSQL = strSQL & !State & "', '" & !Postcode & "', '" & sCountry & "', '" & !Contact & "', "
                    strSQL = strSQL & ![Contact Email] & "', " & ![Contact Phone] & "', " & ![Room Count] & ");"
    
                    '     Debug.Print strSQL
    
                    'insert the record
                    CurrentDb.Execute strSQL, dbFailOnError
    
                    'increment LocID
                    LocID = LocID + 1
    
                    'move to next record in recordset
                    .MoveNext
                Wend
            End If
            .Close
        End With
    
    Exit_Here:
        Set rs = Nothing
        Exit Sub
    
    HandleErr:
        MsgBox Err.Number & " " & Err.Description
        Resume Exit_Here
    
    End Sub
    I guessed on the field types when creating the SQL string. Un-comment the debug line and single step through the code. Look at the Immediate Window to see if the SQL string is formed correctly. (the delimiter are correct).
    Re-comment after testing is done.

    Try this on a COPY of your dB.


    Remember:
    "Type" is a reserved word in Access and shouldn't be used as an object name.
    Shouldn't use spaces or special characters in object names. (as in "Phone#")

  8. #8
    Kaye960 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    9
    I fought with this for a couple weeks before I got pulled off on something else. I have a work around for now( I can upload one location at a time). I will revisit it again in the future, but for now I'm going to let it be. Thank you for your assistance.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 12-25-2015, 03:25 PM
  2. Replies: 1
    Last Post: 09-08-2014, 09:47 PM
  3. Appending records to a table
    By crowegreg in forum Programming
    Replies: 6
    Last Post: 08-13-2013, 09:54 AM
  4. Replies: 1
    Last Post: 06-19-2012, 06:12 PM
  5. show duplicate records when appending a table
    By bdaniel in forum Programming
    Replies: 1
    Last Post: 06-01-2011, 07:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums