Results 1 to 2 of 2
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Add Table description in VB (2000)

    I want to append a linked table, and then add a description comment.
    The code runs without errors BUT no description appears.
    Am I doing this wrong

    The code is below.

    The table definitions and locations for the link are stored in a table.

    Prior to ReLinking the tables links are deleted
    ( this is necessary because a folder containing the linked tables is deleted and refilled with new copies of the tables).
    And yes I know we ought to be able to link to the originals
    BUT, the application that uses them stops this.


    ================================================== =====================================
    Function Re_Link_Tables()

    Dim dbs As DAO.database, rst As DAO.Recordset, tdf As DAO.tabledef
    Dim strSQL As String, strT As String, strProvider As String, strCnn As String, strLocalName As String, strSource As String
    Dim strLinkDB As String
    Dim intT As Integer, strErr As String
    Dim prp As DAO.Property


    strSQL = "SELECT * FROM sys_RDB_PDX_Tables ;"
    intT = 0

    On Error GoTo NoLinkError

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

    If rst.RecordCount = 0 Then
    MsgBox "Tables are NOT defined to be linked", vbExclamation
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Re_Link_Tables = False
    Exit Function
    End If

    'Table OK, so Proceed with the Link
    rst.MoveFirst
    Do While Not rst.EOF
    intT = intT + 1
    strProvider = rst("Provider") 'Connection String Provider
    strLocalName = rst("LocalTableName") 'Connection String local table
    strLinkDB = rst("SourceTable") 'Connection Table to Collect
    strSource = rst("DataSource") 'Connection String Database (is the folder for paradox)
    'Now Link it
    Set tdf = dbs.CreateTableDef(strLocalName)
    tdf.Connect = strProvider & "DataBase=" & strSource
    tdf.SourceTableName = strLinkDB
    dbs.TableDefs.Append tdf
    'Now set the Linked table name as the Property
    'Try to set the property
    On Error Resume Next
    tdf.Properties("Description").Value = strSource & "" & strLinkDB
    If Err.Number = 3270 Then
    'Could stop this here
    Set prp = tdf.CreateProperty("Description", , strSource & "" & strLinkDB)
    tdf.Properties.Append prp
    Set prp = Nothing

    End If
    On Error GoTo NoLinkError
    Set tdf = Nothing
    rst.MoveNext
    Loop

    Re_Link_Tables = True

    Tidy:
    Set prp = Nothing
    Set tdf = Nothing
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
    Set dbs = Nothing


    Exit Function

    NoLinkError:
    Re_Link_Tables = False
    strErr = "Could Not Re Link all the tables" & vbLf & "Table " & intT & " " & vbLf & "Local name = " & strLocalName & vbLf & "From " & strSource & "" & strLinkDB & vbLf & "Could not be linked " & vbLf & "All tables after this failed to link!"
    MsgBox strErr, vbCritical, "Re Link Paradox Tables Error"

    Resume Tidy

    Exit Function
    ================================================== =====================================

    Thanks
    Andrew

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Add Table description in VB (2000)

    SORTED IT.

    The Property MUST have a type attribute of dbText (even though this is said to be optional).

    Set prp = tdf.CreateProperty("Description",dbtext , strSource & "" & strLinkDB)
    Andrew

Posting Permissions

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