Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    5

    Unanswered: Linked Table goes Blank

    I am having problems with linked tab delimited text files. I am running Access 2007 in windows XP.

    This has happend to with several different access databases. I cannot figure out what is causing access tables to behave like this. After building an access database and running it fine, a linked table will suddenly decide to no longer show data it contains. I open the table and it shows only the field headings and no data. I have verified that the table is looking at the right file and it should be showing data. The only way to fix it is to delete the table and its link specification and relink it. I have had to completely rebuild a database because of this.

    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm a little confused here: Are you talking about tab delimited text files or about Access tables?
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Posts
    5
    I am talking about a access table that is linked to a tab delimited text file. I have to use a tab delimited text file because of nightly runs that generate tab delimited text files. The access database is looking at those files. All fields in the file are formatted as text fields.

    Most of the time these work great. Occasionally though, it all crashes because access will no longer see the data in the file, even though it is in the same folder, same name, same structure, same format just different data for the day. When this happens I can open the text file and see the data but access doesn't for some reason. I cannot figure this out and usually have to either relink the text files or do a complete rebuild of the database.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try to use the RefreshLink method of the Tabledef object before trying to access the contents of the linked table:
    Code:
    Sub RefreshLinkedTable()
    
        Dim tdf As DAO.TableDef
        Dim dbs As DAO.Database
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("LinkedTable")
        tdf.RefreshLink
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    Another, more radical, solution would consist in re-creating the attached table every time you want to use it:
    Code:
    Sub CreateLinkedTable(ByVal TableName As String, ByVal SourceTableName As String, ByVal ConnectString As String)
    
        Dim tdf As DAO.TableDef
        Dim dbs As DAO.Database
        
        Set dbs = CurrentDb
    '
    '   Delete the attached table if it already exists.
    '
        For Each tdf In dbs.TableDefs
            If tdf.Name = TableName Then
                DoCmd.DeleteObject acTable, TableName
                Exit For
            End If
        Next
    '
    '   Create the (new) attached table.
    '
        Set tdf = dbs.CreateTableDef(TableName, SourceTableName, ConnectString)
        dbs.TableDefs.Append tdf
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •