Exporting Relationships with VBA (posted in export group and VBA group)

[Katrina]

I have 3 databases - a master with all the tables forms
etc, a Back end with just the tables, and a front end with
the forms etc.

I was given the following code after I asked for help on
exporting relationships with code... (I want to export the
tables from the master to the back End - however, i do
not want to use the database splitter, because I also want
leave the tables in the master - I ignore the part of the
code below that deletes the current tables)

However when I use the code, I get the error that "You
cannot add or change a record because a related
record is required in table "TABLENAME""
Where TABLENAME is the valid name of one of my tables.

I checked and all of the tables are in the BE...

I marked the part where the error occurs with the line
********THIS IS WHERE I GET MY ERROR***********

Any suggestions?


Public Sub sSplitdbFEWithRelationships(strFile As String)
' Procedure to export all tables from the front end to a
specified back-end
' Accepts:
' strFile - The Name and path of the back-end
database.
' Notes:
' This code copes with relationships in the front-
end database, recreating them in the back-end.
' It has a limit of 100 relationships, but this can
be manually changed by changing the upper boundary of astr
as required.
On Error GoTo E_Handle
Dim dbFE As Database, dbBE As Database
Dim tdf As TableDef
Dim rel As Relation
Dim fld As Field
Dim astr(1 To 100, 1 To 4) As String
Dim intLoop As Integer, intRelCount As Integer,
intTableCount As Integer
Dim strTable As String
Set dbFE = CurrentDb
intLoop = 1
If Len(Dir(strFile)) = 0 Then
Set dbBE = DBEngine(0).CreateDatabase(strFile,
dbLangGeneral)
Else
Set dbBE = DBEngine(0).OpenDatabase(strFile)
End If
For Each rel In dbFE.Relations
For Each fld In rel.Fields
astr(intLoop, 1) = rel.Table
astr(intLoop, 2) = rel.ForeignTable
astr(intLoop, 3) = fld.Name
astr(intLoop, 4) = fld.ForeignName
intLoop = intLoop + 1
Next fld
Next rel
intRelCount = dbFE.Relations.Count - 1
For intLoop = intRelCount To 0 Step -1
dbFE.Relations.Delete dbFE.Relations(intLoop).Name
Next intLoop
intTableCount = dbFE.TableDefs.Count - 1
For intLoop = intTableCount To 0 Step -1
strTable = dbFE.TableDefs(intLoop).Name
If Left(strTable, 4) <> "MSys" And Left(strTable, 4)
<> "USys" And Len(dbFE.TableDefs(intLoop).Connect) = 0 Then
DoCmd.TransferDatabase acExport, "Microsoft
Access", strFile, acTable, strTable, strTable
DoCmd.DeleteObject acTable, strTable
DoCmd.TransferDatabase acLink, "Microsoft
Access", strFile, acTable, strTable, strTable
End If
Next intLoop
'********THIS IS WHERE I GET MY ERROR***********
For intLoop = 1 To intRelCount + 1
Set rel = dbBE.CreateRelation(astr(intLoop, 1) &
astr(intLoop, 2), astr(intLoop, 1), astr(intLoop, 2))
rel.Fields.Append rel.CreateField(astr(intLoop, 3))
rel.Fields(astr(intLoop, 3)).ForeignName = astr
(intLoop, 4)
dbBE.Relations.Append rel
Next intLoop
sExit:
On Error Resume Next
Set rel = Nothing
Set dbFE = Nothing
dbBE.Close
Set dbBE = Nothing
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf
& "sSplitdbFEWithRelationships", vbOKOnly +
vbCritical, "Error: " & Err.Number
Resume sExit
End Sub

 

[TC]

Sorry, not to answer your question, but: why have the three databases?

If you distribute your database in a split form (front-end +
back-end), you should really develop & test it in that form also. This
is because certain things can work differently in a split database,
compared to an unsplit one. So, code that works correctly in an
unmsplit database, can stop working when you split the database.

For example, Openrecordset() opens a different kind of recordset (by
default) for local tables, compared to linked tables. So certain
methods of that default recordset might work in one case, but not in
the other.

HTH,
TC