454,963 Members | 1,238 Online
Need help? Post your question and get tips & solutions from a community of 454,963 IT Pros & Developers. It's quick & easy.

 P: 48 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. Feb 5 '08 #1
27 Replies

 Expert 5K+ P: 8,679 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers [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 Feb 6 '08 #2  Expert 100+ P: 904 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. Feb 6 '08 #3  Expert 100+ P: 897 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 Feb 6 '08 #4  Expert 100+ P: 904 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 Feb 6 '08 #5  P: 48 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?? Feb 6 '08 #6  Expert 100+ P: 904 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?? Feb 6 '08 #7  Expert 100+ P: 904 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. Expand|Select|Wrap|Line Numbers 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?? Feb 6 '08 #8

 Expert Mod 10K+ P: 14,534 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 Feb 7 '08 #9

 Expert 100+ P: 904 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 Feb 7 '08 #10

 Expert Mod 10K+ P: 14,534 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. Feb 7 '08 #11

 P: 48 cheers mshmyob for the code. Feb 7 '08 #12

 P: 48 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 Feb 7 '08 #13

 Expert 100+ P: 904 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 Feb 7 '08 #14

 Expert 100+ P: 904 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. Feb 7 '08 #15

 Expert Mod 10K+ P: 14,534 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. Feb 7 '08 #16

 Expert 100+ P: 904 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. Feb 7 '08 #17

 P: 48 got rid of the error message, so the code runs ok, but doesnt link the tables?? any ideas Feb 7 '08 #18

 Expert 100+ P: 904 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 Feb 7 '08 #19

 P: 48 code as below Expand|Select|Wrap|Line Numbers 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 Feb 7 '08 #20  Expert 100+ P: 904 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 Expand|Select|Wrap|Line Numbers 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   Feb 8 '08 #21

 P: 48 hi, changed code to strBeFile = "c:\SCRM_BE.mdb" and changed file name, but still doesnt link. any more suggestions please. Feb 9 '08 #22

 Expert 100+ P: 904 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. Feb 9 '08 #23

 P: 48 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 Feb 9 '08 #24

 Expert 100+ P: 904 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 Feb 9 '08 #25

 P: 48 sorry, just to clarify, manual link? Feb 10 '08 #26

 P: 48 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! Feb 10 '08 #27

 Expert 100+ P: 904 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! Feb 10 '08 #28