473,221 Members | 2,135 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

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

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
7 2916
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
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
<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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: geotso | last post by:
Hi. I supose that my problem has a javascript solution so, I kindly ask for your help: I want to use a link to open one of two files (*not both*) stored each one in a different partition of...
2
by: jaYPee | last post by:
i have split my database and created a code that will look up for the location of the mdb back end everytime the front end link has change. i'm wondering if there is a way to just create and INI...
14
by: Steve McLellan | last post by:
Hi, Sorry to repost, but this is becoming aggravating, and causing me a lot of wasted time. I've got a reasonably large mixed C++ project, and after a number of builds (but not a constant...
2
by: JenHu | last post by:
hi, I have a datagrid in my asp.net application, I am using vb.net language. I want to have my last column as a link column, link to text file. The link path is "c:\Temp\" & F_File_Name, which...
3
by: Andreas | last post by:
Hi! I'm currently developing a DLL that makes use of C++ and .net (mixed) using Visual Studio 2003. Now, as I wanted to move to the new Visual Studio 2005, I converted this project into the...
6
by: Ozz | last post by:
Hi there, I have a link on my web page. When clicked, opens up a pdf file that is stored on my server. Every file is specific to a user's user name and I don't want users to see each other's...
2
by: SR | last post by:
I have started a web site using ASP.NET 2.0. I would like to centralize all of my classes in a StyleSheet but I cannot figure out how to link the StyleSheet to a Content Page since there is no...
2
by: BASSPU03 | last post by:
I used the Common Dialog API to store file paths on my form's underlying table. These paths are displayed in a textbox that I can click to open the selected file. Having stored the file paths in...
2
by: franc sutherland | last post by:
Hello everyone, I am using Access 2003. In my database there is a table which is linked to an excel spreadsheet. When I move the mdb file to someone else's computer the spreadsheet will be in...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.