Relationships between Tables

[LiamOC]

I am backing up all the tables in my database onto a blank database using the DoCmd.CopyObject method. This is working fine but I have trouble with tables that are in relationships. I want to keep the relationships but I am prevented from copying over tables that are in relationships. How do I get around this problem?

Thanks,

Liam

 

[GKProgrammer]

Hi Liam,

I have used the DoCmd.CopyObject method before in a backup routine that I created and it had no problems copying over the relationships to the new DB. Perhaps you could post the code so I could have a look at it and hopefully I could help you out. Regards,
gkprogrammer

 

[LiamOC]

Here it is, nothing special, just lots of DoCmd.CopyObject commands like these.That's it!

DoCmd.CopyObject "C:\WINDOWS\Desktop\Liam\T .L. I\TLI-backup", , acTable, "GENERAL-CODING"
DoCmd.CopyObject "C:\WINDOWS\Desktop\Liam\T .L. I\TLI-backup", , acTable, "NETWORK REFURBISHMENT-WORK_COMPLETED"

The error message I get is when I try and backup information on top of older information. It says I can't delete "NETWORK REFURBISHMENT-WORK_COMPLETED" because it is part of one or more relationships. Delete??? I don't have a delete statement anywhere so I assume this is just some background stuff the copy object does.

Thanks,
Liam

 

[GKProgrammer]

Hello Again,

Why not try copying the tables to a temporary db and then copy over the existing one, something like this(This is assuming that you don't have any tables that begin with MSys):

Dim con As Container
Dim doc As Document
Dim dbs As Database
Dim wsp As Workspace
Dim cnt As Byte, filecnt As Integer
Dim filename As String, filename2 As String

filename = "c:\temp.mdb"
filename2 = "C:\WINDOWS\Desktop\Liam\T .L. I\TLI-backup"
cnt = 1
filecnt = 1
Set wsp = DBEngine.Workspaces(0)

While cnt = 1
If Dir(filename) = "" Then
Set dbs = wsp.CreateDatabase(filename, dbLangGeneral)
cnt = 2
Else
filename = "C:\temp" & filecnt & ".mdb"
filecnt = filecnt + 1
End If
Wend

Set con = DBEngine(0)(0).Containers("Tables&quot
With con
For Each doc In .Documents
With doc
If Left$(.Name, 4) <> &quot;MSys&quot; Then
DoCmd.CopyObject filename, .Name, acTable, .Name
End If
End With
Next doc
End With
dbs.Close
Set wsp = Nothing
Set con = Nothing
Set doc = Nothing

If Dir(filename2) <> &quot;&quot; Then Kill filename2
FileCopy filename, filename2
Kill filename

Please let me know if this helps.
Regards,
gkprogrammer

 

[LiamOC]

Thanks for the help but I am getting an error when I compile the code I get the error:

User-defined type not defined

for the line Dim con As Container

 

[LiamOC]

Thanks for the help but I am getting an error when I compile the code I get:

User-defined type not defined

for the line Dim con As Container

 

[GKProgrammer]

That is because you don't have a reference to the Microsoft DAO Library.

When in your module code go to Tools>References
Scroll down to the Microsoft DAO (whatever version you have) Library and select it. The code will now work. Regards,
gkprogrammer

 

[GKProgrammer]

Liam,

Did this work for? Please let me know. Regards,
gkprogrammer