Automate monthly data archive 2

[jstricklin]

Hi all,

This is a great forum. I've gotten some excellent tips here. Thank you all.

My question is this.

Each month I download and import data from our online live database into access. Our organization has multiple sites, but the data all come down together with a "site code" that identifies each site's records within each table. In short, I start with one big database with all of the data for every site.

I am looking for a way to automate archiving each site's data into seperate .mdb's from the .mdb that contains all of the data, every month.

The database that contains all of the data has one table that contains the "site codes" for every site.

I am wondering if anyone has an example of code that would Create a new .mdb for every site using the sitecodes in the sitecode table, and then append the data into its respective mdb, then use the site code to name the .mdb. I was also thinking I could have a form where I enter the new path for all of the .mdb's to be archived to each time i run proposed archive program.

I hope this is clear.

Sincere thanks,

jstricklin

 

[Frink]

Hallo,

I've nothing written already but a quick look at the help seems to indicate it's not too tricky.

Use the CreateDatabase method to create your new database, then I'd probably use the TransferDatabase method (with StructureOnly) to copy the table to the new database. Then use TransferDatabase to create a link to your new table, and an append query to copy the required records into it. Use the DeleteObject method to remove the link to the new table when you have finished.

- Frink

 

[jstricklin]

Frink,

Thanks very much for the response. That is almost exactly what I am doing now with queries and macros. The problem is that it must be done once for each site. I'm not a particularly experienced programer, and I was hoping someone had done the same thing programaticaly so that the process could be looped through each site in my "sitecode" table.

Sincere thanks,

jstricklin

 

[EnS]

jsttricklin

I have an automated archive process that runs daily in order to keep my site dbs table records as small as possible. Though, I don't create a db for each of the sites, instead, I pull data from each of the site dbs and archive them into one central archive db. Therefore, I created the archive db with the apportriate tables (one for each site) and loop through the process to extract the needed data. My code is quite lengthly and will probably be hard to read here. copy the contents of the code into a blank module an it should be easier to read. I hope this will point you in the righ direction.

code:

Option Base 1
Option Compare Database
Option Explicit

Dim archiveDbTableType(10) As String, tobeArchivedDbTableType(10) As String, errTag As Boolean, _
errCount As Byte, opCode As Byte

Public Function moveArchiveData()
On Error GoTo Err_moveArchiveData

Dim archiveDbTable As TableDef, archiveDbName As String, tblTypeID As Byte, _
archiveDb As Database, DBFileID As Byte, DBFilePathID As Byte

DoCmd.Hourglass True
'Open error log
Open "c:\temp\archErr.log" For Output As #1
'Open archive db
Set archiveDb = OpenDatabase(DBLocations(4, 0))
'Set path id in relation to file location module
DBFilePathID = 0
'Set data to table type array
setTableTypes
archiveDbName = ("'" & archiveDb.Name & "'&quot
errCount = 0
'Loop through all deposit balancing dbs and archive data
For DBFileID = 5 To 14
Dim archiveDbTableName As String, tobeArchivedDb As Database, _
tobeArchivedDbTable As TableDef, tobeArchivedDbName As String, tobeArchivedDbTableName As String, _
tobeArchivedDbDateFieldName As String, tobeArchivedDbClearedFieldName As String, _
sqlCondition As String, sqlStatement As String

errTag = False
opCode = 0
tblTypeID = DBFileID - 4
Set archiveDbTable = archiveDb.TableDefs("tblReconPstngs_" & archiveDbTableType(tblTypeID))
Set tobeArchivedDb = OpenDatabase(DBLocations(DBFileID, DBFilePathID))
Set tobeArchivedDbTable = tobeArchivedDb.TableDefs(tobeArchivedDbTableType(tblTypeID))

archiveDbTableName = archiveDbTable.Name
tobeArchivedDbName = ("'" & tobeArchivedDb.Name & "'&quot
tobeArchivedDbTableName = ("[" & tobeArchivedDbTable.Name & "]&quot

Select Case DBFileID
'Set Non-Centralized specific table fields
Case 5, 8, 13, 14
tobeArchivedDbDateFieldName = "[Pst Date]"
tobeArchivedDbClearedFieldName = "Cleared"
'Set Centralized specific table fields
Case Else
tobeArchivedDbDateFieldName = "Date"
tobeArchivedDbClearedFieldName = "Status"
End Select
'Set sql specific WHERE statement in relation to specific table fields
sqlCondition = (&quot;WHERE &quot; & tobeArchivedDbDateFieldName & &quot; <= Date() - 95 &quot; _
& &quot;AND &quot; & tobeArchivedDbClearedFieldName & &quot; = Yes&quot

If errTag = False Then
opCode = 1
'Move archivable data from deposit balancing db to archive db
DoCmd.RunSQL (&quot;INSERT INTO &quot; & archiveDbTableName & &quot; IN &quot; & archiveDbName & &quot; &quot; _
& &quot;SELECT * FROM &quot; & tobeArchivedDbTableName & &quot; IN &quot; & tobeArchivedDbName & &quot; &quot; _
& sqlCondition & &quot;;&quot
End If
If errTag = False Then
opCode = 2
'Delete archived data from deposit balancing db
DoCmd.RunSQL (&quot;DELETE * FROM &quot; & tobeArchivedDbTableName & &quot; IN &quot; & tobeArchivedDbName & &quot; &quot; & sqlCondition & &quot;;&quot
End If

'Close deposit balancing db
tobeArchivedDb.Close
'Next deposit balancing db to be archived
Next DBFileID

opCode = 3
'Set archival process completion data
With archiveDb
Dim lastArchive As Recordset
Set archiveDbTable = .TableDefs(&quot;lastArchDate&quot
With archiveDbTable
Set lastArchive = .OpenRecordset(dbOpenTable)
'Write success/fail codes to lastArchived table
With lastArchive
.MoveFirst
.Edit
!lastArchived = DATE
If errCount = 0 Then
!lastStatus = &quot;Successful&quot;
Else
!lastStatus = (&quot;Failed &quot; & errCount)
End If
.Update
.Close
End With
End With
'Close archive db
.Close
End With

Close #1 'Close error log
DoCmd.Hourglass False
Beep
MsgBox &quot;Data archived.&quot;, vbInformation, varStorage.appName

Exit_moveArchiveData:
Exit Function

Err_moveArchiveData:
'Write error to log and continue
errTag = True
errCount = errCount + 1
Write #1, DBFileID; opCode; Err.DESCRIPTION
Resume Next

End Function

Private Sub setTableTypes()

'set archiveDb table name in reference to tobeArchivedDb database
archiveDbTableType(1) = &quot;Dime&quot; 'for use with archiveDb linked to DmRcnV2 table
archiveDbTableType(2) = &quot;ElSeg&quot; 'for use with archiveDb linked to ESGrp1V2 table
archiveDbTableType(3) = &quot;ElSeg&quot; 'for use with archiveDb linked to ESGrp2v2 table
archiveDbTableType(4) = &quot;Ga&quot; 'for use with archiveDb linked to GaRcnV2 table
archiveDbTableType(5) = &quot;Pomp&quot; 'for use with archiveDb linked to PompVer2 table
archiveDbTableType(6) = &quot;Port&quot; 'for use with archiveDb linked to PortVer2 table
archiveDbTableType(7) = &quot;SnLe&quot; 'for use with archiveDb linked to SanLVer2 table
archiveDbTableType(8) = &quot;Taco&quot; 'for use with archiveDb linked to TacoVer2 table
archiveDbTableType(9) = &quot;Tx&quot; 'for use with archiveDb linked to TxRcnV2 table
archiveDbTableType(10) = &quot;Co&quot; 'for use with archiveDb linked to CoRcnV2 table

'set tobeArchivedDb table name in reference to archiveDb database
tobeArchivedDbTableType(1) = &quot;tblReconPstngs&quot; 'for use with linked DmRcnV2 table
tobeArchivedDbTableType(2) = &quot;11720 El Segundo&quot; 'for use with linked ESGrp1V2 table
tobeArchivedDbTableType(3) = &quot;11720 El Segundo&quot; 'for use with linked ESGrp2v2 table
tobeArchivedDbTableType(4) = &quot;tblReconPstngs&quot; 'for use with linked GaRcnV2 table
tobeArchivedDbTableType(5) = &quot;11720 Pompano&quot; 'for use with linked PompVer2 table
tobeArchivedDbTableType(6) = &quot;11720 Portland&quot; 'for use with linked PortVer2 table
tobeArchivedDbTableType(7) = &quot;11720 San Leandro&quot; 'for use with linked SanLVer2 table
tobeArchivedDbTableType(8) = &quot;11720 Tacoma&quot; 'for use with linked TacoVer2 table
tobeArchivedDbTableType(9) = &quot;tblReconPstngs&quot; 'for use with linked TxRcnV2 table
tobeArchivedDbTableType(10) = &quot;tblReconPstngs&quot; 'for use with linked CoRcnV2 table
End Sub


Hope this helps
ERM

 

[jstricklin]

ENS,

This is great. I think this will help with a couple of issue I'm dealing with. Its funny that you posted this, because this is almost the exact answer to the next question I was going to ask the group. Sheesh.


Thanks,

jstricklin

 

[EnS]

COOL! My first STAR! AWSOME!

I glad I was able to help!

COOL!!!! ERM

 

[MichaelRed]

I'm sure that some other procedures exist to support the code posted, however as it stands, I do not believe it will work. Several variables are neither declared or instantiated before their use, the first (and fatal) is DbLocations(4, 0).

A few minor complaints, such as the use of an ordinal value for the (error) file handle, and the failure to access this resource.

I would also, just for readability suggest that the sql strings be broken up a bit and concatenated at run time.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[EnS]

MichealRed,

Thanx for the constructive critisim here. Lord knows everybody needs an outside opinion in order to really be able to evaluate themselves correctly.

You are correct, the code, as it stands, will not work. This is because DBLocations() is a function the I have created to store the path and file names of the different files that I need to access at different times. However, this code was posted as an example. It was meant to display the logic for the question posted. If this code is to be used by somebody, then, of course, they would have to modify some of it in order to get it to work within their environment. ERM

 

[MichaelRed]

I think all would recognize the need for modification, however to use / reference a function without any explination doesn't seem (to me) to meet the needs of other users. Even if the function is quite specific to the app, it's purpose may not be at all clear to the user of the remaining fragments.

Many caveats and snippets are posted along with an expliniation of the missing parts, and are therefore useable by others w/o the need to devine the purpose of referenced -but omitted- code or structures. Here, we are left with the necessity of thoughtful investigation of the missing pieces in order to even evaluate the parts shown.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[EnS]

Point accepted.

Thanx
ERM