Advice needed...

ok, so heres the situation..

several databases, all hitting a couple million records in a single table so need to archive..nearing 2gb limit (is this a fixed limit or can i go over?)

created archive databases, one per month, which split it to about 350Mb per database (single table)

what i need to do is still allow the reporting of the historical data in teh monthly archives so whats the best way to do this?

i had though create a reporting front end, ask user for start date and end date, link the relevant archives relating to these dates to allow the reporting and create custom recordsets?

still a bit stuck as to how to create a single recordset from teh multiple archive databases without killing performance? the start database was pretty slow. network based 10Mbit LAN.

looking for suggestions as to an implementation type to facilitate the above. will code the solution myself although examples of how you guys do things are always welcome :thumb:

TIA

B

This is the VBA forum. If you're using Access, a couple million records in one table is way more than it was designed to handle. If you're using SQL server, MySQL, Oracle, etc, you might get better results in the database forum.

1) Assuming you have coded linking/relinking tables, you could (in theory) relink to all the dbs (including new ones) as they are created, then run union queries to put all the totals for each month into one table then use aquery to reformat/sum these together.

2) Or you could pull off the statistics you need as the db is archived (thus not changed?) then just link to those stats tables (which would cut your running time down) and only have the currently live month with pulling info?

3) Upgrade to a bigger db ;)

4) uh, find some other way to get the data you need.

(2) sounds like a good one, as all archived data (in theory) doesn't change, ,so if you pull off some combined stats tables and read those, you won't need to do the recalcs each time. Just unions and adding together :)


Post up what you decide to do :) and whether it works pls (I'm curious!)

ok, heres what ive done so far..
ive also limited the reporting of data to any consecutive 90 days to aid in performance.

VB Code:

Function Link_Tables()
On Error GoTo Link_Tables_Err_Click
 
Dim dbs As Database
Dim tbfNew As TableDef
Dim CurrFile As String
Dim TableName As String
Dim TableNameArray(5) As String
Dim MyCount As Integer
 
'set database and start file
Set dbs = CurrentDb()
CurrFile = Dir("L:\Class Data Archive\Archive\*.mdb")
 
'do while still files to link
Do While CurrFile <> ""
 
TableName = "tbl_" & Left(CurrFile, InStr(CurrFile, ".") - 1)
 
'create new linked table
Set tbfNew = dbs.CreateTableDef(TableName)
 
    'connect table and set name
   With tbfNew
     .Connect = ";database=L:\Class Data Archive\Archive\" & CurrFile
     .SourceTableName = TableName
   End With
    
    'update tabledefs
   dbs.TableDefs.Append tbfNew
   
   'set to next file
   CurrFile = Dir
    
'repeat
Loop
 
Create_Query:
'once tables linked create UNION query
Dim qdf As QueryDef
Dim strSQL As String
 
'initialise count
MyCount = 1
 
'populate array
With dbs
    For Each tdf In .TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 4) <> "~TMP" Then
            TableNameArray(MyCount) = tdf.Name
            MyCount = MyCount + 1
        End If
    Next
End With
 
'delete existing query first
DoCmd.DeleteObject acQuery, "qry_Class_Data_UNION"
 
'Dynamic SQL string depending on contents of array
strSQL = "SELECT " & TableNameArray(1) & ".*"
strSQL = strSQL & " FROM " & TableNameArray(1) & ""
strSQL = strSQL & " WHERE " & TableNameArray(1) & ".[Report Date] >= (forms!Switchboard!txtstartdate) and " & TableNameArray(1) & ".[Report Date] <=(forms!switchboard!txtenddate)"
If TableNameArray(2) <> "" Then
    strSQL = strSQL & " UNION SELECT " & TableNameArray(2) & ".*"
    strSQL = strSQL & " FROM " & TableNameArray(2) & ""
    strSQL = strSQL & " WHERE " & TableNameArray(2) & ".[Report Date] >= (forms!Switchboard!txtstartdate) and " & TableNameArray(2) & ".[Report Date] <=(forms!switchboard!txtenddate)"
    If TableNameArray(3) <> "" Then
        strSQL = strSQL & " UNION SELECT " & TableNameArray(3) & ".*"
        strSQL = strSQL & " FROM " & TableNameArray(3) & ""
        strSQL = strSQL & " WHERE " & TableNameArray(3) & ".[Report Date] >= (forms!Switchboard!txtstartdate) and " & TableNameArray(3) & ".[Report Date] <=(forms!switchboard!txtenddate)"
        Else
            strSQL = strSQL & ";"
    End If
    Else
    strSQL = strSQL & ";"
End If
 
'create query
Set qdf = dbs.CreateQueryDef("qry_Class_Data_UNION", strSQL)
    
Link_Tables_Exit_Click:
    Exit Function
 
Link_Tables_Err_Click:
'table already exists
    If Err.Number = 3012 Then
        Resume Next
        Else: MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbCritical, "Error!"
        Resume Link_Tables_Exit_Click
    End If
    
End Function

i now need to extract the month from the table name (tbl_Class_Data_January_05)
and only link the tables relating to the start and end date input on the switchboard.

using the criteria on the UNION SQL does improve performance..so its looking promising so far.

thanks for the advice..I will post up when i get the chance to finish this.