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"
Exit Sub
Else
sFilePath = v
End If
' Remove any existing link
On Error Resume Next
If Len(CurrentDb.TableDefs(sStagingTableName).Connect) > 0 Then
DoCmd.DeleteObject acTable, sStagingTableName
End If
On Error GoTo 0
Call DoCmd.TransferText(acImportDelim, , sStagingTableName, sFilePath, True)
Posted by: david@powell-au.net
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar