MS Access backup

[Sivamaddy]

Hi All,

I having problem in maintaining the data in the single database. Since current size is 330MB.
So I am trying to move the old dated data to another db and it may present in the same path under one folder.

Whenever I am trying to pull the report from old dated, I need to get the data from that db.

How can we do this simple and efficient.

thanks

 

[Smallman]

Hi Sivamaddy

Welcome to the Chandoo Forum

What format are you trying to pull the data to. Are you querying this data from Excel or are you in one database trying to access old data.

If you are looking for a snap shot of data you could set up a parameter query in your database and call the parameters from Excel. If the old Parameters, less than a certain date the data will pull from one database, else the data will pull from the newer database.

This is quite a detailed process to set up before it becomes simple. I have some examples at work which I could dig up but the size of the Database would make it difficult to post.

Just my thoughts on your problem.


Smallmanb

 

[Sivamaddy]

Thanks.

Thanks for your valuable comments.

Yes, You are correct. I am just pulling the data from both the DB.

Whenever I pull the report my query will get the data from Current db and as well as Backup db. But I have maintain the master table only in Current DB. Because of this, First I am pulling the data from backup db to current db in one temp table with selected data and put the join with actual table in the current db.

I am not sure, Is there any other simplest way. If possible please provide the sample code with some dummy samples(pls attach).

 

[Smallman]

Hi Sivamaddy

I will get you a sample tomorrow Aussie time. If no one provides you with a workable solution between now and then I put the file on drop box maybe this time tomorrow and you can pick the sample up then. I will check on this thread in the morning.

Just so I provide the right data do you want to see a parameter query where you put the criteria (dates) into an excel spreadsheet and pull the data into that sheet from an access database. Is that the case?

Take care

Smallman

 

[Sivamaddy]

Yes dude.

Thanks

Hope my problem will get rid off me tomorrow.

 

[Smallman]

No worries

You will have many more questions tomorrow. Parameter queries take a bit of setting up but are good once you have them working for you.

Take care

Smallman

 

[Smallman]

Hi Sivamaddy

I have located the file. I will need you to PM me your email address as I will drop this on my drop box and I have some sensitive data in the file. The following is the coding for a parameter query. You will notice about 2 thirds of the way down the the MyQueryDef Calls for a Parameter. I will show you how to set this up. You will also have to set up a connection to the DAO x.x Object Library.

The way I see your procedure working.

Set both files up with a parameter query on the date.
Add an if statement which says if DATE is less than a date of your choice then Path = A Else Path = B
Import the data.
Consolidate both data sets.

Option Explicit
 
Sub TimeActiv()
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim sh As Worksheet
Dim i As Integer
Dim lw As Integer
Dim fname As String
Dim Hrstr As String
 
Hrstr = Range("FileLoc").Value '
fname = Range("HRFileName").Value
 
Set MyDatabase = DBEngine.OpenDatabase(Hrstr & fname)
Set MyQueryDef = MyDatabase.QueryDefs("qryTimeActivityFinal")
Set sh = Sheets("IW47")
 
If sh.FilterMode = True Then
  sh.Range("A1").AutoFilter
End If
 
sh.Range("A2:M10000").ClearContents
'Speed up calcs
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
 
    With MyQueryDef
        .Parameters("[Lvl 3]") = Range("Level3").Value
            End With
    'Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset
    sh.Range("A2").CopyFromRecordset MyRecordset
'Add column heading names to the spreadsheet
  For i = 1 To MyRecordset.Fields.Count
    sh.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
  Next i
 
'Put defaults back
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
 
End Sub

That should do it.

Take care

Smallman

 

[dan_l]

could also be done with query tool?