Sabtu, 13 Februari 2016

[MS_AccessPros] Re: Generalising a compare-two-tables tool.


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
    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:
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)



Tidak ada komentar:

Posting Komentar