By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

Import File Module

P: 41
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet transfertype:=acImport, _
  2.             tablename:="tblBTM", _
  3.             FileName:="P:\Fin\B_SHEET\COMMS\NIMS\Investigation\New MACS\MARCH 08\BT_MACS_MAR07_MAR08.xls", Hasfieldnames:=True, _
  4.             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

Apr 11 '08 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 15k+
P: 31,495
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?
Apr 11 '08 #2

Expert Mod 15k+
P: 31,495
The code for clearing the table prior to the import process (add before line #1 of your code) would be fairly straightforward :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False)
  2. Call DoCmd.RunSQL("DELETE FROM [tblBTM]")
  3. Call DoCmd.SetWarnings(True)
Apr 11 '08 #3

P: 41
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!


Apr 16 '08 #4

Expert Mod 15k+
P: 31,495

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.
Apr 16 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.