Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184

    Unanswered: Broken links: "is not a valid name...

    I have a form set up that allows users to choose another database and receive data updates from the second database. The second database is basically a 'clone' of the first, which we use because people making updates don't always have network access.

    After the user selects the clone, the master database creates a link to the tables, runs update and add SQL queries, then deletes the link.

    Or it should. Sometimes it blows up and leaves stray links. I've got code to check for an old link and delete if it's there before trying to link, but it apparently isn't working. Could a slow network cause this?

    Here's the code:

    If ExistTable("tblTable_Clone") = True Then
    'delete link, we are creating a new link
    DoCmd.DeleteObject acTable, "tblTable_Clone"
    End If

    strSQL = <some SQL statment>
    CurrentDb.Execute strSQL

    DoCmd.DeleteObject acTable, "tblTable_Clone"

    So, the links aren't going away and we end up with several bad runs and links named tblTable_Clone1, tblTable_Clone2, etc.

    Second problem is that sometimes the link gets really broken and I get a message that says "is not a valid name. Make sure the name is not too long...". When this happens, I have to go through linked table manager and force it to ask for the path, then link manually.

    what's going on? How can I get the ghost and zombie links to go away?

  2. #2
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    I would like these links to be cleared out via VBA. It's an easy enough manual fix, but it happens enough that it's driving us all batty.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A very slow network could be the cause of your problems (Access and WANs do not go along very well together), however, several pieces of information are missing:
    - What's in the ExistTable function?
    - How do you create the linked table?
    - Do you test the validity of the connection string before creating the linked table (i.e. assigning it to the Connect property of the TableDef)?
    - Why don't you use the RefreshLink method of the TableDef object when the linked table already exists?
    Have a nice day!

  4. #4
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    ExistTable is some code I borrowed:

    Function ExistTable(TableName As String) As Boolean
    Dim db As Database
    Dim tbl As Recordset

    On Error GoTo ERR_ExistTable

    Set db = CurrentDb
    Set tbl = db.OpenRecordset(TableName)
    ExistTable = True
    Set tbl = Nothing

    EXIT_ExistTable:
    Exit Function

    ERR_ExistTable:
    ExistTable = False
    Set tbl = Nothing
    Resume EXIT_ExistTable
    End Function

    ================================================== =

    Sorry I left out the line that creates the link:

    DoCmd.TransferDatabase acLink, "Microsoft Access", tbFile, acTable, "tblTable", "tblTable_Clone"

    Where tbFile is the backend database name.



    I can't just refresh the link because because the backend database name changes: Database_LisaClone, Database_JoeSmith, etc.

    ================================================== =


    I'm not sure how I'd test the connection first. I'm really starting to think that the slow network is the culprit and it's not deleting completely or at all.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Call me old-fashioned if you want, but I dont like to rely on error trapping when it's not absolutely necessary, especially in VB/VBA.

    Here are alternative methods to test for the existence of a table. The first one is not recommended by MS and, although is works with every version of Access (from Acess 2.0 to Access 2010), it's possible that it won't work in a future version of Access:
    Code:
    Function Exist(ByVal ObjectName As String) As Boolean
    
        Exist = DCount("*", "MSysObjects", "Name='" & ObjectName & "'")
    
    End Function
    The second is more standard:
    Code:
    Function TableExists(ByVal TableName As String) As Boolean
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            If tdf.Name = TableName Then Exit For
        Next tdf
        If Not tdf Is Nothing Then TableExists = True
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Function
    2. You can test for the existence of the remote database (the one where the table you want to link to the current database is stored) using:
    Code:
    Function DatabaseExists(ByVal DatabaseName As String) As Boolean
    
        If Len(Dir(DatabaseName)) > 0 Then DatabaseExists = True
        
    End Function
    3. If you prefer a function that's able to test for the existence of almost anything (an object in the current database or in another database, a file, a property, a database in a SQL Server, etc.) you can try:
    Code:
    Option Compare Database
    Option Explicit
    
    Public Function Exist(ByVal ObjectType As String, ByVal ObjectName As String, Optional ByVal SearchInLib As Boolean) As Variant
    
        Const c_Criteria As String = "Type IN ( @T ) AND Name = '@N'"
        
        Dim dbs As DAO.Database
        Dim pty As Property
        Dim strCriteria As String
        Dim varRetVal As Variant
        Dim var As Variant
        
        On Error GoTo Err_Exist
        ObjectType = Trim(ObjectType)
        ObjectName = Trim(ObjectName)
        If Len(ObjectName) > 0 Then
            Select Case ObjectType
                Case "Table":       strCriteria = Replace(Replace(c_Criteria, "@N", ObjectName), "@T", "1, 4, 6")
                Case "Query":       strCriteria = Replace(Replace(c_Criteria, "@N", ObjectName), "@T", "5")
                Case "Form":        strCriteria = Replace(Replace(c_Criteria, "@N", ObjectName), "@T", "-32768")
                Case "Report":      strCriteria = Replace(Replace(c_Criteria, "@N", ObjectName), "@T", "-32764")
                Case "Macro":       strCriteria = Replace(Replace(c_Criteria, "@N", ObjectName), "@T", "-32766")
                Case "Module":      strCriteria = Replace(Replace(c_Criteria, "@N", ObjectName), "@T", "-32761")
                Case "Server":      varRetVal = (InStr(GetServerList, ObjectName) > 0)
                Case "File":        varRetVal = (Len(Dir(ObjectName)) > 0)
                Case "Database":    var = Split(ObjectName, ";")
                                    If UBound(var) = 1 Then
                                        varRetVal = Exist("Server", var(0))
                                        If varRetVal = True Then varRetVal = CBool((InStr(GetDatabaseList(var(0)), var(1)) > 0))
                                    Else
                                        varRetVal = Array(2, "Exist", "Bad ObjectName format.", "When used with ObjectType = 'Database',", _
                                                                      "the format of ObjectName must be: '<ServerName>;<DatabaseName>'")
                                    End If
                Case "Property":    If SearchInLib = True Then Set dbs = CodeDb Else Set dbs = CurrentDb
                                    For Each pty In dbs.Properties
                                        If pty.Name = ObjectName Then Exit For
                                    Next pty
                                    varRetVal = Not (pty Is Nothing)
                                    Set pty = Nothing
                                    Set dbs = Nothing
                Case Else:          varRetVal = Array(2, "Exist", "Unknown ObjectType: '" & ObjectType & "'", _
                                                                  "1. The parameter ObjectType cannot be an empty string.", _
                                                                  "2. Valid Objectype values are:", _
                                                                  vbTab & "'Table', 'Query', 'Form', 'Report', 'Macro'", _
                                                                  vbTab & "'Module', 'Server', 'File', 'Database', 'Property'")
            End Select
            If Len(strCriteria) > 0 Then
                If SearchInLib = True Then
                    varRetVal = CBool(DCountEx("Name", "MSysObjects", strCriteria))
                Else
                    varRetVal = CBool(DCount("Name", "MSysObjects", strCriteria))
                End If
            End If
        Else
            varRetVal = False
        End If
        
    Exit_Exist:
        Exist = varRetVal
        Exit Function
    
    Err_Exist:
        varRetVal = Array(Err.Number, "Exist", Err.Description)
        Err.Clear
        Resume Exit_Exist
    
    End Function
    
    Public Function GetDatabaseList(ByVal ServerName As String) As Variant
    
        Const c_Connection As String = "ODBC;driver={SQL Server};SERVER=@S;DATABASE=Master;Trusted_Connection=Yes;"
    
        Dim strConnection As String
        Dim strSQL As String
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim varRetVal As Variant
        
        On Error GoTo Err_GetDatabaseList
        If Exist("Query", "qry_wzListFormBuilder", True) Then CodeDb.QueryDefs.Delete "qry_wzListFormBuilder"
        strConnection = Replace(c_Connection, "@S", ServerName)
        strSQL = "SELECT [Name] FROM sys.databases WHERE owner_sid <> '0x01' ORDER BY [Name]"
        Set dbs = CodeDb
        Set qdf = dbs.CreateQueryDef
        qdf.Name = "qry_wzListFormBuilder"
        qdf.Connect = strConnection
        qdf.SQL = strSQL
        dbs.QueryDefs.Append qdf
        Set rst = qdf.OpenRecordset
        With rst
            Do Until .EOF
                If Len(varRetVal) > 0 Then varRetVal = varRetVal & ";"
                varRetVal = varRetVal & rst!Name
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        If Exist("Query", "qry_wzListFormBuilder", True) Then CodeDb.QueryDefs.Delete "qry_wzListFormBuilder"
        
    Exit_GetDatabaseList:
        GetDatabaseList = varRetVal
        Exit Function
        
    Err_GetDatabaseList:
        varRetVal = Array(Err.Number, "GetDatabaseList", Err.Description)
        Err.Clear
        Resume Exit_GetDatabaseList
    
    End Function
    
    Public Function GetServerList() As Variant
    
        Dim objSQLDMO As Object
        Dim Servers As Variant
        Dim varRetVal As Variant
        Dim strServer As String
        Dim i As Integer
        
        
        On Error GoTo Err_GetServerList
        Set objSQLDMO = CreateObject("SQLDMO.Application")
        Set Servers = objSQLDMO.ListAvailableSQLServers
        For i = 1 To Servers.Count
            strServer = Servers.Item(i)
            If strServer = "(local)" Then strServer = Environ("COMPUTERNAME")
            If Len(varRetVal) > 0 Then varRetVal = varRetVal & ";"
            varRetVal = varRetVal & strServer
        Next
        Set Servers = Nothing
        Set objSQLDMO = Nothing
    
    Exit_GetServerList:
        GetServerList = varRetVal
        Exit Function
        
    Err_GetServerList:
        varRetVal = Array(Err.Number, "GetServerList", Err.Description)
        Err.Clear
        Resume Exit_GetServerList
    
    End Function
    
    Public Function DCountEx(ByVal Column As String, _
                             ByVal Domain As String, _
                             Optional ByVal Criteria As String, _
                             Optional ByVal Database As String = "<CodeDb>") As Variant
    
        
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim varRetVal As Variant
        
        On Error GoTo Err_DCountEx
        Select Case Database
            Case "<CodeDb>"
                Set dbs = CodeDb
            Case "<CurrentDb>"
                Set dbs = CurrentDb
            Case Else
                If Len(Dir(Database)) > 0 Then Set dbs = OpenDatabase(Database)
        End Select
        strSQL = "SELECT " & Column & " FROM " & Domain
        If Len(Criteria) > 0 Then strSQL = strSQL & " WHERE " & Criteria
        Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)
        If rst.EOF = False Then
            rst.MoveLast
            varRetVal = rst.RecordCount
        End If
        rst.Close
        Set rst = Nothing
    
    Exit_DCountEx:
        Set dbs = Nothing
        DCountEx = varRetVal
        Exit Function
    
    Err_DCountEx:
        varRetVal = Array(Err.Number, "DCountEx", Err.Description)
        Err.Clear
        Resume Exit_DCountEx
    
    End Function
    4. Personnaly, I prefer to create a linked table explicitly:
    Code:
    Function CreateLinkToExternalTable(ByVal MDBPath As Variant, ByVal TableName As Variant) As Variant
    
        Dim dbs As Database
        Dim tdf As TableDef
        Dim varRetVal As Variant
    
        On Error GoTo Err_CreateLinkToExternalTable
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef(TableName)
        tdf.Connect = ";DATABASE=" & MDBPath
        tdf.SourceTableName = TableName
        dbs.TableDefs.Append tdf
        varRetVal = True
    
    Exit_CreateLinkToExternalTable:
        CreateLinkToExternalTable = varRetVal
        Exit Function
    
    Err_CreateLinkToExternalTable:
        varRetVal = Array(Err.Number, "CreateLinkToExternalTable", Err.Description)
        Err.Clear
        Resume Exit_CreateLinkToExternalTable
    
    End Function
    Anyway, none of these will really help is a slow network is the cause of your problems.
    Have a nice day!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Note:
    Several functions in my previous post use a special error trapping mechanism and can return an Array in case of error. Those functions are intended to be called through a particular function named Try which works in conjunction with another function named DisplayMsg, as follows:
    Code:
    Public Function Try(ByVal Evaluate As Variant) As Variant
    
        Dim varRetVal As Variant
        
        varRetVal = Evaluate
        If IsArray(varRetVal) Then
            DisplayMsg varRetVal
            varRetVal = False
        End If
        Try = varRetVal
        
    End Function
    
    Public Sub DisplayMsg(ByVal MsgStack As Variant)
    
        Dim strMsg As String
        Dim i As Integer
        
        Select Case MsgStack(0)
            Case 0:     strMsg = "Information returned by the List Form Builder:" & vbNewLine
            Case 1, 2:  strMsg = "Warning returned by the List Form Builder:" & vbNewLine
            Case Else:  strMsg = "An error occured during the Form creation process." & vbNewLine
        End Select
        For i = 0 To UBound(MsgStack)
            strMsg = strMsg & vbNewLine & _
                     Choose(i + 1, "Code:   ", "Source: ", "Message: ", "Additional Information:" & vbNewLine, "", "") & MsgStack(i)
        Next i
        MsgBox strMsg, Switch(MsgStack(0) = 0, vbInformation, MsgStack(0) < 3, vbExclamation, MsgStack(0) > 2, vbCritical), "ListForm Builder"
        If MsgStack(0) > 2 Then
            DoCmd.Close acForm, "Frm_wzListFormBuilder_Main", acSaveNo
            End
        End If
        
    End Sub
    Example of use:
    Code:
    If Try(Exist("Form", "SF_wzListFormBuilder_Sample", True)) = True Then DoCmd.DeleteObject acForm, "SF_wzListFormBuilder_Sample"
    Have a nice day!

  7. #7
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184

    Cool

    Thanks! I tried the "TableExists" function and it worked. I also added some "DoEvents" to slow things down. I tested by placing the remote database on a slow server and it did its thing and removed the links at the end like it's supoosed to do. I might run some tests with the old code to see if I can duplicate the problem. It's been random, which is why I think it's a network speed problem, for which the best I can do is "try and see".

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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