Manually changing location of a linked table?

[accessdan]

Hi everyone,

I have sent out a copy of an Access DB frontend to a customer. Now the
frontend gets the data from a linked table. However the customer wants the
daily update of the DB (linked table) to be stored on a server. From my end,
I don't know how to change the location manually. I've tried to use the
Linked Table Manager but the location that I entered (supplied by my
customer) in the browse window does not exist here at my company. I cannot
travel to see my customer because they are overseas.

Does anyone have any ideas on this?

Thanks in advance
Dan.

 

[LittleSmudge]

There is a way to give the front end the path to the backend and get it to link ALL od the table from the backend to itself.

I have the code at home and I'll look it up over the weekend for you.

If your need is urgent the have a look back at the history in one of the Tek-Tips Access boards - because all of the code is here within the last 2 months for sure.


G LS

 

[accessdan]

Hi LittleSmudge,

I'll be anxiously awaiting your post! i've looked over the last 2 months worth of posts but couldn't seem to get the solution I've been looking for.

Thanks in advance,
Dan.

 

[LittleSmudge]

Dan,
Delay in responding to your request due to baby daughter being ill all weekend.

I've not forgotten. Expect something useful tomorrow.


G LS

 

[accessdan]

Smudge,

Very nice of you to let me know!

Thankyou muchly
Dan.

 

[LittleSmudge]

Okay Dan so here it comes.. ..

This first post contains the code behind two buttons that I have on an Admin switchboard.
These buttons are only visible to Users who have Administrator Group membership - using MS Access security model.


Message on the cmdDeleteLinks button is
“Delete existing links to DataStore tables Use this BEFORE distributing updates from Development to User locations.”
Private Sub cmdDeleteLinks_Click()
If MsgBox("Are you REALLY sure?", vbYesNo, "Just checking.&quot = vbYes Then
Call DeleteTableLinks ' Sub in mdlLinkedTable
End If
End Sub

Message on the cmdDeleteAndRelink button is
“Delete existing links to DataStore tables and reconnect Or just Reconnect if no links exist.”
Private Sub cmdDeleteAndRelink_Click()
If MsgBox("Are you REALLY sure?", vbYesNo, "Just checking.&quot = vbYes Then
Call DeleteTableLinks ' Sub in mdlLinkedTable
Call RemakeTableLinks ' Sub in mdlLinkedTable
End If
End Sub


In the next post I will place the code that I have resident in a global module ( makes porting to other applications easier. )


G LS

 

[LittleSmudge]

Continues from the post above.

This code in a Global module called mdlLinkedTable
Function GetLinkedConnectStrings()
' This is just a useful Function that prints all the tables to the Debug Window
' to work round the bug in the MSAccess Linked Table Manager
' which cannot scroll Right to view long \\Server\Path\Names
On Error GoTo Err_GetLinkedConnectStrings

Dim db As Database
Dim doc As Document
Dim tbf As TableDef

DoCmd.Hourglass True
Set db = CurrentDb

For Each tbf In db.TableDefs
DoEvents
If Not Left(tbf.Name, 4) = "MSys" Then
'ignore system tables
Debug.Print tbf.Name & "; " & tbf.Connect
End If
Next tbf

Exit_GetLinkedConnectStrings:
On Error Resume Next
DoCmd.Hourglass False
db.Close
Set db = Nothing

Exit Function

Err_GetLinkedConnectStrings:
Select Case Err
Case 0 'insert Errors you wish to ignore here
Resume Next
Case 3011 'object not found
Resume Next
Case 3045 'database already in use
Beep
MsgBox "The database is in use by someone else. " _
& "Go and evict them and try again.", , "You are not alone."
Case Else 'All other errors will trap
Beep
MsgBox "Error deleting tables.@" & Err.Number & "; " & Err.Description
Resume Exit_GetLinkedConnectStrings
End Select
Resume 0 'FOR TROUBLESHOOTING
End Function


Public Sub DeleteTableLinks(Optional strConnectString As String = "&quot
'If strConnectString is omited all links will be removed
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If tdf.Connect <> &quot;&quot; Then 'Check for linked tables
'Check for pointed links
If InStr(1, tdf.Connect, strConnectString, vbTextCompare) > 0 Then
'Removing links
DoCmd.DeleteObject acTable, tdf.Name
DoCmd.Echo True, &quot;Progress: Deleting link to table &quot; & tdf.Name
End If
End If
Next tdf
End Sub



Public Sub RemakeTableLinks()
On Error GoTo Err_RemakeTableLinks
Dim dbs As Database
Dim tdf As TableDef
Dim strLinkSourceDB As String
Dim tdfCount As Long
Dim intCount As Long

' Need to go get database from use
Select Case MsgBox(&quot;If you are connecting to the XXXXX Server, click on Yes&quot; & vbLf _
& &quot;If you are connecting to the YYYYY Server, click on No&quot; _
, vbYesNoCancel, &quot;Get Data From User&quot
Case Is = vbYes
strLinkSourceDB = &quot;\\XXXSERVER\DatabasePath\ManagementDatastore.mdb&quot;
Case Is = vbNo
strLinkSourceDB = &quot;\\YYYSERVER\DatabasePath\ManagementDatastore.mdb&quot;
Case Else
Select Case MsgBox(&quot;If you are connecting to the DataStore Design Master, click on Yes&quot; & vbLf _
& &quot;If you are connecting to the \\Special Development Server, click on No&quot; _
, vbYesNoCancel, &quot;Get Data From User&quot
Case Is = vbYes
strLinkSourceDB = &quot;\\ XXXSERVER\DatabasePath\ManagementDataMaster.mdb&quot;
Case Is = vbNo
strLinkSourceDB = &quot;\\DevSERVER\DatabasePath\ManagementDataMaster.mdb&quot;
Case Else
MsgBox &quot;Option to Terminate selected.&quot;, , &quot;No Link to Make&quot;
Exit Sub
End Select
End Select

'Open source DB
Set dbs = OpenDatabase(strLinkSourceDB)

'Counting tables in the source DB
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> &quot;MSys&quot; Then
'Do not link to the System tables
tdfCount = tdfCount + 1
End If
Next tdf
'Check all tables in source DB (dbs)
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> &quot;MSys&quot; Then
'Do not link to the System tables
'Creating links
intCount = intCount + 1
DoCmd.TransferDatabase acLink, &quot;Microsoft Access&quot;, strLinkSourceDB, acTable, tdf.Name, tdf.Name
DoCmd.Echo False, &quot;Progress: Linking table &quot; & intCount & &quot; of &quot; & tdfCount
End If
Next tdf
'Close source DB
dbs.Close
Set dbs = Nothing

Exit_RemakeTableLinks:
DoCmd.Echo True
Exit Sub

Err_RemakeTableLinks:
MsgBox Err.Description, , &quot;mdlLinkedTable, Sub RemakeTableLinks &quot; & Err.Number
Resume Exit_RemakeTableLinks
End Sub

 

[LittleSmudge]

As you'll see Dan there are some bits of the code in the global module that will need changing. The code I've posted only allows the user to link to BackEnd databases of predetermined name in predefined locations. You might need to replace this with an Input box or a CommonDialog box to get the \\Server\Path\Name.mdb of the target BackEnd if you want to give the user that flexability.



'ope-all-that-'elps.

G LS

 

[LittleSmudge]

One other point I ment to make earlier :-

Although I have the code running on an Access 2000 database, the code above uses DAO so you'll need the DAO 3.6 reference. This is almost certainly included already if you are running Access 97. If you are running A2k then you might need to add the reference in order to get it to compile.


Graham

 

[accessdan]

Thanks Graham!

I'll give that a go now... thanks for helping me out

Cheers
Dan.