Urgent: programming linking tables

[writetoashishp]

I want to install 2 files (Frontend mdb and backend mdb) in user environment.

Problem when deploying is, in the frontend mdb, i have to inject (configure) the backend mdb path.
How to do that programaticaly, i mean to use any module/macro or what?

I will put these things in my setup (either a vbscript) or steps to execute in mdb when installing.

Things i need:
1. What is the code to modify "MSysObjects" table?
2. Where should i put the code?

Thanks for providing nice place.

 

[MSAccessRookie]

I want to install 2 files (Frontend mdb and backend mdb) in user environment.

Problem when deploying is, in the frontend mdb, i have to inject (configure) the backend mdb path.
How to do that programaticaly, i mean to use any module/macro or what?

I will put these things in my setup (either a vbscript) or steps to execute in mdb when installing.

Things i need:
1. What is the code to modify "MSysObjects" table?
2. Where should i put the code?

Thanks for providing nice place.

I am not sure why you need to go to any trouble here when you can use the Linked Table Manager to assign the Links for you. In the Database Page that views the Tables, right clicking on any linked table will provide a Menu tith the Linked Table Manager as an option. Choosing that option will allow you to define the links whereever you need them to be.

 

[KenHigg]

You need to paint the full picture here. I you want to re-link once when you deploy the db then I would suggest an admin form that runs the re-link code. This is a good option because some how the db will have to know where the BE resides and you really don't want end users having to tell the db where to find the tables. So you copy the FE on the client and open the admin for that has a button that does the relink.

Some would argue that if you will have to run the re-link routine from a button you may as well just manually re-link to the BE tables...

 

[Rabbie]

I want to install 2 files (Frontend mdb and backend mdb) in user environment.

Problem when deploying is, in the frontend mdb, i have to inject (configure) the backend mdb path.
How to do that programaticaly, i mean to use any module/macro or what?

I will put these things in my setup (either a vbscript) or steps to execute in mdb when installing.

Things i need:
1. What is the code to modify "MSysObjects" table?
2. Where should i put the code?

Thanks for providing nice place.

The best way is to use UNC type names to link the FE database to the BE. Then it should work correctly from all pcs on the network. There should be a copy of the FE for each user and just one copy of the BE

This should avoid the need for you to have any special code.

You shouldn't modify any system tables unless you know what you are doing

 

[KenHigg]

The best way is to use UNC type names to link the FE database to the BE. Then it should work correctly from all pcs on the network. There should be a copy of the FE for each user and just one copy of the BE

This should avoid the need for you to have any special code.

You shouldn't modify any system tables unless you know what you are doing

The UNC issue is only valid in specific scenarios. Like when users may have different mapping to a share folder. Say like when user a has the network share folder mapped to drive e and the next user has it mapped to f. If all users map exactly the same then UNC is useless. How you came to the conclusion that using UNC would eliminate the need to have any 're-linking' code is a mystery to me when we don't know the big picture.

In order to suggest a deployment 'plan' more information is required.

 

[Dennisk]

here is the refresh code I use

Private Sub RefreshLinks()
' DAO Data Library
    
  Dim tblDefs As TableDefs
  Dim tblDef As TableDef
  Dim strConnection As String
    
  Set tblDefs = CurrentDb.TableDefs
    
' LIVE Production Database Link
  strConnection = "\\xxxxx\DATA\HDR\Shared\xxx\xxx\Smoke_BE.mdb"

  For Each tblDef In tblDefs
    If tblDef.Connect <> "" Then
      tblDef.Connect = ";DATABASE=" & strConnection
      'Debug.Print tDef.Name
      tblDef.RefreshLink
    End If
  Next tblDef
  MsgBox "Refresh Complete"
  
End Sub

 

[KenHigg]

I use a similar routine but store the connection strings and table names in tables so that I don't have to poke around in code if something changes. The I use a form to admin them...

 

[writetoashishp]

I had put this code in a new Module in access mdb, and when i execute this code in access (immediate window), it says:
"Compile error: Argument not optional"
error.

Where should i write this code in access, so that i can link it?
Can u put the details here?

waiting for reply...

 

[DCrake]

What line of code generated the error?

 

[writetoashishp]

What line of code generated the error?

How do i know, i executed the function in "Immediate window" of the editor in access.

I have written code in modules section of mdb.

 

[DCrake]

if you place a breakpoint on the first line of the function and step through it using F8 it should tell you.

 

[writetoashishp]

Thanks all,
I solved the problem, was typo-mistake in my code.