Relinking Backend & Frontend

[Mmccl]

Can this be done

I have done much research on relinking BE and FE tables. This forum has offered several solutions, but either I am unable to implement them due to lack of understanding, or they are not quite what I am needing.

The relinking method I am looking for is contained in this zip file. It is a freely distributed Database but it's editable code has been removed by use of an MDE creation on the front end.

could anyone take a look at this DB and tell me how this fellow relinks his tables. All I can tell from it is that on Start up the first form that loads is a hidden form called "frmSplash". If you load the App with the shift key, and then double click "frmSplash" nothing happens - EXCEPT - a progress bar in the bottom left hand corner of access says "relinking".

This app will relink the BE to the FE as long as both FE and BE are in the same folder, it does not matter what folder you install them too, or how often you move the files. It will always relink, and it does not have a dialog box the prompts for you to give the location of the BE.

Does any one know how this works. If so, could you let me in on the specifics?? enlighten me, or send me to a web site that details this proceedure.

any help would be appreciated

go to this link to download the file - to big for forum at 703kb

http://www.hopeforhonduras.com/linktables.htm

 

[dan-cat]

Well this is my attempt at it.

Would appreciate any kindly criticism of the below


Dim dbs As DAO.Database
Dim db As DAO.Database
Dim tdfNew As DAO.TableDef
Dim tdf As TableDef
Dim i As Integer
Dim strTableName As String
Dim strLinkDb As String
Dim strNameDb As String

Set db = CurrentDb

strNameDb = "YourBackEndDbName.mdb"
strLinkDb = Left(db.Name, InStrRev(db.Name, "\")) & strNameDb

With Application.FileSearch
.NewSearch
.LookIn = Left(db.Name, InStrRev(db.Name, "\") - 1)
.FileName = strNameDb

If .Execute < 1 Then
MsgBox strNameDb & " has not been saved in " & Left(db.Name, InStrRev(db.Name, "\") - 1)
Exit Sub
End If
End With


Set dbs = OpenDatabase(strLinkDb)
For i = 0 To dbs.TableDefs.Count
On Error Resume Next
If Not dbs.TableDefs(i).Name Like "Msys*" Then

strTableName = dbs.TableDefs(i).Name
Set tdfNew = db.CreateTableDef(strTableName)
tdfNew.Connect = ";DATABASE=" & strLinkDb
tdfNew.SourceTableName = strTableName
db.TableDefs.Append tdfNew
End If
Next i



With Application
.RefreshDatabaseWindow
End With


Give us a shout if you don't understand any of it.

 

[Mmccl]

Thank you dan-cat for your help.

I'm sure there are a dozen things I'm doing wrong.

Using your code above I was not sure if this was to be used as an event procedure in a form or as a function in a module.

I set it up in an on open event procedure and got errors.

So I tried it as a module and got an error on this line

Set dbs = OpenDatabase(strLinkDb)

Right now I created 2 tables and split the DB. Then I created 2 forms, one based on a table the other as a menu.

I created a macro to open the menu at start up and I am calling the function from the macro.

Here is a zip with the BE and FE I created so far

What am I doing wrong?

Mike

 

[dan-cat]

I tried it locally on my machine (made sure both dbs were in the same folder) and it worked first time. Make sure your DAO 3.6 reference is above the ActiveX reference.

What error message are you getting?

 

[Mmccl]

OK, I changed to DAO 3.6 and moved it above the Active X and no more errors.

But my goal is to have the FE relink the BE automaticly at start up. If the FE and BE are left in the original creation folder no problem.

But it I move the FE and BE to a new folder, the menu form loads, but when I click the command button to open the mail lists form I get the error:

Could not find file: C:\...\db3_be.mde

It's still looking back to where it was in the beginning.

Thanks for your help
Mike

 

[dan-cat]

Function Relinktables()

Dim dbs As DAO.Database
Dim db As DAO.Database
Dim tdfNew As DAO.TableDef
Dim tdf As TableDef
Dim i As Integer
Dim strTableName As String
Dim strLinkDb As String
Dim strNameDb As String

Set db = CurrentDb

'strNameDb = "YourBackEndDbName.mdb"
strNameDb = "db3_be.mdb"
strLinkDb = Left(db.Name, InStrRev(db.Name, "\")) & strNameDb

For Each tdf In db.TableDefs

If Len(tdf.Connect) > 0 Then
db.TableDefs.Delete tdf.Name
End If
Next

With Application.FileSearch
.NewSearch
.LookIn = Left(db.Name, InStrRev(db.Name, "\") - 1)
.FileName = strNameDb

If .Execute < 1 Then
MsgBox strNameDb & " has not been saved in " & Left(db.Name, InStrRev(db.Name, "\") - 1)
Exit Function
End If
End With


Set dbs = OpenDatabase(strLinkDb)
For i = 0 To dbs.TableDefs.Count
On Error Resume Next
If Not dbs.TableDefs(i).Name Like "Msys*" Then

strTableName = dbs.TableDefs(i).Name

Set tdfNew = db.CreateTableDef(strTableName)
tdfNew.Connect = ";DATABASE=" & strLinkDb
tdfNew.SourceTableName = strTableName
db.TableDefs.Append tdfNew
End If
Next i



With Application
.RefreshDatabaseWindow
End With

End Function


I've added a snippet of code (highlighted in red above) that will check each table in the currentdb and if it is a linked table then it is deleted. This ensures that on relink the links are refreshed correctly

 

[Mmccl]

Works in this sample

Thanks that seems to be it.

now lets see if I can transfer this over to my "BIG" application!?! Here goes . . .

 

[dan-cat]

did it work

 

[Mmccl]

Strange Behavior

Dan-Cat

Seems to work ok with a limited number of tables, but when you have more than 3 (and some times even with just 3) it relinks some tables but not others to the back end.

My testing has gone like this:
1. create a DB with 3 to 5 tables. only one of the tables is set as a record source to a form.
2. split the DB
3. change the folder name
4. when DB opens, some tables relinked, others are still linked to the old folder - which no longer exists.

enclosed is my latest with the relinked caos.



Thanks for the help
Mike

 

[Mmccl]

Seems to do every other one if you have 6 or more tables

 

[dan-cat]

Seems to do every other one if you have 6 or more tables

Ok if this one doesn't work I shall eat my hat

I've tested it with an extra nine tables all now seem to link ok.

Let me know if you have problems

 

[Mmccl]

Ok if this one doesn't work I shall eat my hat

I've tested it with an extra nine tables all now seem to link ok.

Let me know if you have problems

WOW! That certainly goes a long way. I am testing it today. Thanks again for all your help.

 

[Mmccl]

Code works fine as it stands in the little database we have been trading around, BUT when i import the "relink" module to my application and then move my app I get an error message that reads:

C:\... is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server were the file resides.

I click ok, get a blank access screen. close access and then reopen the application and it works, all tables have been relinked ?!?

That is odd, but it must be the complexity of my other code stuff. I guess you can get conflicks.

Would using an application Icon that calls from the startup dialog box have any thing ot do with it?

PS. how do I upload an avatar to my name. I have checked all over my settings and cant find it. only find the screen that lets me turn it off, but I dont have one.