FrontEnd BackEnd linking

[dale5804]

hi there, i currently have a Front end Back end setup. What i am looking for i a neat piece of code i can use on the Start up form in the Front end, that tells it were the Back end is located. Is that possible.

[ADezii]

hi there, i currently have a Front end Back end setup. What i am looking for i a neat piece of code i can use on the Start up form in the Front end, that tells it were the Back end is located. Is that possible.

This code was tested on a 'live' Database that I currently use. The following code will generate a listing of all 'Linked' Tables and the Back End Database to which they are linked. Any questions, feel free to ask:

Dim tdf As DAO.TableDef, strConnect As String
 
For Each tdf In CurrentDb.TableDefs
  strConnect = tdf.Connect
  If Len(strConnect) > 0 Then
    Debug.Print "[" & tdf.Name & "] linked to: " & _
                 Right$(strConnect, Len(strConnect) - InStrRev(strConnect, "="))
  End If
Next

OUTPUT:

[Switchboard Items] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblAssessmentSteps] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblBackupTraining] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblCategories] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblChemicalsStored] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblCRFAC00] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblEmployee] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblEvaluationComments] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblEvaluationStepsForItems] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblEvent] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblExpendedTrngItems] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblHazMatBackups] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblHazMatTopics] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblHMAUResponse] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblIncidentInfo] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblInventoryTransactions] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblLocSublocQty] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblMaintInterval] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblMaintIntervalData] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblMaintRepairs] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblManufacturers] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblMasterItemsList] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblMultipleSuppliers] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblPartsList] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblPCBs] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblProjectCodes] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblProjectTeam] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblQuestions] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblRank] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblSavedTests] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblServiceDates] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblSession] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblSites] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblStorLoc] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblSubLoc] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblSuppliersProvider] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblSystemRequests] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblTechnicalInformation] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblTopics] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblTrainingInService] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblTrainingModules] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblTrainingSubject] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblTransactionCodes] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblUnitLevels] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblUnits] linked to: C:\HAZ-MAT\HMAU_Data.mdb
[tblUsedTrngItems] linked to: C:\HAZ-MAT\HMAU_Data.mdb

[mshmyob]

This is not for listing your links but to generate a new link if the BE is moved.


This is one I use that I found and combined and modified. I create a button on a special relink form and an unbound text box where you type the whole path and file name where the BE is (by typing the file name with extension you can use any version of Access - ie 2007 - i remmed out the part in the code that was looking for the file name and looking for specific extensions).

I type it in manually instead of using the MS library for picking a directory because some computers don't have the proper DLL to use the MS library.

There is some extra stuff in here but won't get activated so don't worry about it.

You only need to run it once on each computer if the BE is installed or moved in a different directory of your original build. Also works on a network if you map a drive letter to a server/directory.


Code Removed due to explicit copyright declaration claim by original author Dev Ashish that it should NOT be DISTRIBUTED EXCEPT as part of an application

hi there, i currently have a Front end Back end setup. What i am looking for i a neat piece of code i can use on the Start up form in the Front end, that tells it were the Back end is located. Is that possible.

[Jim Doherty]

This is not for listing your links but to generate a new link if the BE is moved.


This is one I use that I found and combined and modified. I create a button on a special relink form and an unbound text box where you type the whole path and file name where the BE is (by typing the file name with extension you can use any version of Access - ie 2007 - i remmed out the part in the code that was looking for the file name and looking for specific extensions).

I type it in manually instead of using the MS library for picking a directory because some computers don't have the proper DLL to use the MS library.

There is some extra stuff in here but won't get activated so don't worry about it.

You only need to run it once on each computer if the BE is installed or moved in a different directory of your original build. Also works on a network if you map a drive letter to a server/directory.


Code Removed due to explicit copyright declaration claim by original author Dev Ashish that it should NOT be DISTRIBUTED EXCEPT as part of an application

Subscribing for information purposes only

[mshmyob]

Here is the link but keep in mind I modified it for my scenario as described below.

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

This is not for listing your links but to generate a new link if the BE is moved.


This is one I use that I found and combined and modified. I create a button on a special relink form and an unbound text box where you type the whole path and file name where the BE is (by typing the file name with extension you can use any version of Access - ie 2007 - i remmed out the part in the code that was looking for the file name and looking for specific extensions).

I type it in manually instead of using the MS library for picking a directory because some computers don't have the proper DLL to use the MS library.

There is some extra stuff in here but won't get activated so don't worry about it.

You only need to run it once on each computer if the BE is installed or moved in a different directory of your original build. Also works on a network if you map a drive letter to a server/directory.


Code Removed due to explicit copyright declaration claim by original author Dev Ashish that it should NOT be DISTRIBUTED EXCEPT as part of an application

[dale5804]

cheers everyone.
i tried the above quoted code from Dev Ashish, but can not get it to work. plus its more complicated that i need. all i am trying to sort is something that when the database starts up, a piece of code tells it where the back end is. therefore i can create several front ends, and re-type within there code where the BE will be before i distribute the database.
any other ideas??

[mshmyob]

I will write a new version and simplify it and repost it.

cheers everyone.
i tried the above quoted code from Dev Ashish, but can not get it to work. plus its more complicated that i need. all i am trying to sort is something that when the database starts up, a piece of code tells it where the back end is. therefore i can create several front ends, and re-type within there code where the BE will be before i distribute the database.
any other ideas??

[mshmyob]

Here is a simple version. No error checking and the back end file and location is hard coded. Put it in your main form open event or something like that.

Function RefreshLinks() As Boolean
Dim collTbls As Collection
Dim i As Integer
Dim strTbl As String
Dim dbCurr As Database
Dim dbLink As Database
Dim tdfTables As TableDef
Dim strBeFile As String
Dim collTables As New Collection
Dim tdf As TableDef
 
' get the current linked table definitions
    Set dbCurr = CurrentDb
    dbCurr.TableDefs.Refresh
' end
 
    'First get all linked tables in a collection
    For Each tdf In dbCurr.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                    collTables.Add Item:=.Name & .Connect, Key:=.Name
            End If
        End With
    Next
    Set collTbls = collTables
 
' change this string to your drive/directory/filename               
strBeFile = "c:\any directory\backend file name.mdb"
 
            Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
 
  ' start linking your tables - start from the last and work your way down
     For i = collTbls.Count To 1 Step -1
            strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
                Set tdfTables = dbCurr.TableDefs(strTbl)
                With tdfTables
                    .Connect = ";Database=" & strBeFile
                    .RefreshLink
                End With
    Next
   End Function
 

cheers everyone.
i tried the above quoted code from Dev Ashish, but can not get it to work. plus its more complicated that i need. all i am trying to sort is something that when the database starts up, a piece of code tells it where the back end is. therefore i can create several front ends, and re-type within there code where the BE will be before i distribute the database.
any other ideas??

[MMcCarthy]

Just a note on the copywrite situation. Jim was correct in his actions. This copywrite notice is standard on all Dev Ashish's modules. A lot of MVP's do this and using a link to their site or as in this case the MVP site is an acceptable alternative.

Although the rules state that linking to a competing or commercial site is not allowed. These type of sites are not a problem.

Mary

[mshmyob]

No prob. I did leave his copyright so I wasn't trying to take credit.

Just on a side note I think it absolutley assanign that any programmer would post his source code on the internet or any other public forum and then say you can't use it. Then they shouldn't post it. I know everyone will nit pic saying they didn't say they couldn't use it exactly and you can link to it etc. etc. but as long as it is posted and the copyright is left in then I believe it should be able to be used whereever and whenever someone likes.

But I also understand theScripts reason for removing it. The preceding was just my rant on the new idiocracy of society.

Just a note on the copywrite situation. Jim was correct in his actions. This copywrite notice is standard on all Dev Ashish's modules. A lot of MVP's do this and using a link to their site or as in this case the MVP site is an acceptable alternative.

Although the rules state that linking to a competing or commercial site is not allowed. These type of sites are not a problem.

Mary

[MMcCarthy]

No prob. I did leave his copyright so I wasn't trying to take credit.

Honestly never thought you were, it't just something we try to be careful of. As a developer site we try to be careful of copywrite as much as possible.

Just on a side note I think it absolutley assanign that any programmer would post his source code on the internet or any other public forum and then say you can't use it. Then they shouldn't post it. I know everyone will nit pic saying they didn't say they couldn't use it exactly and you can link to it etc. etc. but as long as it is posted and the copyright is left in then I believe it should be able to be used whereever and whenever someone likes.

I kinda agree with you. I don't think Dev Ashish is trying to stop his code being used just that he doesn't want it republished in full accross various sites.

But I also understand theScripts reason for removing it. The preceding was just my rant on the new idiocracy of society.

Rant away anytime, your opinion is appreciated.

[dale5804]

cheers mshmyob for the code.

[dale5804]

hi, tried the code and i get a ' compile error end sub' message.
also, on my start form i already have two other events, one to display the date/time and a message set as 'on active' and another that counts some data in the linked tables set as 'on load'. obviously i need your code to run first, then the count code and finally the date/time code. any suggestions as to what events the codes should be assigned to.
cheers

[mshmyob]

To get rid of the error - just remove the first and last line (the function lines)
Then make sure you have the words END SUB as the last line of you event- ie make sure it says END SUB at the very botoom of your on load event..

As long as the code goes before the counting of data in the tables you should be fine.

The on activate code you have that displays date/time doesn't access the tables I assume so should have no effect on your program.

hi, tried the code and i get a ' compile error end sub' message.
also, on my start form i already have two other events, one to display the date/time and a message set as 'on active' and another that counts some data in the linked tables set as 'on load'. obviously i need your code to run first, then the count code and finally the date/time code. any suggestions as to what events the codes should be assigned to.
cheers

[mshmyob]

What do you mean 'kinda agree' ;).

Honestly never thought you were, it't just something we try to be careful of. As a developer site we try to be careful of copywrite as much as possible.



I kinda agree with you. I don't think Dev Ashish is trying to stop his code being used just that he doesn't want it republished in full accross various sites.



Rant away anytime, your opinion is appreciated.

[MMcCarthy]

What do you mean 'kinda agree' ;).

Personally I agree its difficult to enforce or expect copywrite on material published on the web. However, as a forum we have an interest in protecting our own articles etc. So although code should be free to use I can understand why there is an attempt to copywrite it against republishing.

[mshmyob]

I am not disagreeing with you. Any articles I agree are copyrighted just like a book or newspaper etc. Even CODE is copyrighted BUT and here is the sticky point... If you don't want someone using it DON't post it. Once you post code obviously it is going to be used and I believe rightly so. Even all the code on theScripts.

I write programs and I don't want people copying them but I don't post the code. If I post any code I expect people will copy it and use it any way they like.

But I agree articles are not to be distributed without the authour's consent but publicly posted code should be used as long as the original authour is given credit.

And anyone who disagrees with me is wrong. (but you are our captain so I don't want to walk the plank ... lol)

Personally I agree its difficult to enforce or expect copywrite on material published on the web. However, as a forum we have an interest in protecting our own articles etc. So although code should be free to use I can understand why there is an attempt to copywrite it against republishing.

[dale5804]

got rid of the error message, so the code runs ok, but doesnt link the tables?? any ideas

[mshmyob]

Did you change the following line ? Can you give me anymore details? Show me what you type in.

I just tested it again and it is working so I need more details from you.

strBeFile = "c:\any directory\backend file name.mdb"

got rid of the error message, so the code runs ok, but doesnt link the tables?? any ideas

[dale5804]

code as below

Dim collTbls As Collection
      Dim i As Integer
      Dim strTbl As String
      Dim dbCurr As Database
      Dim dbLink As Database
      Dim tdfTables As TableDef
      Dim strBeFile As String
      Dim collTables As New Collection
      Dim tdf As TableDef
 
      ' get the current linked table definitions
          Set dbCurr = CurrentDb
 
          dbCurr.TableDefs.Refresh
 
      ' end
          'First get all linked tables in a collection
          For Each tdf In dbCurr.TableDefs
              With tdf
                  If Len(.Connect) > 0 Then
                          collTables.Add Item:=.Name & .Connect, Key:=.Name
                  End If
              End With
          Next
          Set collTbls = collTables
 
      ' change this string to your drive/directory/filename
 
      strBeFile = "c:\SCRM BE.mdb"
 
                  Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
 
        ' start linking your tables - start from the last and work your way down
           For i = collTbls.Count To 1 Step -1
                  strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
                      Set tdfTables = dbCurr.TableDefs(strTbl)
                      With tdfTables
                          .Connect = ";Database=" & strBeFile
                          .RefreshLink
 
           End With
          Next
 

[mshmyob]

This looks suspicious

strBeFile = "c:\SCRM BE.mdb"

Is SCRM a directory??? If so put:

strBEFile="c:\SCRM\be.mdb"

or

If it is a file change the name of the file to SCRM_BE.mdb

and then change the line in code to

strBeFile = "c:\SCRM_BE.mdb"

code as below

Dim collTbls As Collection
      Dim i As Integer
      Dim strTbl As String
      Dim dbCurr As Database
      Dim dbLink As Database
      Dim tdfTables As TableDef
      Dim strBeFile As String
      Dim collTables As New Collection
      Dim tdf As TableDef
 
      ' get the current linked table definitions
          Set dbCurr = CurrentDb
 
          dbCurr.TableDefs.Refresh
 
      ' end
          'First get all linked tables in a collection
          For Each tdf In dbCurr.TableDefs
              With tdf
                  If Len(.Connect) > 0 Then
                          collTables.Add Item:=.Name & .Connect, Key:=.Name
                  End If
              End With
          Next
          Set collTbls = collTables
 
      ' change this string to your drive/directory/filename
 
      strBeFile = "c:\SCRM BE.mdb"
 
                  Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
 
        ' start linking your tables - start from the last and work your way down
           For i = collTbls.Count To 1 Step -1
                  strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
                      Set tdfTables = dbCurr.TableDefs(strTbl)
                      With tdfTables
                          .Connect = ";Database=" & strBeFile
                          .RefreshLink
 
           End With
          Next
 

[dale5804]

hi, changed code to
strBeFile = "c:\SCRM_BE.mdb"
and changed file name, but still doesnt link. any more suggestions please.

[mshmyob]

I am assuming that in your front end code you orginally at some time had manually linked the back end tables. Is this correct?

Where did you put the code?

hi, changed code to
strBeFile = "c:\SCRM_BE.mdb"
and changed file name, but still doesnt link. any more suggestions please.

[dale5804]

basically what i have done, is start with my current database, called SCRM. saed a second copy of it, and called it SCRM BE.
Within SCRM BE, i deleted all the forms etc, leaving only the tables.
Renamed SCRM to SCRM FE, and deleted all its tables, leaving only forms, queries etc.

I know if i use the 'get external data' with access i can link the two and it works. But i can not do this at work, where SCRM will be deployed, hence the need for some code.

The code u kindly supplied is on the start up form with SCRM FE.
Does that answer your question....
cheers

[mshmyob]

Ah ha.. Ok first not the best way to seperate out the fe/be but OK.

To use the code supplied you MUST first link the front end to the back end manually (say at home). Put whatever path/fielname you want in the code supplied that matches your path/filename at work. When you get to work and it runs - presto.

To test it at home just manully link then move your BE to a different directory and supply the path/filename to the code and start your program again and it should work.

Let me know.

basically what i have done, is start with my current database, called SCRM. saed a second copy of it, and called it SCRM BE.
Within SCRM BE, i deleted all the forms etc, leaving only the tables.
Renamed SCRM to SCRM FE, and deleted all its tables, leaving only forms, queries etc.

I know if i use the 'get external data' with access i can link the two and it works. But i can not do this at work, where SCRM will be deployed, hence the need for some code.

The code u kindly supplied is on the start up form with SCRM FE.
Does that answer your question....
cheers

[dale5804]

sorry, just to clarify, manual link?

[dale5804]

sorry, just to clarify, manual link?

done it.... tested it at home like u said and it works. fantastic. thank you so much for your help, your a star!

[mshmyob]

No problem. Glad we could help you.

done it.... tested it at home like u said and it works. fantastic. thank you so much for your help, your a star!