By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,358 Members | 2,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,358 IT Pros & Developers. It's quick & easy.

Link the FE to the BE using Code and Path/File

P: n/a
Hello

I currently Link the FE/BE using the LinkTables Option and the Linked
Table Manager. Any time I need to move the BE to another location, I
have to go through this process over again. I need the flexibility of
being able to move the BE data to wherever I want, without manually
going through this process. I just want to change the Path/Filename
that I keep in a FE Table.

I want to be able to place a Path/Filename in a FE Table that points
to the BE Tables location. I want my code to automatically
connect to the BE Tables by using this Path/Filename as the reference.

How do I approach this? Is there any code available that meets this
criteria?

Thanks
Greg

Mar 17 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Hi, Greg.
I need the flexibility of
being able to move the BE data to wherever I want, without manually
going through this process. I just want to change the Path/Filename
that I keep in a FE Table.

I want to be able to place a Path/Filename in a FE Table that points
to the BE Tables location. I want my code to automatically
connect to the BE Tables by using this Path/Filename as the reference.
That's a lot of "I wants," but you forgot the two most important ones:

1.) I want this to work flawlessly, every time.
2.) I want this for free.

Please see the following Web page for an example:

http://www.mvps.org/access/tables/tbl0009.htm

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
<Ap******@gmail.comwrote in message
news:11**********************@d57g2000hsg.googlegr oups.com...
Hello

I currently Link the FE/BE using the LinkTables Option and the Linked
Table Manager. Any time I need to move the BE to another location, I
have to go through this process over again. I need the flexibility of
being able to move the BE data to wherever I want, without manually
going through this process. I just want to change the Path/Filename
that I keep in a FE Table.

I want to be able to place a Path/Filename in a FE Table that points
to the BE Tables location. I want my code to automatically
connect to the BE Tables by using this Path/Filename as the reference.

How do I approach this? Is there any code available that meets this
criteria?

Thanks
Greg

Mar 17 '07 #2

P: n/a
Hello Gunny
http://www.mvps.org/access/tables/tbl0009.htm
Seems to be the only game in town!
I will give it a whirl or a hurl.

Thanks
Greg

Mar 17 '07 #3

P: n/a
<Ap******@gmail.comwrote
Hello Gunny
>http://www.mvps.org/access/tables/tbl0009.htm

Seems to be the only game in town!
I will give it a whirl or a hurl.
I haven't compared the two, but there's an example of relinking tables at
startup in the Orders and Solutions sample database. I've given up trying to
keep up with where it is stored on the Microsoft website, so I just go to
the Knowledge Base at http://support.microsoft.com and search for it. The KB
article has always had a current link. Of course, this sample is unchanged
since Access 2000 was the current version, so once you download it, just use
the copy saved on your hard drive.

It's just a matter of cycling through the TableDefs, finding those with a
non-Null Connect property, changing the path-and-file to the one you want,
and then executing a RefreshLink... if you are relinking Jet tables in an
..MDB.

If you are using an ODBC driver to link to a server DB back-end, depending
on the capabilities of the ODBC driver, Access, etc., you may have to do
more -- even to having to delete and re-create the linked TableDef.

Larry Linson
Microsoft Access MVP
Mar 18 '07 #4

P: n/a
Ap******@gmail.com wrote:
Hello

I currently Link the FE/BE using the LinkTables Option and the Linked
Table Manager. Any time I need to move the BE to another location, I
have to go through this process over again. I need the flexibility of
being able to move the BE data to wherever I want, without manually
going through this process. I just want to change the Path/Filename
that I keep in a FE Table.

I want to be able to place a Path/Filename in a FE Table that points
to the BE Tables location. I want my code to automatically
connect to the BE Tables by using this Path/Filename as the reference.

How do I approach this? Is there any code available that meets this
criteria?
I've never seen any published code to do this. It's not very hard to
do; the challenge is to maintain the defined relationships.

Just iterate through the defined relationships and re-create them in
the new back-end.

--
'---------------
'John Mishefske
'---------------
Mar 18 '07 #5

P: n/a
Thanks for the replies.

http://www.mvps.org/access/tables/tbl0009.htm
I used the suggested Dev Ashish written code found at the mvps site.

I can modifiy this to do what I need. I did notice, that it seems to
be understood that the FE/BE links are required to have already been
established using the Linked Table Manager before you can successfully
use this code.

I experimented with a split database that did not have its links
established yet, hoping that the code would do this, but it did not
work. Then, I Linked the Tables manually using the Linked Table
Manager. Then, I place the BE where it was not expected to be, and
was able to Re-Establish the link using the code.

Thanks All
Greg

Mar 18 '07 #6

P: n/a
I've done something SIMILAR.
Background:
=========
I used to have about 8 or 10 "identical" databases that I use to control
parts inventory at several of my customers.
"Identical" is in quotes because they all started out that way <grin...
but I would make an improvement (modification) in one of them, and just
never quite "got around to" modifying the rest of them.
I also fooled around with replication ... and hated it.

I decided that I would make ONE master (front-end) database, and then
re-link to whatever back-end I wanted to use. That way, all of the forms,
reports, etc would only have to be changed ONCE. I also change the TITLE of
my app, using code, so that it displays the company name of the customer
that I am working on. I also use an additional linked table that does NOT
change, so I "mickey-moused" my way around having to re-link that table
every time.
================================
I have a command button on frmSwitchboard called cmdRefreshLinks.
There is also a listbox (lstLinkedTables) that uses an SQL statement to
retreive the table names from the database itself.

SELECT DISTINCTROW MSysObjects.Name, MSysObjects.Database
FROM MSysObjects
WHERE (((MSysObjects.Database) Is Not Null))
ORDER BY MSysObjects.Name;
---------------------------------------------------------
Private Sub cmdRefreshLinks_Click()
fRelinkMultipleBackends2
Me.lstLinkedTables.Requery

Dim intX As Integer
Dim MyAppName As String
MyAppName = DLookup("CompanyName", "tblCompanyInformation")
intX = AddAppProperty("AppTitle", dbText, MyAppName)
'intX = AddAppProperty("AppIcon", dbText, "C:\Windows\Cars.bmp")
RefreshTitleBar

End Sub
---------------------------------------------------------
This function adds the company name to the title bar -- copied/pasted from
Help--

Function AddAppProperty(strName As String, varType As Variant, varValue As
Variant) As Integer
Dim dbs As Database, prp As Property
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo AddProp_Err
dbs.Properties(strName) = varValue

AddAppProperty = True

AddProp_Bye:
Exit Function

AddProp_Err:
If Err = conPropNotFoundError Then
Set prp = dbs.CreateProperty(strName, varType, varValue)
dbs.Properties.Append prp
Resume
Else
AddAppProperty = False
Resume AddProp_Bye
End If
End Function
---------------------------------------------------------

Here is the function that does the work.... It is kept in the "Modules"
section of the Database window

Public Function fRelinkMultipleBackends2()
'--------------------------------------------------------------------
'Name: fRelinkMultipleBackends (Function)
'Purpose: Re-links attached tables on a
' one-by-one basis, deals with locating
' 'lost' MDB file links.
'Author: Don Leverton
'Date: July 31, 2004, 09:46:28 PM
'Called by: cmdRefreshLinks_Click() on Switchboard form
'Calls: LinkOneTable function
'Inputs: None
'Output: Message that confirms / informs
'Requires: Dev's fGetMDBName() function and GetOpenFileName API from:
' http://www.mvps.org/access/tables/tbl0009.htm
'Thanks to: Tom van Stiphout, Douglas J. Steele and Dev Ashish
'-------------------------------------------------

Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim tdf As DAO.TableDef

Dim intLinkedCount As Integer
Dim intSuccessCount As Integer

Dim strNewPath As String
Dim strTable As String
Dim Result As Boolean

Dim Msg As String
Dim CR As String
CR = vbCrLf
DoCmd.Hourglass True
On Error Resume Next

' Loop through all tables in database.
For Each tdf In MyDB.TableDefs

If InStr(1, tdf.Name, "tblPricing") 0 Then
'"tblPricing" is from a data path that never changes.
' This code excludes it from being processed.
intSuccessCount = intSuccessCount + 1
intLinkedCount = intLinkedCount + 1
GoTo GetNext
End If
If Len(tdf.Connect) 0 Then ' If the Connect property is non-empty,
the table is linked
intLinkedCount = intLinkedCount + 1 'Get a count of linked tables
strTable = tdf.Name 'Get the linked table name
' On Error Resume Next

' tdf.RefreshLink 'Attempt to relink table using existing .Connect
property

' If Err.Number <0 Then 'If RefreshLink fails...
If Len(strNewPath) 0 Then
'Try to re-use the existing string if it has already
been found
Result = LinkOneTable(MyDB.TableDefs(strTable),
strNewPath)
If Result = True Then 'The re-linking of the table was
successful
intSuccessCount = intSuccessCount + 1
GoTo GetNext
Else
GoTo GetPath
End If '(for Result = True)

End If '(for Len(strNewPath) 0)
GetPath:
Msg = ""
Msg = Chr(39) & strTable & Chr(39)
Msg = Msg & " needs to re-linked " & CR
Msg = Msg & "to it's 'back-end' MDB file" & CR & CR
Msg = Msg & "Please select it's location " & CR
Msg = Msg & "from the next dialog box."
MsgBox (Msg)

strNewPath = fGetMDBName("Please select a new datasource
for: " & strTable)
Result = LinkOneTable(MyDB.TableDefs(strTable), strNewPath)
' Else
intSuccessCount = intSuccessCount + 1 'RefreshLink was
successful
' End If '(for Err <>0)

End If '(for Len tdf)

GetNext:
Next tdf

MyDB.TableDefs.Refresh

Msg = ""
Msg = Msg & intSuccessCount & " of "
Msg = Msg & intLinkedCount & CR
Msg = Msg & "linked tables have been " & CR
Msg = Msg & "successfully re-linked."
MsgBox (Msg)

Set tdf = Nothing
Set MyDB = Nothing
DoCmd.Hourglass False
End Function
----------------------------------
'--------------------------------------------------------------------
Function LinkOneTable(tdf As TableDef, MyPath As String) As Boolean
'Debug.Print "Attempting to re-link " & tdf.Name
On Error Resume Next
' If the Connect property is non-empty, the table is linked
If Len(tdf.Connect) 0 Then
tdf.Connect = ";DATABASE=" & MyPath
Err.Clear
tdf.RefreshLink ' Re-link the table.
If Err Then
LinkOneTable = False ' This attempt to re-link has failed.
Exit Function
End If
End If
Set tdf = Nothing
LinkOneTable = True ' This link has been succesfully refreshed.
End Function
===============================================
Long-winded ... but I hope it helps you with your project!
Don
<Ap******@gmail.comwrote in message
news:11**********************@d57g2000hsg.googlegr oups.com...
Hello

I currently Link the FE/BE using the LinkTables Option and the Linked
Table Manager. Any time I need to move the BE to another location, I
have to go through this process over again. I need the flexibility of
being able to move the BE data to wherever I want, without manually
going through this process. I just want to change the Path/Filename
that I keep in a FE Table.

I want to be able to place a Path/Filename in a FE Table that points
to the BE Tables location. I want my code to automatically
connect to the BE Tables by using this Path/Filename as the reference.

How do I approach this? Is there any code available that meets this
criteria?

Thanks
Greg

Mar 19 '07 #7

P: n/a
John Mishefske wrote:
Ap******@gmail.com wrote:
>Hello

I currently Link the FE/BE using the LinkTables Option and the Linked
Table Manager. Any time I need to move the BE to another location, I
have to go through this process over again. I need the flexibility of
being able to move the BE data to wherever I want, without manually
going through this process. I just want to change the Path/Filename
that I keep in a FE Table.

I want to be able to place a Path/Filename in a FE Table that points
to the BE Tables location. I want my code to automatically
connect to the BE Tables by using this Path/Filename as the reference.

How do I approach this? Is there any code available that meets this
criteria?


I've never seen any published code to do this. It's not very hard to
do; the challenge is to maintain the defined relationships.

Just iterate through the defined relationships and re-create them in
the new back-end.
I mis-understood your request. I was thinking you were looking for a replacement (coded
solution) to the Link Table Manager.

There is no relationship issues if you are just linking to a different BE.

--
'---------------
'John Mishefske
'---------------
Mar 19 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.