eeclint
asked on
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.
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.
ASKER
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.
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.
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
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
ASKER
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.tx
avoids a lot of manual steps. The only piece missing is the update of the existing values.
Thanks again for your help.
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.tx
avoids a lot of manual steps. The only piece missing is the update of the existing values.
Thanks again for your help.
Which table is the existing table and which is the table being imported?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
*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.tx
JeffCoachman