Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Questions about splitting a 2010 Access database

    Hi! I have read through some of the other threads on this subject and I am more confused than ever.

    I have split an Access 2010 database with the tables being in the backend. Some of these tables were originally imported from an Accounting database. Before I split the database, I had a series of macros that would delete these "Accounting tables" and then import the latest version of these tables from the accounting database.

    I need to continue to update those tables on an on-going basis. As a result, I created a startup form that had a control to backup the database and another control to run the macros to update the accounting tables. I then opened the front end and relinked all of the tables in the back end to the front end.

    From what I have read, I believe that I should not have done that (i.e. the backend should contain tables only- no macros, forms, etc.). If that is correct, what is the proper way to deal with these tables that need to be replaced on an ongoing basis? I know I can link the accounting tables from the accounting database to tables in the backend (rather than import them) but I would rather not do that for a variety of reasons.

    If what I am doing is ok, can I use VBA or macros or something in the front end to do these updates at a click of a button?

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    If the tables need to be imported periodically, the macro to do it does need to be in the backend, so I see nothing wrong in what you have done.
    The usual recommendation covers, IMO, everything related to the application that uses the backend data. Obviously your macro, in this case, is not application related, but has the goal of making sure you have the right data, so the usual recommendation does not apply.

  3. The Following User Says Thank You to ruirib For This Useful Post:

    bobguest (2012-01-16)

  4. #3
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    <<From what I have read, I believe that I should not have done that (i.e. the backend should contain tables only- no macros, forms, etc.)>>c
    I am not sure what you read but it has been my experience that splitting your database is definitely the correct thing to do. I have yet to find a single good reason to not keep a database split. If this were my project I would split the database. Maybe even have multiple back ends. Unless you are using Referential Integrity (RI) between the Accounting tables you are importing and your other tables then I would probably use a separate back end for the Accounting. This way if the import process every had an issue it would not put the other tables at risk.


    <<If what I am doing is ok, can I use VBA or macros or something in the front end to do these updates at a click of a button? >>

    I now you can use VBA code to do what you need. I am not sure is macros are powerful enough to do the job.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  5. The Following User Says Thank You to HiTechCoach For This Useful Post:

    bobguest (2012-01-16)

  6. #4
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for your reply! I am glad to see I am headed in the right direction. Would it be possible to create something in the frontend that would automatically run the macro located in the backend and then (ideally) run the linked table manager from the fromt end?

  7. #5
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for your response! I guess I wasn't clear- what I meant was not that I should not split the database but that I couldn't put forms, macros, etc in the backend. Your idea of having more than one backend looks like a great idea! This could solve some other issues I was having if I had two backends. I asked previously if there was any way to run the macro that was located in the backend from the front end and you suggested VBA. Could you be more specific? That is, would I have to open the backend while in the front end and is there VBA code that will run a macro?

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bob,

    Here's some code I use to run from the frontend to relink the backend.
    Code:
    Option Compare Database
     Option Explicit
    
     '+---------------------------------------------------------------------------+
     '| Version: 7.0                        Programmed by: The Computer Mentor    |
     '| Dated  : 01/10/2012                           aka:        |
     '+---------------------------------------------------------------------------+
    
     Public zDBPath        As String
     Public zStatusMsg     As String
     Public lTimerInterval As Long
     Public Const zCodeVersionNo = "7.0"
    
    '                            +------------------+                 +----------+
     '----------------------------|  ReLinkTable()   |-----------------| 01/11/11 |
     '                            +------------------+                 +----------+
     'Called by: Macro - AutoExec
     'Calls    : [Utilities] zGetDBPath()
     '           [Utilities] HideDBWinsdow()
     'Globals  : lTimerInterval
     
     Function ReLinkTable()
    
        Dim zDBFullName    As String
        Dim zBEDBFN        As String
        Dim zTableName(12) As String
        Dim zUserName      As String
        Dim iTblCnt        As Integer
       
        GoTo StartLinking
    
     FileDoesNotExist:
    
        If Err.Number = 7874 Then
          Resume Next
        Else
          MsgBox "Error No: " & Err.Number & vbCrLf & _
                 "Description: " & Err.Description
        End If
    
     StartLinking:
    
        zTableName(0) = "Docks"
        zTableName(1) = "Lots"
        zTableName(2) = "Owners"
        zTableName(3) = "PhoneDir"
        zTableName(4) = "StorageLots"
        zTableName(5) = "tblAuxNumbers"  '*** Last table in ARB_be.mdb       ***
        zTableName(6) = "Builders"       '*** Start of tables in ARBReqs.mdb use index in If iTblCnt = below***
        zTableName(7) = "Letters"
        zTableName(8) = "ARBMembers"
        zTableName(9) = "ARBAssignments"
        zTableName(10) = "Requests"
        zTableName(11) = "RequestTypes"
       
        zDBPath = zGetDBPath()  'You can just hardcode a path to the backend in place of this function!
        zUserName = Environ("USERNAME")
       
        HideDBWindow IIf(UCase(zUserName) = "BRUCE", False, True)
       
        If zDBPath = "Error" Then
          MsgBox zUserName & ": is not an authroized user!", _
                     vbOKOnly + vbCritical, "Error: User Not Authorized"
          ExitDB
        End If
       
        Forms("Switchboard").Caption = _
           Forms("Switchboard").Caption & "  " & zDBPath
        zBEDBFN = "ARB_be.mdb"
        zDBFullName = zDBPath & zBEDBFN
      
        For iTblCnt = 0 To UBound(zTableName) - 1
       
           If iTblCnt = 6 Then            '*** Switch back end DB files ***
             zBEDBFN = "ARBReqs_be.mdb"
             zDBFullName = zDBPath & zBEDBFN
           End If
          
           On Error GoTo FileDoesNotExist
           '*** Delete TableDef from FRONT end DB
           DoCmd.DeleteObject ObjectType:=acTable, ObjectName:=zTableName(iTblCnt)
       
           '*** Copy TableDef from BACK end DB to FRONT end DB - Keep in sync!
           DoCmd.TransferDatabase TransferType:=acLink, _
                                  DatabaseType:="Microsoft Access", _
                                  DatabaseName:=zDBFullName, _
                                    ObjectType:=acTable, _
                                        Source:=zTableName(iTblCnt), _
                                   Destination:=zTableName(iTblCnt)
           On Error GoTo 0
          
        Next iTblCnt
                                 
         zStatusMsg = "Tables have been Re-Linked"
         lTimerInterval = 3000    '*** 3 Seconds ***
         DoCmd.OpenForm "frmStatusMsg", acNormal
         Application.SetOption "Themed Form Controls", False
         StdMenuToggle "False"
        
     End Function    'ReLinkTable()
    There is some code in here to update some menus, etc. but you can ignore it. Hope this helps.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Quote Originally Posted by bobguest View Post
    Thanks for your response! I guess I wasn't clear- what I meant was not that I should not split the database but that I couldn't put forms, macros, etc in the backend. Your idea of having more than one backend looks like a great idea! This could solve some other issues I was having if I had two backends. I asked previously if there was any way to run the macro that was located in the backend from the front end and you suggested VBA. Could you be more specific? That is, would I have to open the backend while in the front end and is there VBA code that will run a macro?

    There is not need to have anything but data in the back end. Everything you need to do can and I believe should be done in the app/front end. This is very critical if the is a multi user application using an Access database as the back end (data)

    I would use a separate back end for the data you are importing. What I find best is to not delete the table and then reimport it bu to run a delete query to empty the table. Next run an append query to load the data back into the tale. This way the table name is fixed. You can set data types, indexes, etc.. This way you do not have to recreate all the table design changes every time you delete the table and import it again. Another advantage to this method is that you don't have to worry about the linked tables name sin the front end since the table is never deleted!
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  10. The Following User Says Thank You to HiTechCoach For This Useful Post:

    bobguest (2012-01-17)

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bob,

    One additional thought. If you are using a switchboard you either need to wait until you split the DB before setting it up or if it is already setup move the Table that controls it from the Back-End to the Front-End. I've had problems when the Switchboard Table is in the Back-end and it's easier when you can make all the changes in the front end as you can link the FE to a test BE while you are making changes to the Switchboard, Reports, Queries, Code, etc. w/o worrying about it not being in the final production copy of the BE.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    bobguest (2012-01-17)

  13. #9
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks! I like both the idea to create a separate backend for the Accounting tables and the method you suggest for populating those tables. I will try it out this weekend.

  14. #10
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for both of your posts! I will try them out this weekend. I do have a startup form but I can't remember whether it is a switchboard or not.

  15. #11
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Good luck this weekend. Let us know how it goes.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •