FrontEnd BackEnd linking

[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\backe nd 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:\S CRM\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!