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:
[CODE=vb]
Dim tdf As DAO.TableDef, strConnect As String

For Each tdf In CurrentDb.Table Defs
strConnect = tdf.Connect
If Len(strConnect) > 0 Then
Debug.Print "[" & tdf.Name & "] linked to: " & _
Right$(strConne ct, Len(strConnect) - InStrRev(strCon nect, "="))
End If
Next[/CODE]
OUTPUT:
[CODE=text]
[Switchboard Items] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblAssessmentSt eps] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblBackupTraini ng] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblCategories] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblChemicalsSto red] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblCRFAC00] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblEmployee] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblEvaluationCo mments] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblEvaluationSt epsForItems] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblEvent] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblExpendedTrng Items] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblHazMatBackup s] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblHazMatTopics] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblHMAUResponse] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblIncidentInfo] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblInventoryTra nsactions] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblLocSublocQty] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblMaintInterva l] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblMaintInterva lData] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblMaintRepairs] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblManufacturer s] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblMasterItemsL ist] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblMultipleSupp liers] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblPartsList] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblPCBs] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblProjectCodes] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblProjectTeam] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblQuestions] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblRank] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblSavedTests] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblServiceDates] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblSession] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblSites] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblStorLoc] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblSubLoc] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblSuppliersPro vider] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblSystemReques ts] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblTechnicalInf ormation] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblTopics] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblTrainingInSe rvice] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblTrainingModu les] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblTrainingSubj ect] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblTransactionC odes] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblUnitLevels] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblUnits] linked to: C:\HAZ-MAT\HMAU_Data.m db
[tblUsedTrngItem s] linked to: C:\HAZ-MAT\HMAU_Data.m db[/CODE]

[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.