By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,963 Members | 1,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,963 IT Pros & Developers. It's quick & easy.

FrontEnd BackEnd linking

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
Share this Question
Share on Google+
27 Replies


ADezii
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
  1. Dim tdf As DAO.TableDef, strConnect As String
  2.  
  3. For Each tdf In CurrentDb.TableDefs
  4.   strConnect = tdf.Connect
  5.   If Len(strConnect) > 0 Then
  6.     Debug.Print "[" & tdf.Name & "] linked to: " & _
  7.                  Right$(strConnect, Len(strConnect) - InStrRev(strConnect, "="))
  8.   End If
  9. Next
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. [Switchboard Items] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  2. [tblAssessmentSteps] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  3. [tblBackupTraining] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  4. [tblCategories] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  5. [tblChemicalsStored] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  6. [tblCRFAC00] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  7. [tblEmployee] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  8. [tblEvaluationComments] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  9. [tblEvaluationStepsForItems] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  10. [tblEvent] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  11. [tblExpendedTrngItems] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  12. [tblHazMatBackups] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  13. [tblHazMatTopics] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  14. [tblHMAUResponse] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  15. [tblIncidentInfo] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  16. [tblInventoryTransactions] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  17. [tblLocSublocQty] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  18. [tblMaintInterval] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  19. [tblMaintIntervalData] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  20. [tblMaintRepairs] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  21. [tblManufacturers] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  22. [tblMasterItemsList] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  23. [tblMultipleSuppliers] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  24. [tblPartsList] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  25. [tblPCBs] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  26. [tblProjectCodes] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  27. [tblProjectTeam] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  28. [tblQuestions] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  29. [tblRank] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  30. [tblSavedTests] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  31. [tblServiceDates] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  32. [tblSession] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  33. [tblSites] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  34. [tblStorLoc] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  35. [tblSubLoc] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  36. [tblSuppliersProvider] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  37. [tblSystemRequests] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  38. [tblTechnicalInformation] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  39. [tblTopics] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  40. [tblTrainingInService] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  41. [tblTrainingModules] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  42. [tblTrainingSubject] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  43. [tblTransactionCodes] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  44. [tblUnitLevels] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  45. [tblUnits] linked to: C:\HAZ-MAT\HMAU_Data.mdb
  46. [tblUsedTrngItems] linked to: C:\HAZ-MAT\HMAU_Data.mdb
Feb 6 '08 #2

mshmyob
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

Jim Doherty
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

mshmyob
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

mshmyob
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

mshmyob
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
  1. Function RefreshLinks() As Boolean
  2. Dim collTbls As Collection
  3. Dim i As Integer
  4. Dim strTbl As String
  5. Dim dbCurr As Database
  6. Dim dbLink As Database
  7. Dim tdfTables As TableDef
  8. Dim strBeFile As String
  9. Dim collTables As New Collection
  10. Dim tdf As TableDef
  11.  
  12. ' get the current linked table definitions
  13.     Set dbCurr = CurrentDb
  14.     dbCurr.TableDefs.Refresh
  15. ' end
  16.  
  17.     'First get all linked tables in a collection
  18.     For Each tdf In dbCurr.TableDefs
  19.         With tdf
  20.             If Len(.Connect) > 0 Then
  21.                     collTables.Add Item:=.Name & .Connect, Key:=.Name
  22.             End If
  23.         End With
  24.     Next
  25.     Set collTbls = collTables
  26.  
  27. ' change this string to your drive/directory/filename               
  28. strBeFile = "c:\any directory\backend file name.mdb"
  29.  
  30.             Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
  31.  
  32.   ' start linking your tables - start from the last and work your way down
  33.      For i = collTbls.Count To 1 Step -1
  34.             strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
  35.                 Set tdfTables = dbCurr.TableDefs(strTbl)
  36.                 With tdfTables
  37.                     .Connect = ";Database=" & strBeFile
  38.                     .RefreshLink
  39.                 End With
  40.     Next
  41.    End Function
  42.  
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

MMcCarthy
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

mshmyob
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

MMcCarthy
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

mshmyob
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

mshmyob
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

MMcCarthy
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

mshmyob
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

mshmyob
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
  1. Dim collTbls As Collection
  2.       Dim i As Integer
  3.       Dim strTbl As String
  4.       Dim dbCurr As Database
  5.       Dim dbLink As Database
  6.       Dim tdfTables As TableDef
  7.       Dim strBeFile As String
  8.       Dim collTables As New Collection
  9.       Dim tdf As TableDef
  10.  
  11.       ' get the current linked table definitions
  12.           Set dbCurr = CurrentDb
  13.  
  14.           dbCurr.TableDefs.Refresh
  15.  
  16.       ' end
  17.           'First get all linked tables in a collection
  18.           For Each tdf In dbCurr.TableDefs
  19.               With tdf
  20.                   If Len(.Connect) > 0 Then
  21.                           collTables.Add Item:=.Name & .Connect, Key:=.Name
  22.                   End If
  23.               End With
  24.           Next
  25.           Set collTbls = collTables
  26.  
  27.       ' change this string to your drive/directory/filename
  28.  
  29.       strBeFile = "c:\SCRM BE.mdb"
  30.  
  31.                   Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
  32.  
  33.         ' start linking your tables - start from the last and work your way down
  34.            For i = collTbls.Count To 1 Step -1
  35.                   strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
  36.                       Set tdfTables = dbCurr.TableDefs(strTbl)
  37.                       With tdfTables
  38.                           .Connect = ";Database=" & strBeFile
  39.                           .RefreshLink
  40.  
  41.            End With
  42.           Next
  43.  
Feb 7 '08 #20

mshmyob
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
  1. Dim collTbls As Collection
  2.       Dim i As Integer
  3.       Dim strTbl As String
  4.       Dim dbCurr As Database
  5.       Dim dbLink As Database
  6.       Dim tdfTables As TableDef
  7.       Dim strBeFile As String
  8.       Dim collTables As New Collection
  9.       Dim tdf As TableDef
  10.  
  11.       ' get the current linked table definitions
  12.           Set dbCurr = CurrentDb
  13.  
  14.           dbCurr.TableDefs.Refresh
  15.  
  16.       ' end
  17.           'First get all linked tables in a collection
  18.           For Each tdf In dbCurr.TableDefs
  19.               With tdf
  20.                   If Len(.Connect) > 0 Then
  21.                           collTables.Add Item:=.Name & .Connect, Key:=.Name
  22.                   End If
  23.               End With
  24.           Next
  25.           Set collTbls = collTables
  26.  
  27.       ' change this string to your drive/directory/filename
  28.  
  29.       strBeFile = "c:\SCRM BE.mdb"
  30.  
  31.                   Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
  32.  
  33.         ' start linking your tables - start from the last and work your way down
  34.            For i = collTbls.Count To 1 Step -1
  35.                   strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
  36.                       Set tdfTables = dbCurr.TableDefs(strTbl)
  37.                       With tdfTables
  38.                           .Connect = ";Database=" & strBeFile
  39.                           .RefreshLink
  40.  
  41.            End With
  42.           Next
  43.  
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

mshmyob
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

mshmyob
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

mshmyob
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

Post your reply

Sign in to post your reply or Sign up for a free account.