473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Import, Append/Update question.

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
13 2018
JConsulting
603 Expert 512MB
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
603 Expert 512MB
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
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
12,516 Expert Mod 8TB
Perhaps a delete query followed by an append query?
May 10 '07 #5
JConsulting
603 Expert 512MB
Perhaps a delete query followed by an append query?
Good point too Rabbit. Simpler probably.
J
May 10 '07 #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
603 Expert 512MB
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
603 Expert 512MB
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
>>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
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
603 Expert 512MB
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
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
603 Expert 512MB
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

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

Similar topics

3
by: Doug Baroter | last post by:
Hi, One of my clients has the following situation. They use Access DB for data update etc. some business functions while they also want to view the Access data quickly and more efficiently in...
2
by: JMCN | last post by:
hi i need some advice on whether if it would be better to use an append query or an update query. here is the situation, i have linked another database table to my current database. then i...
1
by: Aaron | last post by:
Hello fellow programmers, I am trying to run an append/update query from code, a command button on a form initiates the queries. the format i am using is; ...
5
by: strauss.sean | last post by:
Hi! I am trying to import a query as a table from a MS Access database in a specified drive, path, and filename; my filenames and paths are being stored in a table for easy reconfiguration....
10
by: shumaker | last post by:
I don't need a detailed description of a solution(although I wouldn't mind), but I am hoping someone could tell me in general the best path to go about accomplishing a task, since I don't know all...
1
by: socasteel21 via AccessMonster.com | last post by:
I have a spreadsheet that has 3 tabs each of the worksheets is setup exactly like a cooresponding table in Access. I created a button that should import each tab to a new table and then append...
11
by: kaisersose1995 | last post by:
Hi, I've got an import procedure working, using a standard import specification to import a .csv file into a temporary table. The problem i'm having is that i have 4 different sets of borrower...
4
by: bvdp | last post by:
Terry Reedy wrote: <snip> <snip> <snip>
8
by: tow | last post by:
I have a python script (part of a django application, if it makes any difference) which is exhibiting the following behaviour: import my_module # succeeds imp.find_module("my_module") # fails,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.