Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine if table is replicated (Access 2000)

    I am using ADOx to change the link datasource on the linked tables in a front end application. My problem is that I only want to change the link datasource on replicated tables and not on other tables that have been linked. Is there a property that I have overlooked or another way to restrict the Tables collection?

    Any help would be greatly appreciated.
    Graham

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine if table is replicated (Access 2000)

    I don't think there are any Table properties defined in ADOX (including Jet provider-specific ones) that will tell you whether or not table is a replicated table. You may have to resort to archaic DAO methods. Example:

    Public Function IsReplica(ByVal strTbl As String)
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim strMsg As String
    Dim strPropName As String

    Set db = CurrentDb
    Set tbl = db.TableDefs(strTbl)

    strPropName = "ReplicableBool"
    IsReplica = tbl.Properties(strPropName)

    Exit_Sub:
    Set tbl = Nothing
    Set db = Nothing
    Exit Function

    Err_Handler:
    Select Case Err.Number
    Case 3270 ' Error No 3270: Property not found.
    IsReplica = False
    Resume Exit_Sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Debug.Print "Unexpected error: " & strMsg
    Resume Exit_Sub
    End Select

    End Function

    Example:
    ? IsReplica("Table1")
    False

    This worked OK in quick test - if table is not replica, you get Err 3270 when testing for ReplicableBool property. If replica, function returns True. I don't use Replication much, so advise testing to make sure will work in your scenario.

    HTH

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine if table is replicated (Access 2000)

    In further response, here is example of sub that prints out provider-specific properties for an ADOX Table object:

    Public Sub GetTablePropsADO(ByVal strTbl As String)
    On Error GoTo Err_Handler

    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim strMsg As String
    Dim n As Integer
    Dim intCount As Integer

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTbl)
    intCount = tbl.Properties.Count

    Debug.Print tbl.Name
    For n = 0 To intCount - 1
    Debug.Print tbl.Properties(n).Name & ": " & tbl.Properties(n).Value
    Next n

    Exit_Sub:
    Set tbl = Nothing
    Set cat = Nothing
    Exit Sub

    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Debug.Print strMsg
    Resume Next
    End Select

    End Sub

    Example of output with a replicated table:

    ? IsReplica("AWD_TYPE")
    True

    GetTablePropsADO "AWD_TYPE"
    AWD_TYPE
    Temporary Table: False
    Jet OLEDB:Table Validation Text:
    Jet OLEDB:Table Validation Rule:
    Jet OLEDB:Cache Link Name/Password: False
    Jet OLEDB:Remote Table Name:
    Jet OLEDB:Link Provider String:
    Jet OLEDB:Link Datasource:
    Jet OLEDB:Exclusive Link: False
    Jet OLEDB:Create Link: False
    Jet OLEDB:Table Hidden In Access: False

    As shown above, there do not appear to be any properties using ADOX that reveal whether or not table is replica or not. So as noted you may be stuck with DAO for this determination.

    HTH

  4. #4
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine if table is replicated (Access 2000)

    Thanks Mark

    I was trying to avoid using DAO. I'll have to rework my code to test whether the table exists in the replica before trying to change the Link Datasource.

    Graham

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine if table is replicated (Access 2000)

    If these are linked tables, recommend use a "naming convention" if practical - for example, prefix each replicated linked table with "rep" (repOrders, repProducts, etc) - since they are linked you don't have to use actual table name (which can always be derived from table link properties). This would make it easier to identify the replicated tables.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Determine if table is replicated (Access 2000)

    There are specific fields that are added to replica tables, assuming these are Jet tables, and you could test for those field names to determine whether the table is a replica. That could be done with ADO. Replica tables contain fields like s_Generation, s_GUID, and s_Lineage.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine if table is replicated (Access 2000)

    Thanks Charlotte

    It works a treat!

    Graham

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •