The
ProgrammingMSAccess.COM Site
A vba Procedure that Makes a database replicable after first modifying it to keep
a table local
'This procedure requires references to the
'Microsoft ADO Ext. 2.1 for DDL and Security
'as well as the Jet and Replication Objects
'2.1 Library
'Makes a database replicable, but keeps one
'table with a relation local; see comment toward
'end of procedure for special steps to account
'for deletion of a relation
Sub makeDMLessOneTbl(strFilespec As String, _
tblName As String, _
relName As String, _
tbl2Name As String, _
Optional blnColTrack As Boolean = True _)
Dim cat1 As ADOX.Catalog
Dim rep1 As JRO.Replica
Dim key1 As ADOX.Key
'Open a connection and catalog object to support
'searching for relationships in a database
Set cnn1 = New ADODB.Connection
Set cat1 = New ADOX.Catalog
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & strFilespec
Set cat1.ActiveConnection = cnn1
'Delete relation from table with foreign key
'to table that must be left unreplicated
For Each key1 In cat1.Tables(tblName).Keys
Debug.Print key1.Name, key1.Type, key1.RelatedTable
If key1.Name = relName Then
cat1.Tables(tblName).Keys.Delete relName
End If
Next
'Set rep1 pointer at database and assign False to
'replicability property for table to be
'left unreplicated
Set rep1 = New JRO.Replica
rep1.ActiveConnection = "c:\my documents\replicasamples\northwind.mdb"
rep1.SetObjectReplicability tbl2Name, "Tables", False
'Close connection so that MakeReplicable method can
'open database exclusively
Set cat1 = Nothing
cnn1.Close
Set cnn1 = Nothing
'Make database replicable; if you are not keeping a table local, this isall
'you need
Set rep1 = New JRO.Replica
rep1.MakeReplicable strFilespec, blnColTrack
'Since you cannot have a local table related to a
'replicated table, you must update your database's
'design to function without the relation
'Free resource
Set rep1 = Nothing
End Sub
Want to understand Microsoft Access 2000/2002/2003 so that you can program it to
do more tasks like this? Get Programming Microsoft Office Access
2003
by Rick Dobson from Microsoft Press. Learn more about the book by clicking
here.
Copyright 2003 CAB, Inc. All rights reserved. Republication or redistribution
of CAB, Inc. content, including by framing or similar means, is expressly
prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be
liable for any errors in the content, or for any actions taken in reliance
thereon.