I am working on version controlling my MS Access databases by creating an Add-in which contains VBA modules to export my database to ASCII text files, and import those to a database. I am using a combo of approaches which are discussed here: Version Control with Access Development and here: Export Access objects to text
The above links do not really provide solutions for tables. So far I have been able to export the data from the tables into .csv files which contain the data. I then export each table's field information (name,type,attributes,description,size) etc. into a text file which I can later read in (when I do the import) to create tables with those fields set properly. I also had to export the Primary key information (from the indexes) so I can properly set the primary keys.
I am now struggling to export/import relations. To export relations, I use the following code:
Private Sub ExportRelationships()
Dim relationsFolder As String
Dim relat As Relation
Dim field As field
Dim newRelationName As String
relationsFolder = CreateSubFolder("relations")
Open relationsFolder & "relations.txt" For Output As #1
' Loop over each relationship and write to relations.txt file
For Each relat In db.Relations
For Each field In relat.Fields
newRelationName = relat.Table & "_" & field.Name & "__" & relat.ForeignTable & "_" & field.ForeignName
Write #1, newRelationName; relat.Table; relat.ForeignTable; relat.Attributes; field.Name; field.ForeignName
Next field
Next relat
Close #1
End Sub
This creates a .csv text file which contains the information I need to recreate the relations.
I then try to import the relations.txt file created using the following code:
Private Sub ImportRelationships()
Dim fileName As String
Dim currentLine As String
Dim relat As Relation
Dim db As Database
Dim items() As String
Set db = CurrentDb()
fileName = exportFolder & "relations\relations.txt"
Open fileName For Input As #1
While Not EOF(1)
Line Input #1, currentLine
items = Split(currentLine, ",")
If CLng(items(3)) <> 4356 Then
Set relat = db.CreateRelation(Replace(items(0), Chr(34), ""), Replace(items(1), Chr(34), ""), Replace(items(2), Chr(34), ""), CLng(items(3)))
relat.Fields.Append relat.CreateField(Replace(items(4), Chr(34), ""))
relat.Fields(Replace(items(4), Chr(34), "")).ForeignName = Replace(items(5), Chr(34), "")
db.Relations.Append relat
End If
Wend
End Sub
But when I run this, I get a Run-time '3001' Invalid argument error.
I feel like I am missing something critical which is required to create relations. Perhaps it has something to do with the Indexes of a table? Could someone please help me out and explain what I would need to do to export the relations and import them properly?
Thanks in advance for your help.