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

 

[Katrina]

I have three different databases for security.

We have the master database basically for a backup, incase
tables, forms or reports are accidentally deleted or
changed

then we have a front/back end so that we can protect the
data while allowing people to save queries and reports.

Does any one know the answer to my original question?

 

[Tim Ferguson]

We have the master database basically for a backup, incase
tables, forms or reports are accidentally deleted or
changed

then we have a front/back end so that we can protect the
data while allowing people to save queries and reports.

Does any one know the answer to my original question?

Well, with close to a hundred lines of code, without a single comment, I
doubt you are going to get many takers for debugging it. Especially when
the situation you are describing is so scraggy.

For a start, if you want to make a database backup, the safest way is just
to do a simple file copy. An ideal method would combine that with a
database compact: for example copy the file to the backup medium, and then
use Access to compact that back over the original. This can be accomplished
by a simple VBS or even Access script. It would be many hundred times more
reliable than messing about with Relationships collections and the like.

I think the second paragraph means that you do have a conventional FE/ BE
set up, and that is fine. In that case the _only_ thing you have to back up
for security is the Back End with the data. You will still have copies of
the Front End -- most notably on the development machine -- to distribute
when users cock them up. There is one philosophy that suggests copying a
new FE file over each user whenever they log in before they get corruption
problems rather than after.

Frankly, there is no sensible reason for wanting to use DAO to manage
extensive database schema copying. It should be a one-off affair, in which
case you can use the UI and File..Get External Data..Import (with
relationships) to manage it for you. As a bulk procedure, just copy the mdb
file.

Hope that helps


Tim F

 

[Katrina]

Honestly, we had the database set up with the three
databases because we didn't kno whow to make the links
move from one place to another. However, i have since
learned how to do this, so I will take your advice and
split our master database...

Thanks for the help

Katrina