Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: Unrecognized Database format after split

    I have split a multi-user database and have encountered some problems.

    First, I split the database by putting the FE on the client PC's and linking the tables to the BE on each PC. I then installed Tony Toew's autofe so I could send out the FE automatically. First, I would link the tables at one of the client PC's and then put a copy of this FE in the folder to auto distribute. I am now seeing "Unrecognized Database Format" error when I do a compact and repair on the BE. I am wondering if I need to re-link those tables each time the database is opened on the client PC's. I am not a programmer but a GUI user so this is what I did. I found the following re-linking code on Tony Toew's web page.

    Function fRefreshLinks() As Boolean
    Dim strMsg As String, collTbls As Collection
    Dim i As Integer, strDBPath As String, strTbl As String
    Dim dbCurr As DATABASE, dbLink As DATABASE
    Dim tdfLocal As TableDef
    Dim varRet As Variant
    Dim strNewPath As String

    Const cERR_USERCANCEL = vbObjectError + 1000
    Const cERR_NOREMOTETABLE = vbObjectError + 2000

    On Local Error GoTo fRefreshLinks_Err

    If MsgBox("Are you sure you want to reconnect all Access tables?", _
    vbQuestion + vbYesNo, "Please confirm...") = vbNo Then Err.Raise cERR_USERCANCEL

    'First get all linked tables in a collection
    Set collTbls = fGetLinkedTables

    'now link all of them
    Set dbCurr = CurrentDb

    strMsg = "Do you wish to specify a different path for the Access Tables?"

    If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") = vbYes Then
    strNewPath = fGetMDBName("Please select a new datasource")
    strNewPath = vbNullString
    End If

    For i = collTbls.Count To 1 Step -1
    strDBPath = fParsePath(collTbls(i))
    strTbl = fParseTable(collTbls(i))
    varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl & "'....")
    If Left$(strDBPath, 4) = "ODBC" Then
    'ODBC Tables
    'ODBC Tables handled separately
    ' Set tdfLocal = dbCurr.TableDefs(strTbl)
    ' With tdfLocal
    ' .Connect = pcCONNECT
    ' .RefreshLink
    ' collTbls.Remove (strTbl)
    ' End With
    If strNewPath <> vbNullString Then
    'Try this first
    strDBPath = strNewPath
    If Len(Dir(strDBPath)) = 0 Then
    'File Doesn't Exist, call GetOpenFileName
    strDBPath = fGetMDBName("'" & strDBPath & "' not found.")
    If strDBPath = vbNullString Then
    'user pressed cancel
    End If
    End If
    End If

    'backend database exists
    'putting it here since we could have
    'tables from multiple sources
    Set dbLink = DBEngine(0).OpenDatabase(strDBPath)

    'check to see if the table is present in dbLink
    strTbl = fParseTable(collTbls(i))
    If fIsRemoteTable(dbLink, strTbl) Then
    'everything's ok, reconnect
    Set tdfLocal = dbCurr.TableDefs(strTbl)
    With tdfLocal
    .Connect = ";Database=" & strDBPath
    collTbls.Remove (.Name)
    End With
    End If
    End If
    fRefreshLinks = True
    varRet = SysCmd(acSysCmdClearStatus)
    MsgBox "All Access tables were successfully reconnected.", _
    vbInformation + vbOKOnly, _

    Set collTbls = Nothing
    Set tdfLocal = Nothing
    Set dbLink = Nothing
    Set dbCurr = Nothing
    Exit Function
    fRefreshLinks = False
    Select Case Err
    Case 3059:

    MsgBox "No Database was specified, couldn't link tables.", _
    vbCritical + vbOKOnly, _
    "Error in refreshing links."
    Resume fRefreshLinks_End
    MsgBox "Table '" & strTbl & "' was not found in the database" & _
    vbCrLf & dbLink.Name & ". Couldn't refresh links", _
    vbCritical + vbOKOnly, _
    "Error in refreshing links."
    Resume fRefreshLinks_End
    Case Else:
    strMsg = "Error Information..." & vbCrLf & vbCrLf
    strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
    strMsg = strMsg & "Description: " & Err.Description & vbCrLf
    strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
    MsgBox strMsg, vbOKOnly + vbCritical, "Error"
    Resume fRefreshLinks_End
    End Select
    End Function

    Function fIsRemoteTable(dbRemote As DATABASE, strTbl As String) As Boolean
    Dim tdf As TableDef
    On Error Resume Next
    Set tdf = dbRemote.TableDefs(strTbl)
    fIsRemoteTable = (Err = 0)
    Set tdf = Nothing
    End Function

    Function fGetMDBName(strIn As String) As String
    'Calls GetOpenFileName dialog
    Dim strFilter As String

    strFilter = ahtAddFilterItem(strFilter, _
    "Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
    "*.mdb; *.mda; *.mde; *.mdw")
    strFilter = ahtAddFilterItem(strFilter, _
    "All Files (*.*)", _

    fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
    OpenFile:=True, _
    DialogTitle:=strIn, _
    End Function

    Function fGetLinkedTables() As Collection
    'Returns all linked tables
    Dim collTables As New Collection
    Dim tdf As TableDef, db As DATABASE
    Set db = CurrentDb
    For Each tdf In db.TableDefs
    With tdf
    If Len(.Connect) > 0 Then
    If Left$(.Connect, 4) = "ODBC" Then
    ' collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
    'ODBC Reconnect handled separately
    collTables.Add Item:=.Name & .Connect, Key:=.Name
    End If
    End If
    End With
    Set fGetLinkedTables = collTables
    Set collTables = Nothing
    Set tdf = Nothing
    Set db = Nothing
    End Function

    Function fParsePath(strIn As String) As String
    If Left$(strIn, 4) <> "ODBC" Then
    fParsePath = Right(strIn, Len(strIn) _
    - (InStr(1, strIn, "DATABASE=") + 8))
    fParsePath = strIn
    End If
    End Function

    Function fParseTable(strIn As String) As String
    fParseTable = Left$(strIn, InStr(1, strIn, ";") - 1)
    End Function

    I placed this on the on open event of the main form that comes up at startup. However, I get the following error:

    The expression On Open you entered as the event property setting producted the following error:
    User-defined type not defined

    Could someone please tell me what I did wrong here?

    I am wondering if by not re-linking the tables, has produced this corruption in the BE. It seems to bring up the data okay.

    Any help is appreciated,

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    i am concerned with the following line
    Dim tdf As TableDef, db As DATABASE

    there is no DATABASE (all caps) object

    at the very top of your code
    Option Explicit

    then compile many errors do you see?

    currently using SS 2008R2

Posting Permissions

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