Import File Module

[natalie99]

Hello everyone

I have come to the point where I think I need to write a module to finish the last steps of my db. Can anyone please give me their advice / oppinion on the4 easiest way to do this, I have NEVER written a module beofre, I know what they look like, but I do not know the diffference between Private Subs etc etc - help!

My aim is very simple:

Have a form with a browse for file command button, then a command button which will upload the data to an already existing table but it must OVERWRITE the data, NOT append.

I have thus far got this:

DoCmd.TransferSpreadsheet transfertype:=acImport, _
            tablename:="tblBTM", _
            FileName:="P:\Fin\B_SHEET\COMMS\NIMS\Investigation\New MACS\MARCH 08\BT_MACS_MAR07_MAR08.xls", Hasfieldnames:=True, _
            Range:="'BT_MACS_MAR07_MAR08'!", SpreadsheetType:=8

but I don't know what to do with it, and this does not incorporate the browse function :(

pls help :D

nat

[NeoPa]

When you say overwrite the data, do you really mean that ALL the original data should be replaced by the new? As opposed to records with matching keys replacing only those records in the table?

[NeoPa]

The code for clearing the table prior to the import process (add before line #1 of your code) would be fairly straightforward :

Call DoCmd.SetWarnings(False)
Call DoCmd.RunSQL("DELETE FROM [tblBTM]")
Call DoCmd.SetWarnings(True)

[natalie99]

Thanks Neo

Sorry if I wasn't clear enough, I meant that, the table needs to be replaced, completely, which means that, the field names may change, the data will all change, the only items that won't change will be the field names related to my queries.

So, in effect it would be like the Do you wish to replace the query or table 'tblBTM'? prompt box that appears if you import the file and then select new table, then name it the same as the old table.

I am trying to have the format standardized to fix this so I can run your delet code (thanks again for the help) so fingers crossed someone sees my logic!

:)

Nat

[NeoPa]

Nat,

It is possible to run a MakeTable query in Jet (Access) SQL too, but I recommend the clear / add approach over the Delete / MakeTable one if at all possible.