Import/Overwrite data (1 Viewer)

[Lensmeister]

Hoping someone can help me out here.

I have searched a bit and I have some answers but I need a little clarity if possible.

I have a Access 2003 DB and in it I have a table called tbl_NFM

In the same directory on our network I will bve having various csv files that I wish to import in to the table (tbl_NFM) singleally.

e.g.
1st I will import NFM05a and once it's been viewed I wish to import NFM06 and view it.

What I need help on is
1) I want to be able to allow the user to chose what csv file to open, how should I do that (like a dialogue box?)
2) When I import NFM06 I want it to overwrite all the data in the table (tbl_NFM).
3) If it doesn't overwrite is there a way that before the new csv is imported it deletes all the records in the table (Tbl_NFM)?
4) I will need to automate all this, so I suspect a macro from a command button would be best to open the dialogue, choose the file name and then import it?

Thanks for the help all.

Lensmeister.

 

[DCrake]

first of all you will need a delete query that clears all the records from the target table. Next you need to create a macro or VBA code that uses the transferText or TransferSpreadsheet command. Or you could link the spreahsheets if the names of the spreadsheets never change then simply run an append query from the linked spreadsheet to the target table.

I know you mentioned using the browser to let the user pickk the spreadsheet, and this is fine, however this can be fraught with danger, what happens if the user picks a wrong spreadsheet and the contents do not match the expected data?

If you know the path then you can hard code it into your app.

David

 

[Lensmeister]

first of all you will need a delete query that clears all the records from the target table.

Ok never done one of these before but I have made one now and I am running it.
Edit: The result of the query has kept all the rows but added #Deleted in to every record/cell. I was hoping it would delete ALL the rows.

Next you need to create a macro or VBA code that uses the transferText or TransferSpreadsheet command. Or you could link the spreahsheets if the names of the spreadsheets never change then simply run an append query from the linked spreadsheet to the target table.

Sadly the names will differ depending on when the reports are run from the mainframe.

I know you mentioned using the browser to let the user pickk the spreadsheet, and this is fine, however this can be fraught with danger, what happens if the user picks a wrong spreadsheet and the contents do not match the expected data?

The reports always come out in the same format off the mainframe.

Could I create a form with a dir list box on it?

Or any other ideas on how ?

 

[DCrake]

Find attached an mdb that lists the contents of a folder in list box on the screen. It also has the facility to browse, set naming masks and extension types. If you look at the different sections of the code you should have all you need. You may need to check your tools and references to make sure you have them all registed.

I hope the table that you deleted the records from was safe to do so. It sounds like your tabe needs refeshing.

There is code in the app that deletes a table contents.

David