Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55

    Unanswered: Question On MS Access Table(s) Structure

    I have built a database for managing housing. I have 29 fields that contain the property attributes. At this point, I am using one table, two forms and one report... At any given time I will have about 200 to 300<or so> 'active' properties(Records).

    I am not a programmer, I have just enough ability here to be 'dangerous'

    My question is....
    As Records are entered, managed, then go inactive, we have been deleteing the records that go inactive... has been working. But some properties become active again and we must re-enter all 29 fields again.

    I am thinking to put the inactive properties in an "archiveTable". That would mean two tables.... Active and Archived....
    The Archived table would grow over time (As you could imagine). I am thinking since we used the Active table on a daily basis, I do not want to burden the Active table with all these accumulating inactive records.

    I would think I would <somehow> move the record(s) from the active table to the inactive table and back as needed... I would think I would put a button on a 'active house form' and 'inactive housing form' to move the records back and forth...

    Are two tables the best way? or use one table that could baloon over time? (Maybe I already answered my question?) Since I am not a DB gru nor programmer, I thought I'd ask the smart peeps out there the best way to build this because once structured, it would be difficult to change later I would imagine, so I want to do this well out of the chute.

    Please keep the answer simple for me because when I incorporate my final direction I have very basic ability here.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi MesaFloyd

    Well to keep it simple and not to have to make an archive. Why not place a tickbox in the existing table where the details of the house is kept -(not the tenant). This way you can set the query to report

    1. Active houses - this could be when the box is ticked (-1)

    2. Inactive houses - this is when there is no tick (0).

    Then set up a query to look for in active houses. Bring up a list and tick to activate. That way you can easily report active (report 1) inactive (report 2) of set a query for all houses (report 3)


    Hope thats a help

    Gareth


    Keeping it simple

  3. #3
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi Gareth,
    Thanks for getting back to me.

    I do have a tick box now for that purpose. While it is there, I have not programmed it in yet because I got to thinking about how big this table could/would get... that's why I posted this thread... could this be a problem?

    I could see this table to be 1-2-3,000 records in 1-2 years.

    If I use the tick box, and make those records invisible to the form, then I am not sure how I would manage the search process of finding it again... suppose a form for viewing all Records(w and w/o ticks), and a form for viewing(ticks only)

    I must think about this -
    Your probably right, keep one table... add another form....for my simple mind... ;-)

    Thanks so much for stimulating my thinking on this early Sunday morning... what!, working on Sunday morning ?!?!?!? gadzooks

    Mesafloyd

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    The point you made about -

    suppose a form for viewing all Records(w and w/o ticks), and a form for viewing(ticks only)
    is on the right track

    2000 - 3500 records is not a problem as long as you keet the DB compressed. I use a similar protocol for a training monitoring DB and thats heading toward 7500 and still very quick. Archiving is fine IF your going to use that record at some time next yearish - but for less than that and for reconciling capital expenditure Id go tick box, besides if someone asks for the information quickly your not digging about for it.

    It is possible to create a seach form which asks for the tick (-1) No-(0) and * for all so you can migrate the complexity of the DB as you become more experienced.

    gareth

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    There's no need for another form.

    Just create a form that shows all records and add filter functionality to it to only show the active or inactive records.

  6. #6
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Thanks to you both for your suggestions.
    It helps to share.

    I will move forward with my existing table and forms I have.

    I will try to code the present form to read the tick and display Current, and Current+Archived Records.

    Thanks again.
    MesaFloyd

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Here is what I'ld do....

    The best way to keep your DB from growing out of proportion is to in fact Delete or Archive data no longer is use. If you want to access old data again then obviously Archiving that data is the way to go but into a different .mdb file.

    Keep the Archive tick in table and make sure the checkbox is available on Form. For the sake of argument we'll name it simply Archive.

    Leaving your current database open, open another instance of MS-Access.

    Create a new database. Give it the same name as your current database but add _Archive to the end of the name (ie: MyDBName_Archive.mdb).

    Go back to the database window of your current database and go to the tables list. Click on the table you want to Archive and Trim then select the copy button in the Access tool bar.

    Now switch to the new database and select the Paste button in the Access tool bar. A dialog box will display asking you if you want to copy 'Table structure Only' or 'Data and Table Structure'. Select 'Table Structure Only'.

    The Table should be pasted to the new DataBase with the same name as the original. Rename the new table by adding _Archive to the existing name (ie: myTableName_Archive). Now close the new Access database (MS-Access itself).

    Make sure the new .mdb file resides within the same Folder (directory) as your current Database.

    [Note: All of the above procedures can be automatically achieved via VBA code when required. If this is of interest to anyone then let me know.]

    Back to your current Database....

    Open your Form in design view.

    In the OnCurrent event of your Form Insert this code at the top before any other code in the event (if any):

    Code:
    If DCount("[myTableRecordID]", "myTableName", "[myTableRecordID]" > 0) >= 10000 Then
       If MsgBox("Your TableName table now contains more than 10,000 Records. " & _
                 "Do you want to Archive and trim records from this table?", _
                 vbQuestion + vbYesNo, "Archive/Trim Table") = vbYes Then
          Call ArchiveAndTable("myTableName", "PathAndNameOfArchveMDB", "ArchiveTableName")
       End If
    End If
    Here we have preset a limit of 10,000 records or more before a automatic Archive attempt is supplied to the User. Normally, you wouldn't want just any User the capability of preceeding with such a thing so this code could be placed into a Command Button instead, perhaps named CheckLimits or something.

    Place the Following code into a Database Code Module (Reference to DAO 3.x required):

    Code:
       
    Public Function ArchiveAndTrimTable(tbl As String, ArchiveMDBPath As String, _
                    ArchiveTableName As String)
       ' Declare Variables.
      Dim SQLstrg As String
       Dim NumberToArchive As Long
       Dim ErrorMsg As String
     
       ' Ignore Errors for later processing.
       On Error Resume Next
     
       ' Test Parameters
       If DoesFileExist(ArchiveMDBPath) = True Then
          If DoesTableExist(ArchiveTableName, ArchiveMDBPath) = False Then
             ErrorMsg = "Can Not Find The Specified ArchiveTable (" & ArchiveTableName & _
                        ") within the Specified Database MDB File (" & ArchiveMDBPath & ")."
             GoTo DisplayError
          End If
       Else
         ErrorMsg = "Can Not locate the MDB Database file specified:" & vbCrLf & vbCrLf & _
                    ArchiveMDBPath
         GoTo DisplayError
       End If
     
       ' Set up the Query String that will do the job.
       SQLstrg = "INSERT INTO [" & ArchiveTableName & "] IN '" & ArchiveMDBPath & "' " & _
                 "SELECT * FROM [" & tbl & "] WHERE Archive = True;"
       CurrentDb.Execute SQLstrg
     
       ' If there wan an Error then indicate that to User.
      If Err <> 0 Then
          ErrorMsg = "Error With Archive SQL Query string!"
          GoTo DisplayError
       End If
     
       ' Trim out (Delete) the Record from within Current DB.
      SQLstrg = "DELETE FROM [" & tbl & "] WHERE Archive = True;"
       CurrentDb.Execute SQLstrg
     
       ' If there wan an Error then indicate that to User.
       If Err <> 0 Then
          ErrorMsg = "Error With Trim/Delete SQL Query string!"
          GoTo DisplayError
       End If
     
       ' Inform User Of SUCCESS.
      MsgBox "Successfully Archived and Trimmed all Records flaged for Archiving.", _
              vbInformation, "Archive & Trim Successfull"
       Exit Function
     
    DisplayError:
       MsgBox ErrorMsg, vbCritical
       If Err <> 0 Then Err.Clear
    End Function
     
    ' These Functions Are Required....
     
    Public Function DoesTableExist(TableName As String, Optional DBName As String) As Boolean
       ' ---------------------------------------------------------------------
       ' See if a Table Exists within the Provided Database (Current or External)
       '
       ' Returns:
       ' True  - if Table Exists.
       ' False - if Table does not Exist.
       ' ---------------------------------------------------------------------
      Dim db As Database
       Dim i As Integer
       Dim tbl As String
     
       If DBName <> "" Then
          Set db = OpenDatabase(DBName)
       Else
          Set db = CurrentDb
       End If
     
       db.TableDefs.Refresh
       tbl = Trim(TableName)
       If Left$(tbl, 1) = "[" And Right$(tbl, 1) = "]" Then
          tbl = Mid$(tbl, 2, Len(tbl) - 2)
       End If
       For i = 0 To db.TableDefs.Count - 1
           If tbl = db.TableDefs(i).Name Then
               'Table Exists
               DoesTableExist = True
               Exit For
           End If
       Next i
       db.Close
       Set db = Nothing
    End Function
     
    Public Function DoesFileExist(PathStrg As String) As Integer
        Dim a$
        On Error Resume Next
        a$ = Dir(PathStrg, 14)
        If a$ <> "" And Err = 0 Then DoesFileExist = -1 Else Err.Clear
    End Function
    Now if you want to display all the Archived Records into your Very same Form, then Create a button on the Form (name it ShowArchives) and in the OnClick event for that button copy and paste this code:
    Code:
       Me.RecordSource = "SELECT * FROM myTableName_Archive IN '" & _
                      Application.CurrentProject.Path & "\MyDBName_Archive.mdb';"
       If Me.RecordSet.RecordCount > 0 Then 
          Me.ResetArchived.Enabled = True
       End If
    Now create another button on your Form and name it ResetArchived. Place this code into the OnClick event for this button:
    Code:
       Dim SQLstrg As String
       Dim ErrorMsg As String
     
       ' Ignore Error for later processing.
       On Error Resume Next
     
       ' Set up the Reset query String. 
       SQLstrg = "INSERT INTO myTableName SELECT * FROM myTableName_Archive IN '" & _
                 Application.CurrentProject.Path & "\MyDBName_Archive.mdb' WHERE " & _
                 "Archive = False;"
     
       ' Fire the Query...
      CurrentDb.Execute SQLstrg
     
       ' If there was an Error then inform User and leave.
       If Err <> 0 Then
          ErrorMsg = "Error With Reset Archive Query String!"
          Goto DisplayResetError
       End If
       ' Delete the Archived Record from within the Archive DB.
       ' Set up the Delete query String. 
       SQLstrg = "DELETE FROM myTableName_Archive IN '" & _
                 Application.CurrentProject.Path & "\MyDBName_Archive.mdb' WHERE " & _
                 "Archive = False;"
     
       ' Fire the Query...
      CurrentDb.Execute SQLstrg
     
       ' If there was an Error then inform User and leave.
      If Err <> 0 Then
          ErrorMsg = "Error With Delete Archive Query String!"
          Goto DisplayResetError
       End If   
     
       ' Requery the Form so changes are shown in effect.
       Me.Requery
     
       ' Get Outta here.
      Exit Sub
     
    DisplayResetError:
       MsgBox ErrorMsg, vbCritical, "Query String Error"
       Err.Clear
    You can see where this is going right...If you want to place the Archived records back into the original table within the current Database then simply remove the check mark from the Archive (Yes/No) field of all the records you want to reset then click the ResetArchived button.

    Now if you want to display all the Current Records again into your Very same Form, then Create yet another button on the Form (name it ShowCurrent) and in the OnClick event for that button copy and paste this code:

    Code:
       Me.ResetArchived.Enabled = False
       Me.RecordSource = "myCurrentTableName"
    There you go. You will of course need to change all the names indicated in red italic to their proper names.

    If there are any Errors with this code then please let me know and I'll correct it. There may be some typos since most of it is from the top of my head.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  8. #8
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Thanks Cyberlynx for that thoughtful reply....
    Looks like a great way to solve a 'ballooned' table.

    It is well thought out and is very thorough thanks a bunch.

    Kindest regards,
    MesaFloyd

Posting Permissions

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