-2

Following this question thread, was able to successfully code the change suggested by "changing the sourcetable of a linked table in access 2007 with C#". However, it appears this customer has queries coded with relationships defined at the query level and the delete/append process breaks the relationships. Anyone have any idea how to preserve the relationships? And why is it that the tabledef.Sourcetable can't be updated?

Code snip:

Option Compare Database

Sub test() Dim tdf As TableDef Dim db As Database

Set db = CurrentDb

Open "out.txt" For Output As #1
For Each tdf In db.TableDefs
    If tdf.Connect <> vbNullString Then
        Print #1, tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
        Select Case tdf.SourceTableName
            Case "CTITLU.txt"
                'tdf.SourceTableName = "dbo.GRANTSADJS"
                'tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=DCFTDBCL01-L01\EDS_DEV;DATABASE=GRANTSDB2;UID=grants_reader;PWD=xxxxx;TABLE=DBO.GRANTSTABL"
                tdf.RefreshLink
        End Select
    End If
Next

End Sub

When I run this with just the tdf.connect syntax uncommented, it errors on the tdf.refreshlink call with "Run-time error '3011': The Microsoft Access database engine could not find the object 'objectname'..." I'm trying to update a text linked table to the equivalent SQL Server based linked table. The objectname does have spaces and hyphens in it, but it is correctly showing the name in the error message. For whatever reason, the previous developer shipped dumps of the tables to a file system instead of linking the tables directly. This is a small DB with very light transactional activity so there is very little chance this will cause any issues. When the tdf.SourceTableName is uncommented, throws the "Run-time error '3268': Cannot set this property once the object is part of a collection."

I followed other threads indicating this issue (noted above), and was successful using the tdf.delete / tdf.append calls to duplicate the tabledef with new source tablename and connection info. However, the dependent query's relationship definitions have disappeared and the query is unusable without redefining all of the links.

Greg Simpson
  • 1
  • 1
  • Is it the same connection string? – Nathan_Sav May 19 '17 at 14:18
  • You should make a link to code you are talking about or post the code in your question. Otherwise your questions are disembodied from the source of your trouble. – abraxascarab May 19 '17 at 15:01
  • It is not necessary to delete and append tables just to change table links. Just change the Connect property http://stackoverflow.com/questions/15473263/update-an-access-linked-table-to-use-a-unc-path – June7 May 19 '17 at 19:32
  • Possible duplicate of [Update an Access linked table to use a UNC path](http://stackoverflow.com/questions/15473263/update-an-access-linked-table-to-use-a-unc-path) – June7 May 19 '17 at 19:33
  • @June7 This is only true if the SourceTableName remains unchanged. Otherwise it does require deleting and re-appending to the TableDefs collection. One would think that the SourceTableName is just as dynamic as the Connect, but this isn't the case. – C Perkins May 22 '17 at 15:41
  • I suspect this is a problem with Name AutoCorrect Options (i.e. Perform Name AutoCorrect) found at `File | Options | Current Database` When the linked table is deleted, is Access updating the query? You could try disabling these Name AutoCorrect options. By "relationships at the query level", I suppose you mean SQL query JOINS.(?) Could the problem be that the new SQL table is not precisely the same, so the same JOINS are not possible with the new table? – C Perkins May 22 '17 at 15:49
  • Finally, if the problem is only with queries, you could temporarily store the query SQL (in a temporary variable) and reassign it after the TableDef object is deleted and re-appended. This would preserve the "relationships" (i.e. JOINS) defined in the query. See [How to change querydef sql programmatically in MS Access](https://stackoverflow.com/questions/2797951/how-to-change-querydef-sql-programmatically-in-ms-access). – C Perkins May 22 '17 at 15:54

1 Answers1

0

C Perkins, thanks, that was it. There was a slight difference in the table definition such that when using delete/append, it 'broke' the relationships (yes joins) in the related query. Using a DB view to fix that, it worked just fine. However it still 'moves' the query from its former place as being related to the original table object. Our customers will at least have their current data and not a weekly snapshot. Thanks again.

Greg Simpson
  • 1
  • 1