By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Import, Append/Update question.

P: 6
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
May 10 '07 #1
Share this Question
Share on Google+
13 Replies


JConsulting
Expert 100+
P: 603
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.
May 10 '07 #2

JConsulting
Expert 100+
P: 603
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.

Expand|Select|Wrap|Line Numbers
  1. Function Import()
  2. Dim rs As DAO.Recordset
  3. Dim rs1 As DAO.Recordset
  4. Dim strSQL As String
  5. Dim strSQL1 As String
  6. Dim sID As Long
  7. Dim I As Long
  8. Dim J As Long
  9. ' Open your import table
  10. strSQL = "SELECT * from ImportTable;"
  11.     Set rs = CurrentDb.OpenRecordset(strSQL)
  12.     rs.MoveFirst
  13.     Do Until rs.EOF
  14.         sID = rs![Order ID]
  15.             strSQL1 = "SELECT * from TargetTable where [Order ID]=" & sID & ";"
  16.         ' Open your Target Table
  17.         Set rs1 = CurrentDb.OpenRecordset(strSQL1)
  18.         If rs1.EOF Then 'Didn't find any matching IDs
  19.             rs1.Addnew
  20.             For I = 1 To rs.Fields.count - 1 'skips the first column as it's usually the PK
  21.                 For J = 1 To rs1.Fields.count - 1 'skips the first column as it's usually the PK
  22.                     If rs1.Fields(J).Name = rs.Fields(I).Name Then ' If the field names match..insert the value
  23.                         rs1.Fields(J) = rs.Fields(I)
  24.                     End If
  25.                 Next J
  26.             Next I
  27.             rs1.Update
  28.         Else
  29.             rs1.Edit
  30.             For I = 1 To rs.Fields.count - 1  'skips the first column as it's usually the PK
  31.                 For J = J To rs1.Fields.count - 1 'skips the first column as it's usually the PK
  32.                     If rs1.Fields(J).Name = rs.Fields(I).Name Then ' if the field names match...update the value
  33.                         rs1.Fields(J) = rs.Fields(I)
  34.                     End If
  35.                 Next J
  36.             Next I
  37.             rs1.Update
  38.         End If
  39.     rs1.Close
  40.     Set rs1 = Nothing
  41.     rs.MoveNext
  42.     Loop
  43.     rs.Close
  44.     Set rs = Nothing
  45. End Function
  46.  
May 10 '07 #3

P: 6
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.

Expand|Select|Wrap|Line Numbers
  1. Function Import()
  2. Dim rs As DAO.Recordset
  3. Dim rs1 As DAO.Recordset
  4. Dim strSQL As String
  5. Dim strSQL1 As String
  6. Dim sID As Long
  7. Dim I As Long
  8. Dim J As Long
  9. ' Open your import table
  10. strSQL = "SELECT * from ImportTable;"
  11.     Set rs = CurrentDb.OpenRecordset(strSQL)
  12.     rs.MoveFirst
  13.     Do Until rs.EOF
  14.         sID = rs![Order ID]
  15.             strSQL1 = "SELECT * from TargetTable where [Order ID]=" & sID & ";"
  16.         ' Open your Target Table
  17.         Set rs1 = CurrentDb.OpenRecordset(strSQL1)
  18.         If rs1.EOF Then 'Didn't find any matching IDs
  19.             rs1.Addnew
  20.             For I = 1 To rs.Fields.count - 1 'skips the first column as it's usually the PK
  21.                 For J = 1 To rs1.Fields.count - 1 'skips the first column as it's usually the PK
  22.                     If rs1.Fields(J).Name = rs.Fields(I).Name Then ' If the field names match..insert the value
  23.                         rs1.Fields(J) = rs.Fields(I)
  24.                     End If
  25.                 Next J
  26.             Next I
  27.             rs1.Update
  28.         Else
  29.             rs1.Edit
  30.             For I = 1 To rs.Fields.count - 1  'skips the first column as it's usually the PK
  31.                 For J = J To rs1.Fields.count - 1 'skips the first column as it's usually the PK
  32.                     If rs1.Fields(J).Name = rs.Fields(I).Name Then ' if the field names match...update the value
  33.                         rs1.Fields(J) = rs.Fields(I)
  34.                     End If
  35.                 Next J
  36.             Next I
  37.             rs1.Update
  38.         End If
  39.     rs1.Close
  40.     Set rs1 = Nothing
  41.     rs.MoveNext
  42.     Loop
  43.     rs.Close
  44.     Set rs = Nothing
  45. End Function
  46.  
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.
May 10 '07 #4

Rabbit
Expert Mod 10K+
P: 12,359
Perhaps a delete query followed by an append query?
May 10 '07 #5

JConsulting
Expert 100+
P: 603
Perhaps a delete query followed by an append query?
Good point too Rabbit. Simpler probably.
J
May 10 '07 #6

P: 6
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.OpenRecordset(strSQL1)
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
May 10 '07 #7

JConsulting
Expert 100+
P: 603
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.OpenRecordset(strSQL1)
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL1 = "SELECT * from TargetTable where [Order ID]=" & sID & ";"
  2.  
Chances are your order ID field is text..the code assumes numeric.
so that line changes to this

Expand|Select|Wrap|Line Numbers
  1. strSQL1 = "SELECT * from TargetTable where [Order ID]='" & sID & "';"
  2.  
text variables have to be surrounded with ' ' single ticks.

Let us know how it's going.
J
May 10 '07 #8

JConsulting
Expert 100+
P: 603
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.OpenRecordset(strSQL1)
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
May 10 '07 #9

P: 6
>>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).Name = rs.Fields(I).Name 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).Name = <Item not found in this collection> while the other part shows rs.Field(1).Name = "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.
May 10 '07 #10

P: 6
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).Name = rs.Fields(I).Name 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).Name = <Item not found in this collection> while the other part shows rs.Field(1).Name = "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.
Nevermind, I caused there error I messed up the line before it.

So far it ran all the way through with no problems, and I double checked the data. IT'S PERFECT. I don't know what I would have done without your help. Thank you so much again.

Sincerely,

Matthew
May 10 '07 #11

JConsulting
Expert 100+
P: 603
Nevermind, I caused there error I messed up the line before it.

So far it ran all the way through with no problems, and I double checked the data. IT'S PERFECT. I don't know what I would have done without your help. Thank you so much again.

Sincerely,

Matthew
Hi Matthew,
Sounds like you're enjoying VBA. I'm ok at queries..and can write them backward...But coding is a lot more fun.
Happy to share.
J
May 10 '07 #12

P: 6
Hi Matthew,
Sounds like you're enjoying VBA. I'm ok at queries..and can write them backward...But coding is a lot more fun.
Happy to share.
J
Thanks again J, you don't know how much I appreciate it. I beginning to think I wasn't going to find an answer that I could even understand.

So far I have enjoyed the VBA experience I have recieved, I am really into programming but I am still pretty new at it. I am only 21 (in 6 days anyway) and I was recommended for this new position. They found out I was pretty capable on the computer and have been pushing me to see what all I can do. This was just a small part of the automation project they have me working on and it was really stumping me. The rest of the project has been coming along pretty well.
Well thank you again for you help.

BTW how's the weather in Houston, it is starting to get pretty nasty here in Arlington.

Matthew
May 10 '07 #13

JConsulting
Expert 100+
P: 603
Thanks again J, you don't know how much I appreciate it. I beginning to think I wasn't going to find an answer that I could even understand.

So far I have enjoyed the VBA experience I have recieved, I am really into programming but I am still pretty new at it. I am only 21 (in 6 days anyway) and I was recommended for this new position. They found out I was pretty capable on the computer and have been pushing me to see what all I can do. This was just a small part of the automation project they have me working on and it was really stumping me. The rest of the project has been coming along pretty well.
Well thank you again for you help.

BTW how's the weather in Houston, it is starting to get pretty nasty here in Arlington.

Matthew
It's nice to help someone that "gets it", and learns quickly. I've been doing this kind of work for a while...I was always an operator or an analyst...I found my niche when I found programming. I always thought it would be boring...well, if it is, then I guess I am! :)

Houston is nice. Wish I had more time to be outside.

Good luck with your project..I think you'll do fine.
J
May 11 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.