Linked Table Location 3

[tpremore]

Is there any way to link to a table with a relative path?
For example, when I have a link from c:\mydir\main.mdb to tables in c:\mydir\data.mdb the main database will not work properly unless data.mdb is in c:\mydir. What if I wanted to move both mdb files to c:\thisOtherDir? I would have to relink all the tables again??? There has got to be a better way. I may want to package and create an install procedure for this database package, and it would be nice to allow the user the flexibility to install somewhere besides c:\mydir!

Thanks for any help you can provide!
Travis

 

[Aivars]

Hi, Travis!

Of course you need to relink all tables but you can do it in one moment by using function.
Here is procedure for table links creating to different Access database.
You can write following codes in the Form Load or CommandButton Click procedure:

Private sub Form_Load()
dim strOldLinkPath as string
dim strNewLinkPath as string

strOldLinkPath = "C:\MyDocuments\MyDB.mdb"
strNewLinkPath = "C:\Temp\MyDB.mdb"
'Removing links to "C:\MyDocuments\MyDB.mdb"
'if its exist
call LinksDelete(strOldLinkPath)
'Creating links to "C:\Temp\MyDB.mdb"
call LinksCreateToSource(strNewLinkPath)
end sub

Procedure for links creating (you may copy and paste this into any module of your DB):

Public Sub LinksCreateToSource(strLinkSourceDB As String, Optional prpProgressBar As Object)
On Error GoTo Err_LinksCreateToSource
Dim dbs As Database
Dim tdf As TableDef
Dim TdfCount As Long
Dim i As Long

'Open source DB
Set dbs = DBEngine.Workspaces(0).OpenDatabase(strLinkSourceDB)
'Counting tables in the source DB
'for setting progressbar Max property
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
'prpProgressBar is progress bar from your form (optional)
'you can omit this parameter.
'If you have the progressbar on your form then
If Not prpProgressBar Is Nothing Then
prpProgressBar.Max = TdfCount
prpProgressBar.Visible = True
End If
'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
'Progressbar updating
i = i + 1
If Not prpProgressBar Is Nothing Then
prpProgressBar.Value = i
End If
'Creating links
DoCmd.TransferDatabase acLink, _
&quot;Microsoft Access&quot;, strLinkSourceDB, acTable, tdf.Name, tdf.Name
End If
Next tdf
'Close source DB
dbs.Close
Set dbs = Nothing
'Progressbar hidding
If Not prpProgressBar Is Nothing Then
prpProgressBar.Visible = False
End If

Exit_LinksCreateToSource:
Exit Sub

Err_LinksCreateToSource:
MsgBox &quot;Error No &quot; & Err.Number & vbLf & Error$, , &quot;Sub LinksCreateToSource&quot;
Stop
Resume Exit_LinksCreateToSource

End Sub

Following function delete links from your DB. You can point DB what links you want to delete or delete all links:

Public Sub LinksDelete(Optional strConnectString As String = &quot;&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
End If
End If
Next tdf
End Sub

I hope this will solve your task.

Aivars
LOL My summer holidays is started yesterday!!!

 

[gusbrunston]

Dear Aviars:

I know this is an old thread, but wow! it has got me almost where I need to be.

My last problem: when I call &quot;LinksDelete&quot; the procedure deletes the links to all the NEWLY CREATED links except for the last table in the alphabetical order.

Any ideas for what I might be doing wrong?

Thanks so much for this thread, and a belated star to you! Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[scking]

Another means of performing the updates is to not delete the linked tables but modifiy the connection property to link to the new source. Many of the relink apps have pros/cons. My app uses a single table from a number of other databases an notifies the user of any broken links. It would not benefit by the deletealllinks and reconstructalllinks concept. On the other hand, I like Aviars procedures for most simple Access two db architectures.

Steve King Growth follows a healthy professional curiosity

 

[Aivars]

Hi Gus!

There is sub program, which refreshes links:

Sub RefreshLinks(strNewConnect As String, _
strOldConnect As String, _
Optional prbProgressBar As Control)
'This procedure refreshes linked table connections
'
'strOldConnect - Current connect string
'strNewConnect - connect string (full path of DB)
Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb

If Not prbProgressBar Is Nothing Then
'Sets progress bar values and shows it
prbProgressBar.Max = dbs.TableDefs.Count
prbProgressBar.Value = 0
prbProgressBar.Visible = True
End If
For Each tdf In dbs.TableDefs
'Checks for tables that link to old location
'Old connection =>> strOldConnect
If Not prbProgressBar Is Nothing Then
'Updates progress bar
prbProgressBar.Value = prbProgressBar.Value + 1
End If
If tdf.Connect = strOldConnect Then
'Relinks table
tdf.Connect = strNewConnect
tdf.RefreshLink
End If

Next tdf
dbs.Close
Set dbs = Nothing
If Not prbProgressBar Is Nothing Then
'Hides progress bar on form
prbProgressBar.Value = 0
prbProgressBar.Visible = False
End If
End Sub

You can find old link path in the system table MSysObjects. For easy finding you can use function writed by me ConnectStringOfLinkedTable():

Function ConnectStringOfLinkedTable(strTableName As String)
Dim rst As Recordset
Dim strSQL As String

'Returns Jet DB full path of linked table
'strTableName ->>> any table name, which is linked
strSQL = &quot;SELECT Database FROM MSysObjects WHERE Name='&quot; & strTableName & &quot;';&quot;
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not rst.EOF Then
ConnectStringOfLinkedTable = rst!Database
End If
rst.Close
End Function

Example:

dim strNewLocation as string
dim strOldLocation as string

strNewLocation =&quot;C:\MyDirectory\NewDB.mdb&quot;
strOldLocation =ConnectStringOfLinkedTable(&quot;MyLinkedTableName&quot

call RefreshLinks(strNewLocation, strOldLocation)

Good luck!
Aivars

 

[gusbrunston]

Hi.

First, let me say how much I appreciate the effort that so many give to help on this site, and in this case, that means you, Aviars, and you Steve.

I carefully put Aviars sub code first in the On Load event of a database-opening form, then in the On Click event.

In both instances, I got the following error:

The expression On Load (or On Click) you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.

* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

I'm now going back to my books to study events, subs and functions.

If you can help me get by this error, I'd be more than grateful.

Cheers.
Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[gusbrunston]

Hi:
Finished my studies, and came up with this &quot;On Load&quot; procedure which deletes the linked files in the &quot;Front End&quot; and then re-links to all the files in the &quot;Back End&quot;. I wish I were paid by the hour in dollars instead of in avocadoes!

Private Sub Form_Load()
On Error GoTo Form_Load_Err
Dim tdf As TableDef
Dim dbs As Database

‘open the database that has the table links to delete
Set dbs = OpenDatabase("NameOfFrontEnd.mdb")
   
For Each tdf In dbs.TableDefs
  'Don't delete System tables which begin with "MSys"
  If Left(tdf.Name, 4) <> &quot;MSys&quot; Then
    'tables to be kept are all named_ 
       &quot;tbl&quot; + a space &quot; + the name: don't delete them!
    If Left(tdf.Name, 4) <> &quot;tbl &quot; Then
      ‘delete all linked tables
      DoCmd.DeleteObject acTable, tdf.Name
    End If
  End If
Next tdf

‘open the database with the tables to link to
Set dbs = OpenDatabase(&quot;NameOfBackEnd.mdb&quot;)
   
For Each tdf In dbs.TableDefs
  [COLOR=green]‘don’t link system files[/color]
  If Left(tdf.Name, 4) <> &quot;MSys&quot; Then
    DoCmd.TransferDatabase acLink,_
        &quot;Microsoft Access&quot;,_
        &quot;NameOfBackEnd.mdb&quot;, acTable,_
        tdf.Name, tdf.Name, False
  End If
Next tdf
   
Form_Load_Exit:
    Exit Sub

Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit

End Sub

By the way, &quot;color green&quot; and &quot;/color&quot; TGML doesn't seem to work when it's nested inside &quot;code&quot; TGML ???
Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[Quintios]

gus,

How do you get around the fact that the database errors out when it can't find the back end? I got your code to work once I put in the full pathname of the front end (FE) and the back end (BE). What I can imagine happening is, for a simple application like mine, the user puts the front end wherever s/he wants. Access can figure out where it is by itself and that information would go into the string for the FE in your code. But then if the database can't find the back end, then it would prompt the user for a path to it and then store that path.

I won't have an install file, but it would be nice to save the location from session to session. Then anyone could use the same front end and navigate their way to the back end.

So, back to my original question, how do I get out of the error box that says it can't find the back end and then code it such that it asks you for the location and then stores it for the next use? I have dialog box code that will return a path and a filename, I just need to jump out of the database initialization process so that I can put the string in.

Thanks in advance,
Onwards,

Q-

 

[scking]

I believe Aivars correctly responded to the original thread question and demonstrated a solution well. You, on the other hand are expanding the original question. It's true that there are additional issues to consider besides just deleting and relinking, or refreshing linked tables from the front end. That becomes an additional requirement which you should consider. There are many alternatives to the issue that would satisfy the requirement. My application, when it is opened, checks each attached table to verify it exists. If it find one that does not exist it opens a form to facilitate relinking the tables. The form contains a combo box that allows the user to select a location from a previously used location or add a new location for the database. It also includes a browse button to allow the user to browse for the back end to be attached. Once it is connected it verifies the correct version and version release dates and notifies the user if they are mismatched. This information is stamped in both the front-end and back-end by a form that also notifies when the versions are out of synch. When the tables are all successfully linked it notifies the user. If any tables are not successfully linked it notifies the user of each table that could not be linked. You could carry this too far and it a full solution should be based on the application criticality. It so happens I've developed this for a critical application and can import it and adapt it for others.

Alternatives:
1) Store the location of the backend in a lookup table that is referenced when needed. Have a form to edit the table.
2) Store the location of the backend in the registry and allow the user to set the values manually from an options form.
3) Set a default location in code, test it and if it doesn't exist there prompt the user for the new location.
4) You get an error #? while using the data it represents the tables not being in the linked location. You could then
a) prompt the user, b) develop a search routine to search the local directory system or certain directories and subdirectories on the network until it found the file. Then you would need to prompt the user with the locations the filename was found because the file could be in different locations on the drives.

So the developer needs to determine which alternative would best suit the needs of their user community.

Steve King Growth follows a healthy professional curiosity

 

[LittleSmudge]

By the way, &quot;color green&quot; and &quot;/color&quot; TGML doesn't seem to work when it's nested inside &quot;code&quot; TGML ???


NO TGML works inside [ code ] .. .. [ /code ] delimiters because the [ code ] says to the system &quot;Treat this text literally and lay it out in TT format&quot;.


G LS

 

[rr191]

Here's some code I happened to have. Not sure where I got it. I have used the code below to automatically reattach tables if the database MDB and data MDB have been moved. This is Access 97 code and works only if the two files are in the same directory.

Function Autoexec()
Dim VerifyPrompt As Variant
Dim Status As Variant

If Not SysCmd(acSysCmdRuntime) Then
Status = AutoReattachTables(VerifyPrompt)
End If
End Function


Function AutoReattachTables(ByVal nVerifyPrompt As Integer)
'
' This function is used to automatically (and with optional
' user verification) reattach tables in databases that have
' been moved from their original location. It makes two very
' important assumptions, which I believe are true about many
' Access databases that are developed:
'
' 1. The main code for the database is in a separate file from
' the tables themselves (otherwise this routine isn't needed).
' It is assumed that the database containing the code is the
' one containing this module, and that it is the current database
' when this routine is called.
' 2. It is assumed that the files containing the code and tables
' are to reside in the same directory.
'
' This routine essentially checks the second condition, and will
' modify the path name pointing to the table database if it doesn't
' match the path of the code (current) database.
'
' This routine is passed one argument: a flag indicating whether the
' user should be prompted for verification if the table database file
' location is going to be updated. The user is giving the option of
' allowing the update (Yes), or skipping it (No). If &quot;No&quot; is selected,
' no change is made, and the tables will be accessed in the original
' directories. If the verification flag is false, the update is done
' unconditionally.
'
' If the user verification option is enabled, the user is only prompted
' on the first table to be updated. Subsequent tables are handled in
' the same manner as the first. This prevents a large number of prompts
' when many tables are attached. A more robust solution would be to use
' &quot;Yes&quot;, &quot;Yes to All&quot;, &quot;No&quot;, and &quot;Cancel&quot; as the options, but the standard
' message box does not allow this, and I didn't want to create a special
' form for this routine.
'
' This routine can be called from the AutoExec macro, and the user
' verification flag can be set to true or false as desired.
'
' This function returns True (-1) if it succeded or False (0) if there
' were any errors. Unexpected errors may also display a message box.
'
' NOTE: This routine currently only works with attached Access tables,
' but could be modified for other table types (the code to strip off
' the .MDB file name from the database path would need to be smarter).
'
On Error GoTo AutoReattachTables_Err
Dim MainDBDir As String ' Path of database containing code
Dim db As Database ' Current database object
Dim TabDefs As TableDefs ' TableDefs collection
Dim nCurTab As Integer ' Counter for cycling through tables
Dim nPtr As Integer ' Temporary string pointer
Dim TabFileNameLoc As String ' Path of current table DB file
Dim TabFileName As String ' DB file name for current table
Dim nConnectLen As Integer ' Length of Connect property value
Dim ConnectPrefix As String ' beginning of Connect property value
Dim nDBDirPos As Integer ' String pointer
Dim PromptStr As String ' User verification prompt message
Dim nPromptDone As Integer ' Flag for prompting user only once
Dim nYesToAll As Integer ' User response to verification prompt

Set db = DBEngine.Workspaces(0).Databases(0) ' Point to current database
Set TabDefs = db.TableDefs ' open the TableDefs collection
MainDBDir = db.Name ' get full path of code database
For nPtr = Len(MainDBDir) To 1 Step -1 ' find last backslash to strip file name
If Mid$(MainDBDir, nPtr, 1) = &quot;\&quot; Then
MainDBDir = Left$(MainDBDir, nPtr - 1) ' extract path
Exit For
End If
Next
If MainDBDir = &quot;&quot; Then
AutoReattachTables = False ' function failed
Exit Function
End If

' check each table in database
nPromptDone = False ' we haven't prompted user yet
' the next statement initializes the default update action to True if the
' verification prompt is disabled (otherwise, we assume user will say &quot;No&quot
nYesToAll = Not nVerifyPrompt
For nCurTab = 0 To TabDefs.Count - 1
If Mid(TabDefs(nCurTab).Name, 1, 4) <> &quot;MSys&quot; Then ' ignore system tables
TabFileNameLoc = TabDefs(nCurTab).Connect ' get path to DB dir/file
nConnectLen = Len(TabFileNameLoc) ' length of connect string
For nPtr = nConnectLen To 1 Step -1 ' search for last backslash
If Mid$(TabFileNameLoc, nPtr, 1) = &quot;\&quot; Then ' if found, get table file name
TabFileName = Right(TabFileNameLoc, nConnectLen - nPtr)
TabFileNameLoc = Left$(TabFileNameLoc, nPtr - 1)
Exit For
End If
Next
nDBDirPos = InStr(TabFileNameLoc, &quot;DATABASE=&quot ' find prefix to path name
If nDBDirPos Then ' if we found start of path
nDBDirPos = nDBDirPos + 9 ' Len(&quot;DATABASE=&quot ' point past prefix
ConnectPrefix = Left$(TabFileNameLoc, nDBDirPos - 1) ' save prefix for later
TabFileNameLoc = Right(TabFileNameLoc, Len(TabFileNameLoc) - nDBDirPos + 1)
If TabFileNameLoc <> MainDBDir Then ' directories don't match
If nVerifyPrompt And (Not nPromptDone) Then ' see if we check with user
' based on the assumptions discussed in the comments
' at the start of this routine, this function words the
' following message accordingly
PromptStr = &quot;Database file &quot; & TabFileName & &quot; has been moved&quot; & Chr$(10) & Chr$(13)
PromptStr = PromptStr & &quot;from directory: &quot; & TabFileNameLoc & Chr$(10) & Chr$(13)
PromptStr = PromptStr & &quot;to directory: &quot; & MainDBDir & Chr$(10) & Chr$(13) & Chr$(13)
PromptStr = PromptStr & &quot;Would you like the directory location updated for&quot; & Chr$(10) & Chr$(13)
PromptStr = PromptStr & &quot;this and any other database files that have been moved?&quot;
If MsgBox(PromptStr, 4) = 6 Then ' get the response
nYesToAll = True ' if &quot;Yes&quot;, set flag to update
End If
nPromptDone = True
End If
If nYesToAll Then ' if we're to update...
' this is the easy part - construct new path name and force a refresh
TabDefs(nCurTab).Connect = ConnectPrefix & MainDBDir & &quot;\&quot; & TabFileName
TabDefs(nCurTab).RefreshLink
End If
End If
End If
End If
Next
AutoReattachTables = True ' function succeeded

AutoReattachTables_Exit:
Exit Function

AutoReattachTables_Err:
Dim ErrMsg As String
ErrMsg = &quot;AutoReattachTables: &quot; & Error$
If TabFileName <> &quot;&quot; Then ErrMsg = ErrMsg & &quot; (&quot; & TabFileName & &quot&quot;
MsgBox ErrMsg
Resume AutoReattachTables_Exit

End Function

 

[Quintios]

scking,

I appreciate your reply. I've been trying to find a solution for the question as to how to relink tables when the back end has moved. For my situation, the user will copy the front end and then somehow, point to the back end wherever it may lie.

I'm attempting to learn how the linking goes on and how to, once the links are broken, prompt the user to enter the location of the back end and then proceed from there.

What I've seen here has educated me somewhat as to what happens when table links need to be deleted and re-linked. What I'm trying to determine is how to implement the code with as little prompting to the enduser as possible.

From what I can tell, the database links are kept in a system table. After looking again at what Aivars posted I agree that it does what he says it does. What I'm trying to determine is, when the Front End starts up and can't find the tables, how to get the re-linking process started again. I posted in this thread as it is receiving a lot more attention than the other threads I've posted with similar subject matter.

So, I pose the question to you, how do I interrupt the startup process so that it breaks out and goes to the &quot;show me where the backend is&quot; as opposted to simply getting an error dialog box that says &quot;I can't find <enter backend name here>&quot; and then you get a blank screen instead of your startup form.

Am I making sense?

Thanks!
Onwards,

Q-

 

[scking]

Let me first clarify where the links are as they are not in a system table. An Access front-end contains objects which you see as a tablename which, among other things, contains a link pointer to the location of the action back-end. These Access object are in a container. Searching through each of these objects and looking at the 'Connection' property will give you a clear look at where the data is located.

'Note: this code requires DAO references
' and displays a connection string something like
' ;DATABASE=d:\groups\sdv\data2000.mdb

Public Sub ListConnectionProperty()

Dim i As Integer
Dim tbl As TableDef

For i = 0 To db.TableDefs.Count - 1
Set tbl = db.TableDefs(i)
If tbl.Connect <> &quot;&quot; Then
' If the connection string can not be verified then
' open the frmTablelinkWizard to manage the reattachment
' and return a false.
Debug.Print tbl.Connect
'If Not VerifyLink(tbl.Name) Then
' On Error Resume Next
' DoCmd.OpenForm &quot;frmTablelinkWizard&quot;, , , , , acDialog, tbl.Name
'End If
End If
Next i

End Sub


Public Function VerifyLink(strTable As String) As Boolean

Dim strTemp As String

On Error Resume Next

' Set a temporary variable to a field name.
' If there is an error then the function will return a false.
strTemp = CurrentDb.TableDefs(strTable).Fields(0).Name
VerifyLink = (Err.Number = 0)

End Function

Steve King
Growth follows a healthy professional curiosity

 

[howo5]

Hi guys I'm trying to do the same thing where I use the

DoCmd.TransferDatabase acLink, &quot;ODBC Database&quot;, ConnectStr, acTable, rstrTblName, rstrTblName

to link tables in Oracle, my problem is that it shoots up the unique record identifier screen and when I selsct just the first field and click OK it gives me a table full of the same record. Is there a way in code where I can bypass the unique record identifier or specify it in the code so the dialog box doesn't appear?

 

[howo5]

Hi guys I'm trying to do the same thing where I use the

DoCmd.TransferDatabase acLink, &quot;ODBC Database&quot;, ConnectStr, acTable, rstrTblName, rstrTblName

to link tables in Oracle, my problem is that it shoots up the unique record identifier screen and when I selsct just the first field and click OK it gives me a table full of the same record. Is there a way in code where I can bypass the unique record identifier or specify it in the code so the dialog box doesn't appear?

 

[scking]

Never had to deal with that issue. My solution merely finds each link, checks that the linked table exists, and if not, prompts the user with a dialog box so they can select the file which should be linked, and then relinks and refreshes to the new file. To check that the table exists you can merely try to refresh the table link, and if it does not exist it will return an error. I've never needed to use the DoCmd.TransferDatabase acLink command.

If tbl.Refresh <> 0 Then
' Pop up the dialog to choose a new file
End If

Steve King Growth follows a healthy professional curiosity

 

[howo5]

so there's no way in access to get rid of the the unique record identifier screen when linking a table without a primary key is there?