using DoCmd.TransferDatabase to import data

[ozinm]

Hi all,
Hopefully this is just a quickie but I'll paste a bit of text form the MS help to illustrate (mainly because I'm too tired to think straight today!).

MS Access 2002 help for the TransferDatabase Action states:

If you select Import in the Transfer Type argument and Table in the Object Type argument, Access creates a new table containing the data in the imported table.

If you import a table or other object, Access adds a number to the name if it conflicts with an existing name. For example, if you import Employees and Employees already exists, Access renames the imported table or other object Employees1.

If you export to an Access database or another database, Access automatically replaces any existing table or other object that has the same name.

Is there a way to use this command (or could you suggest another one) that instead of creating an Employees1 table, it would actually import the data into the existing table?


As always any help, davice, spelling corrections gratefully received.

Marc

 

[workmad3]

Write a VBA routine to do the transfer for you maybe? Its what I ended up having to do do get data into my database.

 

[ozinm]

yup,
I pretty much thought that's what I'd have to do.
something like:
1. Import table to TEMP table
2. Run an UPDATE query to import the data from TEMP to my exisiting table
3. Delete TEMP table

Oh well thought I'd check with you lot first in case there was something I was missing.

Cheers anyhow.

All the best

Marc

 

[workmad3]

what format are you importing from? If its anything that can be accessed from an ADODB connection, then you can remove the temp table entirely.

And you would(I expect) want INSERT statements rather than update, unless you want to write some function that decides which is needed and uses it.

 

[ozinm]

what format are you importing from? If its anything that can be accessed from an ADODB connection, then you can remove the temp table entirely.

And you would(I expect) want INSERT statements rather than update, unless you want to write some function that decides which is needed and uses it.

/me stares blery eyed at my posting.
Whoops - I meant insert!

Thanks

Marc

 

[Charles Carter]

Re:

I use this command. I'm not sure if it's what you're looking for.

DoCmd.TransferText acImportDelim, "MerTemp Import Specification", "MerDBImport", "C:\MerTemp.txt", True, ""

First I create the Table MerDBImport with matching fields to the imported data, then use the DoCmd to import a .txt file into Table MerDBImport using the MerTemp Import Specification.

Charles

 

[workmad3]

yeah, thats what I was wondering. I suppose you could use file handlers to get the info straight from the text file, but in this case, do a transfertext to get it in, then transfer the info from one table to the other using CurrentDB.Execute("INSERT") statements.

Was wondering if the data being imported was from another db that you could connect to using ADODB and save the trouble of the temp table.