1

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.

Community
  • 1
  • 1

0 Answers0