TransferDatabase, Append question

[Lobster1071]

I'm trying to find an elegant solution to an import issue using the TransferDatabase command (Access 2007). I haven't found a good solution yet, and have read a few posts, including a couple from this site related to this issue. If someone could point me to a link with a good solution, I would appreciate it.

Here is the issue...I have a project with a "table free" frontend main program, with linked tables to the backend containing the actual tables. There is a button that the user presses to import various DBF file(s). When the TransferDatabase command is run, it has no append option that I am aware of, so a table is created each time this happens. Yes, I have code to delete the existing "temp" table so that a new one can be created with the desired name, but the table is created in the frontend, and not the backend,which is where I would like it to be if no append option exists.

This is not a major issue, I'm just trying to find a way to make it "cleaner" if possible. This means the frontend database, over time, will continue to grow due to this temp import file being created and deleted for each import, and it will need to be compacted. I already even created two automatic routines to compact and repair the backend AND the frontend databases if each exceeds a certain file size.

I'm not going to bore everyone with all the code, but I have a function to check if the import table "TempX" exists, then delete it if it does, this way I don't get TempX1, TempX2, etc because the TransferDatabase command doesn't overwrite the table if it exists, but creates a new one with a number at the end

If TableExists("TempX") Then
DoCmd.DeleteObject acTable, "TempX"
End If
DoCmd.TransferDatabase acImport, "dBase IV", GrabDir(.SelectedItems.Item(1)), acTable, GrabFile(.SelectedItems.Item(1)), "TempX", False

Everything works fine, except as I've said, the TempX table is created in the frontend database. A simple append option in the TransferDatabase would solve everything, because I can then append the imported data into a linked table in the backend, but don't see how it can be done. I just would love it if there were a way to import a DBF file into a program without actually having to create a new table every time.


Any good solutions out there?



Thanks

 

[gemma-the-husky]

one alternative is to have a table of the correct format in the backend ready to receive the data, as a normal linked table in the front end. Then you can temporarily import to the front end, and append to the (linked) table in the backend.

I assume you do not need to keep inventing new table layout/designs in the backend? You certainly shouldn't have to do that.

 

[spikepl]

You can select from an external database:

[SELECT | INSERT] INTO destination IN {path | ["path" "type"] | ["" [type; DATABASE = path]]}

https://msdn.microsoft.com/en-us/library/bb177907(v=office.12).aspx

 

[Lobster1071]

Thanks to both of you for your replies.

Gemma/Dave, I still didn't see how that would help, unless I'm missing something. Yes, the structure of all the DBFs are the same. However, I'd still be creating a temporary import file in the front end, which is what I'm trying to avoid.

Spike, I think your solution worked perfectly. After a lot of little testing getting the formatting and syntax correct, which wasn't working out, I found this link which finalized it for me: http://www.access-programmers.co.uk/forums/showthread.php?t=80854
I never would have even known to search for it if you didn't point out the "IN" clause.

This final little code did exactly what I needed:
Dim strSQL As String
strSQL = "INSERT INTO TempX SELECT * FROM DBFfile IN ""C:\FOX\"" ""dBASE IV;"""
DoCmd.RunSQL (strSQL)

It imports perfectly into a linked table within the front end.
Again, thanks so much.

 

[Galaxiom]

I'm not a fan of writing temporary data into an application (which is what the front end effectively is).

Separate throw away databases dedicated to the task and linked to the front end make more sense to me.

I call them Side Ends.

 

[Lobster1071]

That's an OK idea too, but isn't what I'm doing not really writing the data into the front end? I am importing the data into a table linked to the back end. Then over time, that back end gets compacted to keep the wasted space under control. If I had a side end as you suggested, wouldn't I have to do the same thing and compact that over time? Or are you just deleting the entire side end database after the import?

 

[Galaxiom]

Writing to the back end means the same temporary file is available to multiple users. Can be a problem if you have multiple users importing.

It also means you have to compact the back end more often. I tend to avoid writing and deleting data from the BE where I can. All adds opportunities for something to go wrong.

The side end can be discarded or compacted.

 

[Lobster1071]

Hmm, that's interesting. I'll have to think about SE databases, and maybe how to create and then delete them on the fly. Thank you for the good suggestion.

 

[Galaxiom]

The easiest way is to have an empty one stored somewhere handy and copy it.