Automate Import of Data & Update Existing Data in Access Table

Hi All,

InventoryTable
sku  (text)
price (number)
quantity (number)
map  (Yes/No)
map-price (number


sku              price      quantity      weight
A001           10               2            1
A002           5                 1              1
A003           3                 3            11

I have the following table that gets new data in an tab delimited text file everyday. I am looking to import new data & append existing data into this Inventory table daily.

What steps would I have to take to import new data & update existing data? Once that is done, I'd like to setup a daily job to perform the import into the Inventory table.

Thank you for your suggestion & pointers.
eeclintAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
This works for me.

You will have to: examine, study and test it thoroughly, then adapt it to work in your database.

JeffCoachman
EEQ--Access-27832204UpdateExisti.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Import the file once, saving the settings as an "Import Specification"
*NOTE THIS IS NOT THE SAME THING AS CLICKING THE: "Save Import Steps" button!
;-)

Then use code like this to import/append the data:

DoCmd.TransferText acImportDelim, "YourImportSpecName", "YourTableName", "C:\YourFolder\YourFile.txt"

JeffCoachman
0
 
eeclintAuthor Commented:
Sorry for the delay in acknowledging your response.

The command worked albeit partially. It imported the data, but only appended it.

EG. I had 1000 records to start
After the command, I had 2000 records

I would it to append only new records & update the existing ones.

Is that possible?

Thanks for the command as it partially does what I do manually.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jeffrey CoachmanMIS LiasonCommented:
I'm confused...

The field list that you posted:
InventoryTable
sku  (text)
price (number)
quantity (number)
map  (Yes/No)
map-price (number

... does not match the data you posted.
sku              price      quantity      weight
A001           10               2            1
A002           5                 1              1
A003           3                 3            11
0
 
eeclintAuthor Commented:
Sorry for the confusion.

InventoryTable
sku  (text)
price (number)
quantity (number)
map  (Yes/No)
map-price (number

The table happens to be correct. The data feed only contains

sku              
price      
quantity      
weight

I am looking to import new data into those fields & update the existing records.

Running the command:
DoCmd.TransferText acImportDelim, "YourImportSpecName", "YourTableName", "C:\YourFolder\YourFile.txt"

avoids a lot of manual steps. The only piece missing is the update of the existing values.

Thanks again for your help.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Which table is the existing table and which is the table being imported?
0
 
eeclintAuthor Commented:
InventoryTable is the existing table.

The text file data feed is datafeed.txt or in this example "YourFile.txt"

The data in text format in the text file datafeed.txt/YourFile.txt is imported into the existing table InventoryTable.

Thanks
0
 
eeclintAuthor Commented:
Dear JeffCoachman,

I took a quick look at the code & it looks great. I accept the solution. I'll make sure to examine, study and test it thoroughly before incorporating your solution.

Thank you for taking the time to write the sample code & tables.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.