Connecting refreshing links between databases

[exaccess]

Hi Experts,
I have a club member registration application consisting of a program database and a data database. They reside in the same directory. There is an autoexec macro which runs at the beginning. This macro has to find the DATAdb and make sure that the required tables are linked (Not all tables). If it cannot find the DATAdb and then has to make a call to the user to use a different procedure.
Now to find the DATAdb I have used the following code that works. The function GetPathAndName parses the string db.name and outputs the path and name of the PROGdb. Up to here no problems.

GetPathAndName db.Name, FrontPath, dName

Then I use the following code to get the fullpath of the last database used and thereby find the path and name of the last DATAdb. The path of PROGdb is used in place of the existing path of DATAdb to check whether the file exists.

Dim rs As Recordset, cPath As String, cName As String
Set rs = CurrentDb.OpenRecordset("SELECT Database, Type " & _
                            "FROM MSysObjects " & _
                            "WHERE ((MsysObjects.Type) = 6) AND ((MsysObjects.Name) = 'MembersTbl') " & _
                            "ORDER BY MsysObjects.DateUpdate DESC;")
If rs.RecordCount = 0 Then
    MsgBox "There is no database linked in the folder" & Chr(13) & _
        "Use Change Data button", , GC_Title
    Exit Function
End If
rs.MoveFirst
While Not rs.EOF
    Debug.Print "rs!DataBase="; rs!Database
    GetPathAndName rs!Database, cPath, cName
    If FileExists(FrontPath + cName) Then GoTo EndLoop
    rs.MoveNext
Wend
 MsgBox "Data file can not be found, Use CHANGE DATA button to give data file name", , GC_Title
    Exit Function
EndLoop:
    BaseName = FrontPath + cName            'NEW FULLPATH OF DATADB
    Debug.Print "BaseName="; BaseName
'-----------------------------------------------------------------------------------

As the final step I use the table definitions to refresh links that already exist and connect tables that are not linked The code is below. But I can not get it to work. It links 7 tables instead of 16 and when tested in different folder says invalid operation and so on. There is fundamental error some place but I could not figure it out. Help please.

On Error GoTo Error_Linking
Debug.Print "TableDefs.Count="; db.TableDefs.Count
For I = 0 To db.TableDefs.Count - 1
    Debug.Print "TableDefs("; I; ").Connect="; db.TableDefs(I).Connect
    Debug.Print "TableDefs("; I; ").Name="; db.TableDefs(I).Name
    Debug.Print "TableDefs("; I; ").SourceTableName="; db.TableDefs(I).SourceTableName
    tConn = Nz(db.TableDefs(I).Connect, "")     'path and Name of connection database
    tName = Nz(db.TableDefs(I).Name, "")        'Linked table name
    tSour = Nz(db.TableDefs(I).SourceTableName, "") 'Source table name
'------------------------------------------------------------------------------------
' see if the table is not a system or temporary table or null
' if it is originally connected to backend refreshlink
'------------------------------------------------------------------------------------
    Debug.Print "tName="; tName
    If (((Not IsNull(tName)) And (tName <> "") And (Left(tName, 4) <> "MSys") And _
        (Left(tName, 4) <> "~TMP")) Or tSour <> "") Then
        If ((BackPath) = tConn) Then
            Debug.Print "2"
            db.TableDefs(I).RefreshLink
            J1 = J1 + 1
        Else
            Debug.Print "3"
            db.TableDefs(I).Connect = BackPath + bName
            J2 = J2 + 1
        End If
    End If
GoNext:
Next

 

[spikepl]

Do not provide snippets of code but full subroutines
Disable error handling before asking for help
Identify which line of code fails and on what error (which has which text)

 

[gemma-the-husky]

fwiw, I don't refresh tables. I drop all tables, and rebuild the links completely, and never have a problem.

a) you need error handling for this
b) it may connect all your tables. you may need to refresh the tables after the process completes.
are you displaying the J1 and J2 totals anywhere?
c) and I don't use the msysobjects table. I use the tabledefs collection

 

[exaccess]

fwiw, I don't refresh tables. I drop all tables, and rebuild the links completely, and never have a problem.

a) you need error handling for this
b) it may connect all your tables. you may need to refresh the tables after the process completes.
are you displaying the J1 and J2 totals anywhere?
c) and I don't use the msysobjects table. I use the tabledefs collection

Starting by the last question:
If I do not use that table instead the tableDefs how can I get the name of the last used backend database?
J1 and J2 are used to give feedback to the user about the number of tables linked or links refreshed.
I am trying to avoid all tables.
When you say drop tables do you mean drop the links. Otherwise the data will be gone.

 

[exaccess]

Do not provide snippets of code but full subroutines
Disable error handling before asking for help
Identify which line of code fails and on what error (which has which text)

I have placed almost all the code there. There are only a few statements left which are irrelevant. But if I copy the whole module there are lots of comments between the lines which are both somewhat company related and not necessarily for public eyes.

I would appreciate if at least somebody can refer o a source that may lead to the solution.

 

[gemma-the-husky]

Starting by the last question:
If I do not use that table instead the tableDefs how can I get the name of the last used backend database?
J1 and J2 are used to give feedback to the user about the number of tables linked or links refreshed.
I am trying to avoid all tables.
When you say drop tables do you mean drop the links. Otherwise the data will be gone.

1. the last used backend will be in a given tables "connect" string

currentdb.tabledefs("sometable").comect

if this is what you expect it to be, the tables are properly connected. If not they need reconnecting. If connect is "" then it's a local table, not linked, and you can ignore it.

fwiw, I actually store the details of the inked tables in a local table

I prefer to avoid the msys tables. I can get everything I need without them, and I just leave them alone.


2, J1 and J2. I meant that you seem to be incrementing these, but not displaying then at the end, so your job MAY actually have completed normally.


3. Yes drop the linked tables. This will do it for a given table

currentdb.tabledefs.delete "sometable"

 

[gemma-the-husky]

Ok

You need some error handling, but this looks wrong in the J2 section

db.TableDefs(I).Connect = BackPath + bName

the connect string SHOULD look like this, with the leading semicolon

;database=fullpathtobackenddatabase

but of itself this will not connect a table in that database. Look at help for "createtabledef"

 

[exaccess]

Thanks. After submitting the responses I found out about the string connnect=;database and also discovered that two imported text files looked like as if they were connected tables. So the problem is solved.