• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

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.
0
eeclint
Asked:
eeclint
  • 4
  • 4
1 Solution
 
Jeffrey CoachmanCommented:
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
 
Jeffrey CoachmanCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 CoachmanCommented:
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
 
Jeffrey CoachmanCommented:
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
 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now