Re-linking tables via a procedure... 
Author Message
 Re-linking tables via a procedure...

1st Question:

I have a database which is split into 1 database containing the tables and 1
containing forms, modules etc.

Occassionally I need to take a copy home to work on and have to manually
relink the tables from the first db so they work in their new location
(Cdrive instead of S drive).

Can I write some code to do this by pressing a hidden keypress - e.g.
CTRL-ALT-R??

I guess it's a matter of:

- deleting all the currently linked tables
- finding the current path of the Forms database,
- using that path to reference the Tables database
- relink all the tables from that database unless the table name begins with
'prv' for private?

2nd question:

I heard once that it's best to base a form on a query than on a table - does
that make any sense or is it something older versions of Access could use to
improve speed?

Thanks,

Nick



Fri, 08 Nov 2002 03:00:00 GMT  
 Re-linking tables via a procedure...
I use this code on an autoexec macro. Might be of use.
FileName = "NHC Health-info 3.mdb"
CommDataFile = "NHC Health-info 3 Data.mdb"

Set dbsCurrent = CurrentDb
PathName = Left(dbsCurrent.Name, Len(dbsCurrent.Name) - Len(FileName))
Set tdfLinked = dbsCurrent.TableDefs("age 5yr")
LinkFile = ";pwd=simple;DATABASE=" & PathName & CommDataFile

If tdfLinked.Connect = LinkFile Then
Else
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Age 5yr plus")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Allergies")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Chronic People")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Chronics")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("client_details")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Clin")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Clinics")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Communities")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Conditions")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
    Set tdfLinked = dbsCurrent.TableDefs("Disabilities")
    tdfLinked.Connect = LinkFile
    tdfLinked.RefreshLink
etc

Quote:
> 1st Question:

> I have a database which is split into 1 database containing the tables and
1
> containing forms, modules etc.

> Occassionally I need to take a copy home to work on and have to manually
> relink the tables from the first db so they work in their new location
> (Cdrive instead of S drive).

> Can I write some code to do this by pressing a hidden keypress - e.g.
> CTRL-ALT-R??

> I guess it's a matter of:

> - deleting all the currently linked tables
> - finding the current path of the Forms database,
> - using that path to reference the Tables database
> - relink all the tables from that database unless the table name begins
with
> 'prv' for private?

> 2nd question:

> I heard once that it's best to base a form on a query than on a table -
does
> that make any sense or is it something older versions of Access could use
to
> improve speed?

> Thanks,

> Nick



Fri, 08 Nov 2002 03:00:00 GMT  
 Re-linking tables via a procedure...
The below function will ask you for a path, then relink the tables.

Dom's suggestion will work, but since table names are hardcoded into the macro,
anytime you add a new table to the database, you will need to update the macro.

The below function will loop through all of the linked tables, and update the
link to the database you specify.

Good luck.
David

Public Function dmlLinkTables() As Integer

On Error GoTo dmlLinkTables_Err:

Dim D As Database
Dim tblLinked As TableDef
Dim strDBPath As String
Dim strMsg As String
Dim strDef As String

    'Default value for input box
    strDef = "C:\my documents\MyDatabase.mdb"

    'Message to display to user
     strMsg = "Enter the full path and file name of the" & vbCrLf & _
            "back-end database, including the file extension."

    'Call the input box function      
    strDBPath = InputBox(strMsg, "Re-link Tables", strDef)

    If IsNull(strDBPath) Or Len(strDBPath) = 0 Then GoTo dmlLinkTables_Exit:

DoCmd.Hourglass True

    If Not Len(Dir(strDBPath)) = 0 Then
        Set D = CurrentDb()

        For Each tblLinked In D.TableDefs
            If tblLinked.Connect <> "" Then
            'MsgBox tblLinked.Connect
                tblLinked.Connect = ";DATABASE=" & strDBPath
                tblLinked.RefreshLink
            End If
        Next

        dmlLinkTables = True

    Else
        strMsg = "File " & strDBPath & " not found." & vbCrLf & vbCrLf &
"Procedure canceled."
        MsgBox strMsg, vbCritical, "Re-Link Not Successful"
        dmlLinkTables = False
    End If

dmlLinkTables_Exit:
    DoCmd.Hourglass False
    Exit Function

dmlLinkTables_Err:
    MsgBox Error$ & " Reported by function dmlLinkTables"
    dmlLinkTables = False
    Resume dmlLinkTables_Exit

End Function

Quote:
>Subject: Re-linking tables via a procedure...

>Date: 5/22/00 3:22 AM Pacific Daylight Time

>1st Question:

>I have a database which is split into 1 database containing the tables and 1
>containing forms, modules etc.

>Occassionally I need to take a copy home to work on and have to manually
>relink the tables from the first db so they work in their new location
>(Cdrive instead of S drive).

>Can I write some code to do this by pressing a hidden keypress - e.g.
>CTRL-ALT-R??

>I guess it's a matter of:

>- deleting all the currently linked tables
>- finding the current path of the Forms database,
>- using that path to reference the Tables database
>- relink all the tables from that database unless the table name begins with
>'prv' for private?

>2nd question:

>I heard once that it's best to base a form on a query than on a table - does
>that make any sense or is it something older versions of Access could use to
>improve speed?

>Thanks,

>Nick



Sat, 16 Nov 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Relinking tables via code

2. To link procedure and table in visual linking...

3. Updating/relinking an ODBC source via VBA

4. Linked dbaseIV-file in ACCESS to be relinked by VB

5. Linked dbaseIV-file in ACCESS to be relinked by VB

6. Updating/relinking an ODBC source via VBA

7. Linked dbaseIV-file in ACCESS to be relinked by VB

8. Linked dbaseIV-file in ACCESS to be relinked by VB

9. Newbie Help Please: Update table via procedure

10. Help w/ procedure to IMPORT ODBC-Linked tables

11. Can i link Stored Procedure/Table/View

12. Dropping Tables Linked Via code

 

 
Powered by phpBB® Forum Software