Questions about splitting a 2010 Access database

[bobguest]

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?

[ruirib]

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.

[HiTechCoach]

<<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.

[bobguest]

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?

[bobguest]

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?

[RetiredGeek]

Bob,

Here's some code I use to run from the frontend to relink the backend.

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.

[HiTechCoach]

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!

[RetiredGeek]

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.

[bobguest]

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.

[bobguest]

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.

[HiTechCoach]

Good luck this weekend. Let us know how it goes.