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
13 2018
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.
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
-
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.
Perhaps a delete query followed by an append query?
Perhaps a delete query followed by an append query?
Good point too Rabbit. Simpler probably.
J
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
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 : -
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
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
>>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.
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
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
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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;
...
|
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....
|
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...
|
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...
|
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...
|
by: bvdp |
last post by:
Terry Reedy wrote:
<snip>
<snip>
<snip>
|
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,...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| | |