import from Excel to Accestable 1

[paswoord]

I have an Accesstable called "download receipts" and want to update this daily with data from an Excelfile, called "new_receipts.xls".
The path to the Excelfile is C:\data\admin\customs
I want to import new records from the Excelfile to the Accesstable, leaving out duplicate records. The unique key of the Accesstable is "do_inslnr".

The importing should be run by clicking a button in a form called "update".

Can someone help me making a module for this and give info on the commandlines the button should have?

Thanks

 

[GingerR]

in your button OnClick event, start writing code for

docmd.transferspreadsheet

and follow the prompts to fill it in.

if first you want to delete the current data, i'd suggest

currentdb.execute "Delete Table.* from Table;"

something else to consider: based on my experience, i'd import the excel data into a temp table first, then use an append query to put the new data into the 'real' table. sometimes there are funky things that happen with importing from a diff file format.

another option is to LINK (FILE-GET EXTERNAL DATA-LINK) to the excel file and just use an append query to add those records to your table.

 

[paswoord]

Hi GingerR,

Thanks for your reply so far.
I must admit that, as it comes to writing code, I know hardly anything. Can you help me?

Based on the info I provided earlier, what should be the code I write for the button? You are talking about filling in the prompts. What are these and where do I fill those in?

Can you help me out?

Bye
Michel

 

[GingerR]

sure.
This is the gist. I'm taking another route:
You can link to the Excel table. Then using an append query, you can add the records you want to your access table. then we can automate this in a button.

first, if you havent done much programming that has to do with your table, i suggest renaming it to DownloadReceipts, with no space. i suggest not putting spaces in any object names (tables, queries, forms, etc, or fields) because later on it could mess you up, or in the least take more programming time by having to bracket ([]) everything.

in any case....
1) LINK to the EXCEL table: choose menu FILE+GET EXTERNAL DATA; LINK; at the bottom of the BROWSE screen, choose file type EXCEL; browse to your excel file and select it.

2) WRITE AN APPEND QUERY: do you know how to do this? write a query based on your excel table; make it an append query to append the records to your access table. Test the query and make sure it works how you want it to. Since you have a unique key already specified in your access table, it will only append records that aren't already in there. At first the query will say it's going to append say 1000 records, and you know that's wrong. but once you hit OK, it says 'oh but i cant append 975 of them because they violate a primary key' or something like that.

3) MAKE THE BUTTON: on your form, create a new button (look in the TOOLBOX, choose the button, and draw one on your form).

4) when the wizard comes up, choose MISCELLANEOUS, then choose RUN QUERY. Choose the append query you just made. finish going thru the wizard. Access writes the code for you for these simple types of things. you can learn more advanced stuff later.

5) test it: look at the form view of the form. click the button. does it work?

6) to get rid of the pop-up messages where you have to hit OK OK OK to make the query work, do this:
in the design view of the FORM, right click on the button and choose PROPERTIES.
scroll down til you see On CLick. there should be at thing that says [Event Procedure] in there. the wizard did that. click once in there. over to the right is a little button with three dots on it. click that button. it opens to the code that access wrote for you. in order to get rid of those pop-up messages, put in these lines:

Private Sub btnAppendData_Click()

docmd.setwarnings FALSE

.
.
.

docmd.setwarnings TRUE

End Sub

ok so try that out.
g