none
discern whether database is split or NOT

    Question

  • Hello Again -

    In short I need (I have seen it somewhere - but can no longer trace it) a routine that determines whether he database being worked on is split or not - any help please!

    thanks

    db


    • Edited by davidbailie Monday, December 10, 2012 8:32 AM
    Monday, December 10, 2012 8:32 AM

Answers

  • Temp tables are simply tables that are re-created as needed. There is nothing special about them.

    If you locate them in the FE, then the code I posted will work fine. What it basically does is determine if the table is a linked table.

    If you keep your temp tables in a separate file, and link them to the FE, then they are linked tables also.

    In that case, simply use a consistent, specific naming convention like <yourtemptable>_tmp and  modify the code as follows.

    Public Function DbIsSplit() as Boolean

    Dim dB as DAO.Database
    Dim tdF as DAO.Tabledef

    Set dB = CurrentDb

    For Each tdF in dB.TableDefs
    If Len(tdF.Connect) > 0 and Not tdF.Name like "*_tmp" Then
    DbIsSplit = True
    Exit For
    End If
    Next
    Set tdF = Nothing
    Set dB = Nothing




    • Edited by Alphonse G Monday, December 10, 2012 10:41 PM
    • Marked as answer by davidbailie Tuesday, December 11, 2012 3:12 PM
    Monday, December 10, 2012 8:03 PM
  • Public Function DbIsSplit() as Boolean

    Dim dB as DAO.Database
    Dim tdF as DAO.Tabledef

    Set dB = CurrentDb

    For Each tdF in dB.TableDefs
    If Len(tdF.Connect) > 0 and Not tdF.Name like "*_tmp" Then
    DbIsSplit = True
    Exit For
    Next

    Set tdF = Nothing
    Set dB = Nothing


    Hi David,

    The above is a beautiful example of (not tested) air code.
    It misses the End If statement:

        For Each tdF in dB.TableDefs
            If Len(tdF.Connect) > 0 and Not tdF.Name like "*_tmp" Then
                DbIsSplit = True
                Exit For
            End If         <====
        Next

    Imb.

    • Marked as answer by davidbailie Tuesday, December 11, 2012 3:12 PM
    Monday, December 10, 2012 10:35 PM
  • To add to what Imb stated. You are only thinking about your particular case.

    Think of all other possible cases and you would see why it would not make sense for Access to delete the table in the BE. Just for one, what if there were multiple FE's using the same BE for different purposes. One FE no longer uses a particular table in the BE, but others do. If you delete the linked table in the one FE and the BE table was also deleted, what would happen to the other FE's?

    • Marked as answer by davidbailie Thursday, December 13, 2012 1:29 PM
    Wednesday, December 12, 2012 1:09 PM

All replies

  • On the assumption that your linked tables would only be back end tables, as opposed to another file being used for temp tables, the following (air code) should do it.

    Public Function DbIsSplit() as Boolean

    Dim dB as DAO.Database
    Dim tdF as DAO.Tabledef

    Set dB = CurrentDb

    For Each tdF in dB.TableDefs
    If Len(tdF.Connect) > 0 Then
    DbIsSplit = True
    Exit For
    Next

    Set tdF = Nothing
    Set dB = Nothing

    Monday, December 10, 2012 1:20 PM
  • Hello Alphonse

    many thanks for the reply - I can see how your code operates - but ....

    Insofar as I understand your comment :- "On the assumption that your linked tables would only be back end tables, as opposed to another file being used for temp tables, the following (air code) should do it" -

    I have a number of tables which I delete/recreate once a month (effectively temp tables ???) - for reasons known only to myself when I first started designing the db some 5 years ago !! -  basically in fact to stop the auto numbering running out of hand -  certain dropdown menus containing 400 + items are easily recreated 50 times a session - maybe irrelevant - but thought sensible at the time. 

    Once recreated - I am pretty sure that they then form part of the front end - I have yet to decipher the facts fully- but it seems to be the case. In testing I have relinked the tables but have yet to see how this behaves under .accde

    If the tables mentioned are in fact 'temp tables' then your code presumably will not work.

    I would appreciate any observations you have - including the term 'AirCode' - which I gain a sense of but please elucidate...

    thanks

    db





    • Edited by davidbailie Monday, December 10, 2012 5:17 PM
    Monday, December 10, 2012 5:12 PM
  • Temp tables are simply tables that are re-created as needed. There is nothing special about them.

    If you locate them in the FE, then the code I posted will work fine. What it basically does is determine if the table is a linked table.

    If you keep your temp tables in a separate file, and link them to the FE, then they are linked tables also.

    In that case, simply use a consistent, specific naming convention like <yourtemptable>_tmp and  modify the code as follows.

    Public Function DbIsSplit() as Boolean

    Dim dB as DAO.Database
    Dim tdF as DAO.Tabledef

    Set dB = CurrentDb

    For Each tdF in dB.TableDefs
    If Len(tdF.Connect) > 0 and Not tdF.Name like "*_tmp" Then
    DbIsSplit = True
    Exit For
    End If
    Next
    Set tdF = Nothing
    Set dB = Nothing




    • Edited by Alphonse G Monday, December 10, 2012 10:41 PM
    • Marked as answer by davidbailie Tuesday, December 11, 2012 3:12 PM
    Monday, December 10, 2012 8:03 PM
  • Air code is "pulled out of the air" based on experience and the idea "this might work".

    Beware of applying air code without carefully vetting it.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, December 10, 2012 8:34 PM
  • Public Function DbIsSplit() as Boolean

    Dim dB as DAO.Database
    Dim tdF as DAO.Tabledef

    Set dB = CurrentDb

    For Each tdF in dB.TableDefs
    If Len(tdF.Connect) > 0 and Not tdF.Name like "*_tmp" Then
    DbIsSplit = True
    Exit For
    Next

    Set tdF = Nothing
    Set dB = Nothing


    Hi David,

    The above is a beautiful example of (not tested) air code.
    It misses the End If statement:

        For Each tdF in dB.TableDefs
            If Len(tdF.Connect) > 0 and Not tdF.Name like "*_tmp" Then
                DbIsSplit = True
                Exit For
            End If         <====
        Next

    Imb.

    • Marked as answer by davidbailie Tuesday, December 11, 2012 3:12 PM
    Monday, December 10, 2012 10:35 PM
  • Imb, thanks for the correction. I edited to correct - oh the perils of copy/paste :).
    Monday, December 10, 2012 10:43 PM
  • Many Thanks all for your responses

    as I mentioned above the Tables that are deleted & rebuilt every month do in fact become unlinked - they contain no permanent data and are irrelevant in terms of bloating as the Front End compacts on closing!

    I am going to assume that if using the routine above any one table that elicits the response ''DbIsSplit = True is sufficient to confirm it is a split database - job done - 

    I haven't included yet - but thanks again for your help

    'Air Code' - of course - plucked out of 'thin'.... :)

    db


    • Edited by davidbailie Tuesday, December 11, 2012 3:51 PM
    Tuesday, December 11, 2012 3:12 PM
  • mmmh! ..

    I have discovered an anomoly which someone may care to clear up for me.

    The tables as mentioned above which are periodically deleted and rebuilt show up in the front end as 'unlinked' - (having originally been linked)the linked arrow adjacent to them has now disappeared.

    If I go to the relink menu item - the relevant tables cannot be seen for linking - of course they don't BUT

    On opening the BE data base standalone - the five tables can still be seen amongst the rest - I did a test and they are obviously not functioning - though the Front end is working fine

    Just wondering why they have not been physically deleted ?? Just a quirk in access ??

    ta

    db

       


    • Edited by davidbailie Tuesday, December 11, 2012 3:49 PM
    Tuesday, December 11, 2012 3:47 PM
  • You don't say where/how you create these tables. Seems like you have created them in the FE as well as the BE, but not linked to the BE.

    If you run a make table query in the FE, that is where the table will be created.

    If you do it in the BE, then that is where they will be created and if you want to see them in the FE, you need to link them.

    Tuesday, December 11, 2012 3:54 PM
  • Thanks for the response Alphonse

    Perhaps I should indeed have said yes they are created & deleted from the front end

    and yes I do understand that the tables are created in the front end ..

    Leaving still the question as to why they are not deleted from the backend -

    It occurs of course that though the database knows that the are linked it doesn't apply it's knowledge and I have to give it a helping hand !

    db  


    • Edited by davidbailie Wednesday, December 12, 2012 8:24 AM
    Wednesday, December 12, 2012 8:23 AM
  • Perhaps I should indeed have said yes they are created & deleted from the front end

    and yes I do understand that the tables are created in the front end ..

    Leaving still the question as to why they are not deleted from the backend -

    Hi David,

    Is there a kind of contradiction where the tables are created and deleted?

    If you delete a table from the front end, without code, and it is a linked table, then you just delete the link to the external table. To delete a table from the backend you have to go to the backend database, through code or directly.

    Imb.

    Wednesday, December 12, 2012 9:00 AM
  • To add to what Imb stated. You are only thinking about your particular case.

    Think of all other possible cases and you would see why it would not make sense for Access to delete the table in the BE. Just for one, what if there were multiple FE's using the same BE for different purposes. One FE no longer uses a particular table in the BE, but others do. If you delete the linked table in the one FE and the BE table was also deleted, what would happen to the other FE's?

    • Marked as answer by davidbailie Thursday, December 13, 2012 1:29 PM
    Wednesday, December 12, 2012 1:09 PM
  • Yes thanks Alphonse - get the picture

    Regards

    db

    Wednesday, December 12, 2012 5:40 PM