TransferText Loses Properties - No Primary Keys; Unique and Required properties incorrect

[LifetimeLearner]

I am using TransferText to create text files of my tables and then restore them into another database. However, when I examine the restored tables, some of the properties are not correct. The fields and data all exist, but there are no Primary Keys, and both the Unique and Required property are incorrect in a couple of fields that I checked. There may be more, but I did not check further. Can anyone shed light on this?

This is the code to save the tables to text:

Public Sub ExportDatabaseObjects()
  On Error GoTo Err_ExportDatabaseObjects
    
  Dim db As Database
  Dim td As TableDef
  Dim d As Document
  Dim c As Container
  Dim i As Integer
  Dim sExportLocation As String
  Dim strSQl As String
  
  Set db = CurrentDb()
  
  'Archive txt files from previous run of this code
  MoveTxtFilesToArchiveFolder
  
  strSQl = "Delete * from _ApplicationObjectList"
  CurrentDb.Execute strSQl, dbFailOnError
  
  sExportLocation = Application.CurrentProject.Path & "\ExportDatabaseObjectsToFiles\"
  
  For Each td In db.TableDefs 'Tables
   If Left(td.Name, 4) <> "MSys" And td.Name <> "_ApplicationObjectList" Then
   strSQl = "insert into _ApplicationObjectList ( ObjType, ObjName, ObjLocation ) " & _
             "select " & acTable & " as ObjType, '" & td.Name & "' as ObjName, '" & sExportLocation & "Table_" & td.Name & ".txt" & "' as ObjLocation;"
    db.Execute strSQl, dbSeeChanges
    DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
   End If
  Next td
  
 'Other objects get exported here

  Set db = Nothing
  Set c = Nothing
  
  MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation
  
Exit_ExportDatabaseObjects:
  Exit Sub
  
Err_ExportDatabaseObjects:
  MsgBox Err.Number & " - " & Err.Description
  Resume Exit_ExportDatabaseObjects
End Sub

This is the code to restore the tables from text:

Sub RestoreDatabaseObjects()
On Error GoTo err_Handler


  Dim strSQl As String
  Dim rs As DAO.Recordset
  
  strSQl = "select * from _ApplicationObjectList"
  Set rs = CurrentDb.OpenRecordset(strSQl)
  
  If Not rs.BOF And Not rs.EOF Then
    rs.MoveFirst
    While (Not rs.EOF)
        Debug.Print rs.Fields("ObjName")
        If rs.Fields("ObjType") = acTable Then
            DoCmd.TransferText acImportDelim, , rs.Fields("ObjName"), rs.Fields("ObjLocation"), True
        Else
            Application.LoadFromText rs.Fields("ObjType"), rs.Fields("ObjName"), rs.Fields("ObjLocation")
        End If
      rs.MoveNext
    Wend
  End If
  rs.Close
  Set rs = Nothing
Exit_Handler:
    Exit Sub
    
err_Handler:
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure RestoreDatabaseObjects"
    Resume Exit_Handler


End Sub

[isladogs]

Exporting a table as a text file will inevitably mean no primary key etc when imported back into another database
Instead open the new database and import the table from the old database. It will be transferred exactly as it was in the original database. Job done

[LifetimeLearner]

Exporting a table as a text file will inevitably mean no primary key etc when imported back into another database
Instead open the new database and import the table from the old database. It will be transferred exactly as it was in the original database. Job done

I did that and it compounded the issue.

This is what I am wanting to accomplish:
I noticed a weird corruption issue with one of my tables. When I added it to my relationship window it would add 2 copies of the table. After I imported it into another database, it started adding 4. I was able to fix it by creating a new table and copying and pasting the fields from the old table into it, and then importing the data from the old table. It made me wonder, though, if it was possible that there were other tables with corruption. I have used this code successfully on my front-end to repair forms, etc. I like the idea of being able to occasionally do a full reset that leaves any brewing corruption behind. I do it infrequently on the front-end and would like to have the ability to do the same thing on the back-end. Going through the steps I had to do to resolve this issue would take a ridiculous amount of time for all of my tables, so I was hoping that this code would do that.

Is there another alternative that would accomplish this?

[isladogs]

Doing what I suggested in the standard approach. It does not cause duplication in the relationships window

There are indeed better methods of fixing issues but the solutions depend on the type of issue involved

There are four types of corruption:
a) compilation code corruption - this is fixed by DECOMPILING your project. See http://www.fmsinc.com/microsoftacces.../Decompile.asp. Make a backup first. Afterwards recompile then compact
b) corruption in form/report controls - best fixed by creating a new form and copying all objects that haven't been corrupted then re-creating any that have been
b) data corruption in user tables - this needs to be identified and corrupted records removed. Copying a table with corrupted data to a new database or importing all objects to a new database will often cause the corrupted data to be transferred as well
c) data corruption in system tables - this is usually fixed by importing all objects except system tables to a new database. However, there are other approaches depending on the issue. For example, see http://www.mendipdatasystems.co.uk/r...cts/4594424204

However, if you take sensible precautions when developing databases, corruption should be very uncommon. If it is happening regularly then you should think about why this is happening.
It would also be wise to backup before making any design changes and doing regular backups of your data

[Micron]

Agree that repeated corruption is a sign of a problem. Using Access over wifi is said to be one way to repeatedly corrupt. Another approach to fixing it is to use vba to create and define new tables and their field data types. However, if I had to go to that length so as to be able to frequently remedy a problem I'd say it's more sensible to fix the cause of the problem.

[LifetimeLearner]

Doing what I suggested in the standard approach. It does not cause duplication in the relationships window

Apparently my table never got that memo, because that is exactly what happened. It caught me by surprise when the duplicate tables showed up in the relationship window the first time. I would delete the duplicate and save my changes, but when I opened it up again, the duplicate table was back. So I imported all my tables into another database to see if that would clear the corruption, only to find that now there were 4 copies of the same table in the relationship window. As I mentioned, I was able to clear it by recreating the table. I would really like to understand what would cause this kind of corruption in the table structure in the first place.

I appreciate the input from both of you, and I would love to get to the point of never seeing corruption. I am doing all that I know to prevent it, but there must be things that cause corruption of which I am not aware. Is there a definitive list somewhere of the practices or conditions that would cause corruption?

[Micron]

a Google search will give you tens of millions of hits so not really practical to re-list them here. Besides, your issue might not be corruption at all. I've just done a search on this and the results indicate that
- Access might be creating duplicates to better represent a relationship when you actually need a copy of a table, based on your design
- deleting a table won't fix the issue; you need to delete the relationship line first, then delete the table
- apparently a duplicate relationship might be created when you use the lookup field wizard (I do not use lookup fields in tables so wouldn't experience this)
Why you'd get 4 instances in a new db is a puzzle though. Perhaps you should research the current issue and see if anything you find applies to your situation before deciding it is due to corruption.

[LifetimeLearner]

a Google search will give you tens of millions of hits so not really practical to re-list them here. Besides, your issue might not be corruption at all. I've just done a search on this and the results indicate that
- Access might be creating duplicates to better represent a relationship when you actually need a copy of a table, based on your design
- deleting a table won't fix the issue; you need to delete the relationship line first, then delete the table
- apparently a duplicate relationship might be created when you use the lookup field wizard (I do not use lookup fields in tables so wouldn't experience this)
Why you'd get 4 instances in a new db is a puzzle though. Perhaps you should research the current issue and see if anything you find applies to your situation before deciding it is due to corruption.

The second possibility you mentioned is the key to what I am seeing and a very important thing to know. I went back to one of my backups that had the extra table and deleted the relationship line. Once I did that I was able to delete the table, save the relationship window and the table did not reappear when I reopened the window. It appears that when I am "deleting" tables from the relationships it is actually just hiding the tables. As you said you have to delete the line first and then you can delete the table and you are actually removing the relationship, not just hiding it.

I don't know why I had two in the first place or why I got four tables when I imported all my tables into a new database. Neither your first suggestion nor your third pertain in this case, but I will do some more research when I have a bit of time.

This has been most enlightening. Thanks!

[Micron]

Happy to help where I can. Please do post a solution/cause if you figure it out.

[isladogs]

You may find it helpful to read some or all of my 3-part article about relationships and referential integrity. This is a link to the first part: Relationships1 - Mendip Data Systems