Tables Changing From Linked To Local At Runtime

Hi Guys,

This is one of this only times i've had a problem whose answer i couldn't find on this or other forums, so here's the question.

My DBase links to the back end tables
tblBrkdn
tblBrkdnArchive
tblBrkdwnTradespeople
tblBrkdwnTradespeopleArchive

The following two tables are local, not linked
tblBrkdnArchiveTemp
tblBrkdwnTradespeopleArchiveTemp

After running through the following code, all of the four above mentioned linked tables are now local. It's vexing. I'm terribly vexed. If you can help, my status would chnge from vexed to joyous....:

Thx in advance...

' 1) Join all together (Each Table)
' Create two recordsets, One for the Brkdn set, one for the Tradespeople Set.
' Brkdn: Want it to be the Union of all records in (tblBrkdn, tblBrkdnArchive) without duplicating BrkdwnID
'Standard Union Query with no duplicates. Transferring data into a temporary table
SQLStr = "SELECT * INTO tblBrkdnArchiveTemp FROM ("
SQLStr = SQLStr & "SELECT tblBrkdn.* FROM tblBrkdn INNER JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID "
SQLStr = SQLStr & "Union ALL "
SQLStr = SQLStr & "SELECT tblBrkdn.* "
SQLStr = SQLStr & "FROM tblBrkdn LEFT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID "
SQLStr = SQLStr & "WHERE (((tblBrkdnArchive.BrkdwnID) Is Null))"
SQLStr = SQLStr & "UNION ALL SELECT tblBrkdnArchive.* "
SQLStr = SQLStr & "FROM tblBrkdn RIGHT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID "
SQLStr = SQLStr & "WHERE tblBrkdn.BrkdwnID is null);"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

' BrkdnTradespeople: Want it to be the Union of all records in (tblBrkdwnTradespeople, tblBrkdwnTradespeopleArchive) without duplicating ID
'Standard Union Query with no duplicates. Transferring data into a temporary table
SQLStr = "SELECT * INTO tblBrkdwnTradespeopleArchiveTemp FROM ("
SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* FROM tblBrkdwnTradespeople INNER JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID "
SQLStr = SQLStr & "Union ALL "
SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* "
SQLStr = SQLStr & "FROM tblBrkdwnTradespeople LEFT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID "
SQLStr = SQLStr & "WHERE (((tblBrkdwnTradespeopleArchive.ID) Is Null))"
SQLStr = SQLStr & "UNION ALL SELECT tblBrkdwnTradespeopleArchive.* "
SQLStr = SQLStr & "FROM tblBrkdwnTradespeople RIGHT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID "
SQLStr = SQLStr & "WHERE tblBrkdwnTradespeople.ID is null);"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

'delete everything from the four non-temporary tables
'tblBrkdn
SQLStr = "DELETE * FROM tblBrkdn"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

'tblBrkdwnTradespeople
SQLStr = "DELETE * FROM tblBrkdwnTradespeople"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

'tblBrkdnArchive
SQLStr = "DELETE * FROM tblBrkdnArchive"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

'tblBrkdwnTradespeopleArchive
SQLStr = "DELETE * FROM tblBrkdwnTradespeopleArchive"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

' 2) Paste the entire record set to each Brkdn table
'tblBrkdn
SQLStr = "SELECT * INTO tblBrkdn FROM tblBrkdnArchiveTemp"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

'tblBrkdnArchive
SQLStr = "SELECT * INTO tblBrkdnArchive FROM tblBrkdnArchiveTemp"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

' 3) Delete from Active & Archive Brkdn tables WHERE [DATE] < txtArchiveDate.Text
'tblBrkdn
SQLStr = "DELETE * FROM tblBrkdn WHERE ([DATE]<#" & dateStr & "#);"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

'tblBrkdnArchive
SQLStr = "DELETE * FROM tblBrkdnArchive WHERE ([DATE]>=#" & dateStr & "#);"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

' 4) Paste to Active Tradespeople table WHERE Associated Breakdown Record [DATE] >= txtArchiveBeforeDate.text
SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeople FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdn ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd n.BrkdwnID)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

' 5) Paste to Archive Tradespeople table WHERE Associated Breakdown Record [DATE] < txtArchiveBeforeDate.text
SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeopleArchive FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdnArchive ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd nArchive.BrkdwnID)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
' 6) Clean up: Delete all records from the temporary tables
'tblBrkdnArchiveTemp
SQLStr = "DELETE * FROM tblBrkdnArchiveTemp;"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True

'tblBrkdwnTradespeopleArchiveTemp
SQLStr = "DELETE * FROM tblBrkdwnTradespeopleArchiveTemp;"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True