Linked Table Manager - failes on more than 3 tables at a time (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 13:17
Joined
Oct 22, 2009
Messages
2,803
Access 2007 - linking Access 2007 Be on network or C:\ drive folder

Linked Table Manager, Always Prompt for New Location, Select All
Choose a Be (back end tables) 2007 Access db

Problem: each and every table asks for the user to re-select the Access 2007 Be path again, and again, and again.

However: Instead of Select All, check 3 to 8 tables at a time and it will update just the 3 to 8 all at once (as it normally should).

Earlier this week, it behaved properly dozens of times and updated all tables (selct All) with a single click.
Not using ODBC, just directly linking Access 2007 to Access 2007.

Started on the network. Then copied both the front end and backend to the C:\ drive root folder just to make sure it is not network latency or something like that. Same exact issue.
Did compact and repair the Front End database.
Did compact and repair the Back End database.

I might end up writing some code to re-link this.
:confused:
But, wondered if anyone has any ideas why this happens, how to prevent it, or how to best recover?
 

SOS

Registered Lunatic
Local time
Today, 12:17
Joined
Aug 27, 2008
Messages
3,517
Are you linked to only one backend? If you have accidentally have linked to a test version and have other links to a live version, it will do that. We have more than one database backend for some of our databases and you have to ensure you don't try to link more than one at a time or else it will come up with that. In fact, if it comes up with that dialog again after I've selected my database to link to then I know that I accidentally selected at least one table that was linked to another location and I'll cancel, go find it and then remove it from the tables I am trying to link at the time, let it link the one location and then go deal with that other.
 

Rx_

Nothing In Moderation
Local time
Today, 13:17
Joined
Oct 22, 2009
Messages
2,803
As usual, you are right as rain.
I wish Microsoft would enhance the Linked Table Manager to put the table name in a column so that the link column would agline.

To answer your question, this database being maintained only has a single _be (back end) database. The relinks are always from a production to a test in bulk.
Looking at the details, the last dozen tables did not relink.
When relinking the 12 tables to the network location, it took just over a full minute!
That probably indicates some bad network latency.
But, there was not an apparent error on Access part.

As you say, after all the tables were linked to exactly the same location, they were re-linked to a _be on the C:\ drive. (both test db and data db located on the C:\ drive) The 95 tables only took a few seconds to re-link.

For databases that use links to multiple locations, my preference is to use vba to relink. Since this was a quick maintenance job, I didn't bother with that. Besides, it just worked with out a problem all week, dozens of times.

Thanks for the tip. Would not have looked at the details that closely since it is Friday afternoon.
 

RenaG

Registered User.
Local time
Today, 13:17
Joined
Mar 29, 2011
Messages
166
I just had this happen to me and once again was reminded what a wonderful resource this forum is!! I found the reason for this strange behavior and was able to work around it in just a few minutes!! So I have a generic THANK YOU to everybody who shares their time posting solutions and explainations! You never know when it is going to help somebody down the road.

But I have a question. I have done this re-linking thing on the same db several times now. It is always the same files residing in the same location. And always before today I could run it in one step. However, what caused the problem this time was that some of the files referenced the drive letter and some referenced the UNC. How in the world did these references get changed? How can I change them so that they are all the same and future linking can be done in one step? I just tried going into the test FE and linking the files that reference the UNC to the drive letter but that didn't work.

TIA!
~RLG
 

Rx_

Nothing In Moderation
Local time
Today, 13:17
Joined
Oct 22, 2009
Messages
2,803
A personal thanks (thumbs up in the bottom of a post) will go a long way for help the next time.

You caught me at the very end of the day, this is not the best code
This is a standard code found on several old sites. It should give you some ideas:
Code:
Function Reconnect ()
[COLOR=#008000]'**************************************************************
'*     START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
'*     AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
'*     WHEN THE 'DATA.MDB' (backend)  AND 'PROGRAM.MDB' (frontend)
'*     ARE IN THE SAME DIRECTORY!
************************************************************[/COLOR]
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer

Set db = dbengine.Workspaces(0).Databases(0)
[COLOR=#008000]'*************************************************************
'*                     ID THE PATH                    *
'*************************************************************[/COLOR]

For i = Len(db.name) To 1 Step -1
    If Mid(db.name, i, 1) = Chr(92) Then
        path = Mid(db.name, 1, i)
        [COLOR=#008000]'MsgBox (path)[/COLOR]
        Exit For
    End If
Next
[COLOR=#008000]'*************************************************************
'*       CONNECT  AGAIN  AFTER CHANGING THE PATH     *
'*************************************************************[/COLOR]

For i = 0 To db.tabledefs.count - 1
    If db.tabledefs(i).connect <> " " Then
        source = Mid(db.tabledefs(i).connect, 11)
[COLOR=#008000]        'Debug.Print source[/COLOR]
        For j = Len(source) To 1 Step -1
            If Mid(source, j, 1) = Chr(92) Then
               dbsource = Mid(source, j + 1, Len(source))
               source = Mid(source, 1, j)
                   If source <> path Then
                        db.tabledefs(i).connect = ";Database=" + path + dbsource
                        db.tabledefs(i).RefreshLink
                        [COLOR=#008000]'Debug.Print ";Database=" + path + dbsource[/COLOR]
                    End If
                Exit For
            End If
         Next
    End If
Next
End Function
If you have a naming convention for tables - this could be modified so that tables beginning with tblCost could be filtered and connected to one databaes while those beginning with tblSales are filtered and connected to another database.
db.tabledefs(i).connect = ";Database=" + path + dbsource
Or you could just get rid of the vairables and hard code everything.


There may be a reason to wipe out the linked tables and then use code to specifically re-link specific named tables. Once in a while, this has solved some pesky ghost in the machine.
Code:
Private Sub CommandDeleteAllLinkedTables_Click()
    Dim db As DAO.Database
    Dim count As Integer
    Set db = CurrentDb()
    count = 1
    For count = 0 To db.TableDefs.count - 1
        If Left(db.TableDefs(count).Connect, 10) = ";DATABASE=" Then
            DoCmd.DeleteObject acTable, db.TableDefs(count).Name
        End If
    Next
    MsgBox "All Linked Tables Destroyed", vbOkOnly, "Time to relink"  
End Sub

Maybe that will give you some ideas on what to search for.
 

Dhamdard

Dad Mohammad Hamdard
Local time
Today, 23:47
Joined
Nov 19, 2010
Messages
103
Let me share my lessons learned using database on network. When we split database, the last thing access asks is the file name for the back-end database. This is very critical step. If you provide the whole path here, then your front-end will never ask you again for linking tables again and again unless the back-end database is relocated.

For example;
\\178.123.5\Share-Drive\DB_be.acdb (This will be saved with the front end database. On each open, it will look through this path and linked front end with back end)
 

RenaG

Registered User.
Local time
Today, 13:17
Joined
Mar 29, 2011
Messages
166
Good morning!

Hi Bob ~ this is the same db you and I have had many discussions about - specifically me trying to understand the ins-n-outs of splitting, linking, importing, etc. I started with a whole db and then after it was mostly finished and in production, split it. I don't remember how I indicated the path for the test side but I do know that I selected the UNC for production. This is a fairly new db and I have only added a couple of tables for each modification. Anyway, the production side is fine and for the life of me, I can't think, now, why I was trying to link the tables on the test side. I have a sinus headache and not thinking too clearly this morning but it looks like I had brainfade at the end of the day and was doing something that wasn't necessary. I hate it when that happens :eek:. But I learned something so I guess all is not lost.

Hi Rx ~ thanks for the reminder for giving a thumbs up! Thanks also for sharing your code. It is way over my head at this point in my Access career but I am going to save it for future use!

Hi dhamdard ~ thanks for sharing. I think this is what I did so I am still wondering how the path naming convention changed. It would have been done all at once and the only paths that show the drive letter are the first 12 files (all of which were in the original db design, not new ones added later). It's a mystery!

Later,
~RLG
 

Rx_

Nothing In Moderation
Local time
Today, 13:17
Joined
Oct 22, 2009
Messages
2,803
Just a reminder for those using Microsoft SQL Server.
There are several options for linking tables to SQL Server not covered here.
In SQL Server, there will often be a View in SQL Server to link rather than directly to a table.
And, there are many more considerations/options.
Check out http://www.access-programmers.co.uk/forums/showthread.php?t=213176
 

elpenguino

New member
Local time
Tomorrow, 08:17
Joined
Apr 28, 2015
Messages
4
Thanks to the earlier posters. Their trail of breadcrumbs led me out of the access forest of despair :)

My problem was that most of my tables had a path like this:

\\ servername \ folder \ file. mdb
But a couple of the tables had names like this:

\\ servername.domain.co.nz \ folder \ file.mdb

And that upset the linked table manager. Even though both paths point to the same machine and back end file. Go figure..

Any hoo, onwards and upwards !
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:17
Joined
Jan 20, 2009
Messages
12,849
But a couple of the tables had names like this:

\\ servername.domain.co.nz \ folder \ file.mdb

Access does not usually work well when data files are not on the same LAN as the front end. This is because pieces of the file are transmitted to the front end machine where they are processed.

Can result in corruption when there is any glitch in the communication.

Better to use a database server for the back end. In this structure the command to change the data is sent by the front end and the processing done on the database server.

Better still, use a terminal client like Remote Desktop so both the FE and BE are at the server.
 

Cronk

Registered User.
Local time
Tomorrow, 06:17
Joined
Jul 4, 2013
Messages
2,770
I use a relinking function regularly to change the links from a test Back end to production BE, using code similar to that posted previously in this thread.

It can speed up the relinking process, especially when there are many files, to open a recordset based on the first relinked table for the duration of the linking process.

FWIW
 

elpenguino

New member
Local time
Tomorrow, 08:17
Joined
Apr 28, 2015
Messages
4
I think I am going to play with that idea thanks - I give my users a copy to use when they travel. I have to re-link the tables to the travellers back end and I have to disable stuff like alert emails.

If I can just flip a switch, I will have more time for watching cat videos :)
 

Users who are viewing this thread

Top Bottom