Import and Overwrite of Excel files

[creamichoc]

Hi,

I've got an Access table that I regularly want updated/replaced using a NEW (i.e. different file name but same path) excel file. So effectively I want to be able to select the new file I want, import it (with fixed criteria i.e 1st row contains headers, no primary key etc) & give it the name of the previous table (to overwrite).

Currently I use the Import Spreadsheet Wizard to achieve this, click through each step & then rename at the end. Any idea how I could improve this process for the end users?

Thanks in advance

 

[DCrake]

Create a macro to do the same thing
Convert the macro to vba
Place the vba behind a command button on a form
Have a text box on the form for the user to enter the name
Run the code

David

 

[Atomic Shrimp]

Rather than import and rename, I would:

-Empty the previously-imported table (run a delete query)
-Import the spreadsheet, appending it into the existing (now empty) table

 

[creamichoc]

Thanks for the replies guys. Unfortunately, once the data is in the table I perform a number of queries on it (add columns etc) so the incoming table will not resemble the current table. Additionally the append query would need me to specify the table name of the newly imported file which if kept from excel would vary.

I'll try this macro soultion but have struggled to use this in the past - am much more comfortable in VBA/SQL!

Thanks again!

 

[DCrake]

What are you doing to the table once you have imported the data from Excel? If you are performing calculations these can be done in queries without the need of adding new columns as you say.

If you are consistant with the new columns you are adding then try the earier suggestion and make the amended table as a template and import the data into the appropriate fields.

David

 

[creamichoc]

Queries include adding a field with a constant (user defines), datajoins to other tables etc.
I accept that this may not be the ideal solution, however rather than reengineer the entire db I was hoping to be able to be able to write some VBA to replicate the Import Wizard process (since criteria entered here is constant). I guess even by taking the import to template route I would have the same issues with importing.

 

[DCrake]

Lets work backwards. If we asume we want to run another import. Last time we imported we made the table added the field updated that field with a constant, etc, etc.

Now this time on your form get the user to enter the value for the constant along with the name of the file

Delete the records from previously created and amended table

Next import the records from the excel file into this table

run an update query to replace the previous constant with the new constant, if different. And you should now be where you were the previous month but with a new set of data.

Don't forget I do not the exact methology of your business and this is only an assumption.

David

 

[Atomic Shrimp]

Thanks for the replies guys. Unfortunately, once the data is in the table I perform a number of queries on it (add columns etc) so the incoming table will not resemble the current table. Additionally the append query would need me to specify the table name of the newly imported file which if kept from excel would vary.

Those seem more like reasons to do it the way I suggested.

Set your queries up to work on a table with known name and structure
Set your import up to get the data into that table.

If you need to just add new records to your table, rather than completely replacing it, then you just need another intermediate table that you can clear down and import-append into each time, before appending the new ones into the main table.

 

[creamichoc]

Those seem more like reasons to do it the way I suggested.

Set your queries up to work on a table with known name and structure
Set your import up to get the data into that table.

If you need to just add new records to your table, rather than completely replacing it, then you just need another intermediate table that you can clear down and import-append into each time, before appending the new ones into the main table.

Ok I see your point but that's just a better way of what I've got. First I think it's important to put in place what I don't have! I.e. not being able to import the data to the table!