Import, Append/Update question.

[mpslanker]

This is my first time posting here, so if I have made any mistakes (i.e. wrong forum area, etc) I am sorry.

I have an Access database that I have to import a text file into and append it to a certain table. I know this part of my question has been answered and I understand how to do it quite well after searching long and hard for an answer to my next part. I can either import the text file directly into the table or into it's own seperate table to combine the two later. My problem however is that, I can't just append the new data to the existing table I have to be able to update any of the records in the existing table if the new data has a record that contains an Order ID already in the existing table in order to avoid duplicates. I cannot just not enter the new data since its record may contain updated information.
Hope all of that made sense. Thanks to everyone in advance.

Thank you,

Matthew

[JConsulting]

This is my first time posting here, so if I have made any mistakes (i.e. wrong forum area, etc) I am sorry.

I have an Access database that I have to import a text file into and append it to a certain table. I know this part of my question has been answered and I understand how to do it quite well after searching long and hard for an answer to my next part. I can either import the text file directly into the table or into it's own seperate table to combine the two later. My problem however is that, I can't just append the new data to the existing table I have to be able to update any of the records in the existing table if the new data has a record that contains an Order ID already in the existing table in order to avoid duplicates. I cannot just not enter the new data since its record may contain updated information.
Hope all of that made sense. Thanks to everyone in advance.

Thank you,

Matthew

guess what...you get to learn to code now! Aren't you excited?

Here's the gist of it.

You have a table full of imported records.
Somewhere in your table you have an Order ID field.

You need to do a lookup using that id for each record you want to process.

If the Order ID is found, then you run an update query

if the Order ID is not found, you do an insert.

I know...DUH. But the method I'll give you in a second actually does that.

You'll need to plug in your table names and hopefully the field names match.

[JConsulting]

This is my first time posting here, so if I have made any mistakes (i.e. wrong forum area, etc) I am sorry.

I have an Access database that I have to import a text file into and append it to a certain table. I know this part of my question has been answered and I understand how to do it quite well after searching long and hard for an answer to my next part. I can either import the text file directly into the table or into it's own seperate table to combine the two later. My problem however is that, I can't just append the new data to the existing table I have to be able to update any of the records in the existing table if the new data has a record that contains an Order ID already in the existing table in order to avoid duplicates. I cannot just not enter the new data since its record may contain updated information.
Hope all of that made sense. Thanks to everyone in advance.

Thank you,

Matthew

Ready to code? This is commented, so hopefully will make sense.
This code goes either on a button...or in a Module where you can call it from a macro or something.

Function Import()
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strSQL As String
Dim strSQL1 As String
Dim sID As Long
Dim I As Long
Dim J As Long
' Open your import table
strSQL = "SELECT * from ImportTable;"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        sID = rs![Order ID]
            strSQL1 = "SELECT * from TargetTable where [Order ID]=" & sID & ";"
        ' Open your Target Table
        Set rs1 = CurrentDb.OpenRecordset(strSQL1)
        If rs1.EOF Then 'Didn't find any matching IDs
            rs1.Addnew
            For I = 1 To rs.Fields.count - 1 'skips the first column as it's usually the PK
                For J = 1 To rs1.Fields.count - 1 'skips the first column as it's usually the PK
                    If rs1.Fields(J).Name = rs.Fields(I).Name Then ' If the field names match..insert the value
                        rs1.Fields(J) = rs.Fields(I)
                    End If
                Next J
            Next I
            rs1.Update
        Else
            rs1.Edit
            For I = 1 To rs.Fields.count - 1  'skips the first column as it's usually the PK
                For J = J To rs1.Fields.count - 1 'skips the first column as it's usually the PK
                    If rs1.Fields(J).Name = rs.Fields(I).Name Then ' if the field names match...update the value
                        rs1.Fields(J) = rs.Fields(I)
                    End If
                Next J
            Next I
            rs1.Update
        End If
    rs1.Close
    Set rs1 = Nothing
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Function
 

[mpslanker]

Ready to code? This is commented, so hopefully will make sense.
This code goes either on a button...or in a Module where you can call it from a macro or something.

Function Import()
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strSQL As String
Dim strSQL1 As String
Dim sID As Long
Dim I As Long
Dim J As Long
' Open your import table
strSQL = "SELECT * from ImportTable;"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        sID = rs![Order ID]
            strSQL1 = "SELECT * from TargetTable where [Order ID]=" & sID & ";"
        ' Open your Target Table
        Set rs1 = CurrentDb.OpenRecordset(strSQL1)
        If rs1.EOF Then 'Didn't find any matching IDs
            rs1.Addnew
            For I = 1 To rs.Fields.count - 1 'skips the first column as it's usually the PK
                For J = 1 To rs1.Fields.count - 1 'skips the first column as it's usually the PK
                    If rs1.Fields(J).Name = rs.Fields(I).Name Then ' If the field names match..insert the value
                        rs1.Fields(J) = rs.Fields(I)
                    End If
                Next J
            Next I
            rs1.Update
        Else
            rs1.Edit
            For I = 1 To rs.Fields.count - 1  'skips the first column as it's usually the PK
                For J = J To rs1.Fields.count - 1 'skips the first column as it's usually the PK
                    If rs1.Fields(J).Name = rs.Fields(I).Name Then ' if the field names match...update the value
                        rs1.Fields(J) = rs.Fields(I)
                    End If
                Next J
            Next I
            rs1.Update
        End If
    rs1.Close
    Set rs1 = Nothing
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Function
 

Wow, that was an incredibly quick response, and so far it makes perfect sense. I know a little bit about using VBA so I should be able to stumble through it, until I figure how out it all fits together. Thank you so much. I really appreciate it. I will get to work on getting that in my database and let you know how it goes. Thanks again.

Sincerely,

Matthew.

[Rabbit]

Perhaps a delete query followed by an append query?

[JConsulting]

Perhaps a delete query followed by an append query?

Good point too Rabbit. Simpler probably.
J

[mpslanker]

Good point too Rabbit. Simpler probably.
J

I may wind up having to go that route, but I wanna try the other way first. (I love learning new things)

I had to go in and make sure when the text file imported pulled the field names in too, and that they had the same name as the table the data is being appended to. After I got all that taken care of and the above function modified with the appropriate table/column names, I now recieve a runtime error. Error #3464: "Data type mis-matched in criteria expresssion" at this line of code -

Set rs1 = CurrentDb.OpenR ecordset(strSQL 1)

I think it has someting to do with the fact that the sID is dimmed as a Long, but the Order_ID field in the database is text in both tables. I tried dimming sID as a string, unfortunately still recieve the same error.

Should I change the field in the database to number instead of text?
Thank you all again for all your help.

Sincerely,
Matthew

[JConsulting]

I may wind up having to go that route, but I wanna try the other way first. (I love learning new things)

I had to go in and make sure when the text file imported pulled the field names in too, and that they had the same name as the table the data is being appended to. After I got all that taken care of and the above function modified with the appropriate table/column names, I now recieve a runtime error. Error #3464: "Data type mis-matched in criteria expresssion" at this line of code -

Set rs1 = CurrentDb.OpenR ecordset(strSQL 1)

I think it has someting to do with the fact that the sID is dimmed as a Long, but the Order_ID field in the database is text in both tables. I tried dimming sID as a string, unfortunately still recieve the same error.

Should I change the field in the database to number instead of text?
Thank you all again for all your help.

Sincerely,
Matthew

actually the error is one line above where you actually define the strSQL1. In this case :

strSQL1 = "SELECT * from TargetTable where [Order ID]=" & sID & ";"
 

Chances are your order ID field is text..the code assumes numeric.
so that line changes to this

strSQL1 = "SELECT * from TargetTable where [Order ID]='" & sID & "';"
 

text variables have to be surrounded with ' ' single ticks.

Let us know how it's going.
J

[JConsulting]

I may wind up having to go that route, but I wanna try the other way first. (I love learning new things)

I had to go in and make sure when the text file imported pulled the field names in too, and that they had the same name as the table the data is being appended to. After I got all that taken care of and the above function modified with the appropriate table/column names, I now recieve a runtime error. Error #3464: "Data type mis-matched in criteria expresssion" at this line of code -

Set rs1 = CurrentDb.OpenR ecordset(strSQL 1)

I think it has someting to do with the fact that the sID is dimmed as a Long, but the Order_ID field in the database is text in both tables. I tried dimming sID as a string, unfortunately still recieve the same error.

Should I change the field in the database to number instead of text?
Thank you all again for all your help.

Sincerely,
Matthew

>>I think it has someting to do with the fact that the sID is dimmed as a Long, but the Order_ID field in the database is text in both tables. I tried dimming sID as a string, unfortunately still recieve the same error.

YES. Sorry, I missed that in my first parousal.
Change

Dim sID as Long

to

Dim sID as string

should do it.
J

[mpslanker]

>>I think it has someting to do with the fact that the sID is dimmed as a Long, but the Order_ID field in the database is text in both tables. I tried dimming sID as a string, unfortunately still recieve the same error.

YES. Sorry, I missed that in my first parousal.
Change

Dim sID as Long

to

Dim sID as string

should do it.
J

So the single tick marks made the difference, and it is coming along a lot faster than I expected it to. Everything is starting to make sense. I did run into a new error that I don't understand. The program breaks at:

If rs1.Fields(J).N ame = rs.Fields(I).Na me Then ' if the field names match...update the value

Throwing another error:

Run-time error: '3265'
Item not found in this collection.

I don't understand where the collection is for rs1 is. When the program is paused and you point to that line of code it will show in the tool tip:
rs1.Field(24).N ame = <Item not found in this collection> while the other part shows rs.Field(1).Nam e = "Order_ID" All of this comes from the ELSE part of the first IF statement.

No clue where to begin on this one.

Thank you.