Modifying linked table targets through VBA

Question

Hi there,

I am looking for information about possibly modifying the targets of my linked tables through VBA.

I have two ways to access our database: locally (from desktop front-end) and remotely (for use on telework or for remote employees, through Citrix).  Part of the database design involves a number of temporary tables that are populated/cleared on the fly during certain report processes.  Currently, I put those tables in a database on the users' personal network drives (K drive), because I needed a drive that was available both for local and remote users.

This creates performance problems for desktop users.  The generation of the temporary tables involves a lot of data processing and storage, so sending this data between the desktop computer and the K drive slows things down a lot.  If I could have the desktop users' temporary tables point to something locally on their desktop, that would speed things up a lot for most of the users.

What I'd like to do is have VBA change the target of the temporary table, after determining if the session is local or remote.  I already have logic (for other uses) that determines if the session is local or remote.  But I just need some insight into the best way to programmatically change the target of the temporary table.

Appreciate any assistance.

Thanks!

Emily

Answer 1

A common or often used approach is to create a temp accDB file in in the same folder as the front end (since all users have their own front end, right???).

You thus create the temp accDB, link some tables to the temp DB.

A sample database that achieves this result is here:

http://www.granite.ab.ca/access/temptables.htm

I also suggest that a copy of the table structure be placed in the front end, and upon creating of the temp ACCDB, then you execute a transferDatabase command to popular the temp ACCDB with the required tables, and then link to such tables. That way you don’t write code to “maintain” or have to create the temp table but simply maintain the blank table as part of the application in the front end.

As for table re-linking code - well that quite much part of any application that been split, since you often have to re-link the database during testing and development.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

You can try the following procedure:

Function LinkTable(TableName As String, FilePath As String) As Boolean

    On Error GoTo Err_Process

    Dim dbs1 As DAO.Database

    Dim tdf1 As TableDef

    Dim blnReturn As Boolean

    Dim strMsg As String

    blnReturn = False

    Set dbs1 = CurrentDb

    Set tdf1 = dbs1.TableDefs(TableName)

    tdf1.Connect = ";DATABASE=" & FilePath

    tdf1.RefreshLink

    blnReturn = True

Exit_Process:

    Set dbs1 = Nothing

    Set tdf1 = Nothing

    LinkTable = blnReturn

    Exit Function

Err_Process:

    Select Case Err

    Case 3265

        strMsg = "Could not connect table: " & TableName & vbCrLf & vbCrLf

        strMsg = strMsg & "Item not found in table collection."

        MsgBox strMsg, vbExclamation, "Error"

    Case 3011, 3078

        '3011 The MS Jet Database Engine could not find the object "Object Name."

        '3078 The MS Jet Database Engine cannot find the input table or query "Object Name."

        strMsg = "Could not connect table: " & TableName & vbCrLf & vbCrLf

        strMsg = strMsg & "Item not found in File: " & FilePath & "."

        MsgBox strMsg, vbExclamation, "Error"

    Case 3024

        'Err 3024 Can't locate file...

        strMsg = "Could not connect table: " & TableName & vbCrLf & vbCrLf

        strMsg = strMsg & "Invalid file path: " & FilePath & "."

        MsgBox strMsg, vbExclamation, "Error"

    Case Else

        'Your error handler

    End Select

    Resume Exit_Process

End Function

Answer 3

You can Relink the tables when you load the database. For that there are multiple examples of relinking code on the WEB. One example is here:

http://www.jstreettech.com/downloads.aspx

Another is here:

http://access.mvps.org/access/tables/tbl0009.htm

Answer 4

Sorry, I used poor wording in my earlier post.  The tables are not "created" on the fly; they are just populated/cleared on the fly.  I've edited to make that clearer.

The temporary tables themselves reside in a database that already exists currently on their personal network K drive.  I created that database and its temporary tables "by hand."

What I want to do is to have a copy of this database containing the temporary tables in two different locations.  First I will have the current K drive location, which I will keep so that users connecting remotely via Citrix can access the temporary tables.  Second, I will have a new copy of the database in a local location (say at C:\MyDatabase).

When a session starts, I have code that will determine if the user is local or remote.  If they are local, then I'd like the VBA code to link the temporary tables to the C drive location database.  If they are remote, then I'd like the code to link the temporary tables to the K drive location.

Thanks!

Emily

Answer 5

Can you show us the code you use to create the temp tables?