This was a case where I guess I was using this group to 'think aloud'. An alias only works within a query; you have to name the actual table once within that query. What I perhaps failed to make clear in my last post is that I have a chain of dependent queries for the various functions, and I didn't want to modify them all.
The name "...staging" of my main source table is a clue to my previous thinking on this. Different table content could be loaded into this staging table as required.
A 'pure' way of doing this is to empty the contents of the table (DELETE (*) FROM table) and then reload it with a bulk INSERT from the new source.
The cheap-and-dirty approach I took is the second route I described above. As my new source files are of reliably consistent structure, I de-linked the existing one and relinked the new ones using TransferText, as below.
In both cases, it would be a good idea to register the source in some place at the same time as it's loaded into the staging table. I found the Link Manager doesn't always report this (any intelligence here?).
v = ahtCommonFileOpenSave() ' Getz's wrapper for API call
If IsNull(v) Then
MsgBox "Nothing selected - exiting"
sFilePath = v
' Remove any existing link
On Error Resume Next
If Len(CurrentDb.TableDefs(sStagingTableName).Connect) > 0 Then
DoCmd.DeleteObject acTable, sStagingTableName
On Error GoTo 0
Call DoCmd.TransferText(acImportDelim, , sStagingTableName, sFilePath, True)
Posted by: email@example.com
|Reply via web post||•||Reply to sender||•||Reply to group||•||Start a New Topic||•||Messages in this topic (2)|