Scan backups

  • Thread starter Thread starter Prav
  • Start date Start date
P

Prav

Hi, a database is being backed up weekly into B:\PRJDB_Backups e.g
PRJDB20080105.mdb, PRJDB20080112.mdb .....
There is no date showing wwhen a project has been added in the tbl_Prj. I
need to find when a project was added i.e
if not in PRJDB20080105 but in PRJDB20080112 then we will say the project
was added on the 12/01/2008.
How do i scan all backup files (there are other files in that folder) to
find when projects were added? Cheers
 
You can use the Dir() function to loop through all the backup mdbs.
On each iteration of the loop, use TransferDatabase to link to the table.
Then use a DLookup to seach for the project. If you don't find it, Delete
the link and move on to the next mdb. (You don't say how you identify a
project, so I will assume a Long Integer ProjectID field for example
purposes. This function will return the name of the mdb where the project is
found or vbNullstring if it is not found. Help on Dir says the file names
will be returned in no particular order; however, my experience is they are
returned sorted by name. This would make sure you find the earliest date a
project was added.

But, if you are not getting the files in order (which for you would be by
date because of your wise naming), you may need to read the file names into
an array and sort the array before searching.

Disclaimer: This is "air code" written in this editor, so expect syntax
errors. Good testing an debugging is in order.

Private Function FindProject(lngProjectCode) as String
Const conPath As String = "B:\PRJDB_Backups\"
Const conTableName As String = "tbl_Prj"
Dim strDbName As String
Dim varResult As Variant

strDbName = Dir(conPath & "*.mdb")
Do While strDbName <> vbNullString
Docmd.TransferDatabase
Docmd.TransferDatabase acLink, "Microsoft Access", _
conPath & strDbName, acTable,conTableName, conTableName
varResult = DLookup("[ProjectID]", conTableName, "[ProjectID] = " & _
lngProjectCode)
Docmd.DeleteObject, acTable, conTableName
If Not IsNull(varResult) Then
FindProject = strDbName
Exit Do
End If
strDbName = Dir()
End Function
 
Thanks for that Klatuu.
As mentioned there are other databases in that folder (don't ask why :-)).
How can i get only the PRJDB20080105.mdb, PRJDB20080112.mdb and so on and
ignoring the rest?


Klatuu said:
You can use the Dir() function to loop through all the backup mdbs.
On each iteration of the loop, use TransferDatabase to link to the table.
Then use a DLookup to seach for the project. If you don't find it, Delete
the link and move on to the next mdb. (You don't say how you identify a
project, so I will assume a Long Integer ProjectID field for example
purposes. This function will return the name of the mdb where the project is
found or vbNullstring if it is not found. Help on Dir says the file names
will be returned in no particular order; however, my experience is they are
returned sorted by name. This would make sure you find the earliest date a
project was added.

But, if you are not getting the files in order (which for you would be by
date because of your wise naming), you may need to read the file names into
an array and sort the array before searching.

Disclaimer: This is "air code" written in this editor, so expect syntax
errors. Good testing an debugging is in order.

Private Function FindProject(lngProjectCode) as String
Const conPath As String = "B:\PRJDB_Backups\"
Const conTableName As String = "tbl_Prj"
Dim strDbName As String
Dim varResult As Variant

strDbName = Dir(conPath & "*.mdb")
Do While strDbName <> vbNullString
Docmd.TransferDatabase
Docmd.TransferDatabase acLink, "Microsoft Access", _
conPath & strDbName, acTable,conTableName, conTableName
varResult = DLookup("[ProjectID]", conTableName, "[ProjectID] = " & _
lngProjectCode)
Docmd.DeleteObject, acTable, conTableName
If Not IsNull(varResult) Then
FindProject = strDbName
Exit Do
End If
strDbName = Dir()
End Function
--
Dave Hargis, Microsoft Access MVP


Prav said:
Hi, a database is being backed up weekly into B:\PRJDB_Backups e.g
PRJDB20080105.mdb, PRJDB20080112.mdb .....
There is no date showing wwhen a project has been added in the tbl_Prj. I
need to find when a project was added i.e
if not in PRJDB20080105 but in PRJDB20080112 then we will say the project
was added on the 12/01/2008.
How do i scan all backup files (there are other files in that folder) to
find when projects were added? Cheers
Click to expand...
Click to expand...
 
In the first Dir call:
strDbName = Dir(conPath & "*.mdb")

use
strDbName = Dir(conPath & "PRJDB*.mdb")
--
Dave Hargis, Microsoft Access MVP


Prav said:
Thanks for that Klatuu.
As mentioned there are other databases in that folder (don't ask why :-)).
How can i get only the PRJDB20080105.mdb, PRJDB20080112.mdb and so on and
ignoring the rest?


Klatuu said:
You can use the Dir() function to loop through all the backup mdbs.
On each iteration of the loop, use TransferDatabase to link to the table.
Then use a DLookup to seach for the project. If you don't find it, Delete
the link and move on to the next mdb. (You don't say how you identify a
project, so I will assume a Long Integer ProjectID field for example
purposes. This function will return the name of the mdb where the project is
found or vbNullstring if it is not found. Help on Dir says the file names
will be returned in no particular order; however, my experience is they are
returned sorted by name. This would make sure you find the earliest date a
project was added.

But, if you are not getting the files in order (which for you would be by
date because of your wise naming), you may need to read the file names into
an array and sort the array before searching.

Disclaimer: This is "air code" written in this editor, so expect syntax
errors. Good testing an debugging is in order.

Private Function FindProject(lngProjectCode) as String
Const conPath As String = "B:\PRJDB_Backups\"
Const conTableName As String = "tbl_Prj"
Dim strDbName As String
Dim varResult As Variant

strDbName = Dir(conPath & "*.mdb")
Do While strDbName <> vbNullString
Docmd.TransferDatabase
Docmd.TransferDatabase acLink, "Microsoft Access", _
conPath & strDbName, acTable,conTableName, conTableName
varResult = DLookup("[ProjectID]", conTableName, "[ProjectID] = " & _
lngProjectCode)
Docmd.DeleteObject, acTable, conTableName
If Not IsNull(varResult) Then
FindProject = strDbName
Exit Do
End If
strDbName = Dir()
End Function
--
Dave Hargis, Microsoft Access MVP


Prav said:
Hi, a database is being backed up weekly into B:\PRJDB_Backups e.g
PRJDB20080105.mdb, PRJDB20080112.mdb .....
There is no date showing wwhen a project has been added in the tbl_Prj. I
need to find when a project was added i.e
if not in PRJDB20080105 but in PRJDB20080112 then we will say the project
was added on the 12/01/2008.
How do i scan all backup files (there are other files in that folder) to
find when projects were added? Cheers
Click to expand...
Click to expand...
Click to expand...
 
I have another problem is a a file called PRJDB.mdb. How can i ignore that one?

Klatuu said:
In the first Dir call:
strDbName = Dir(conPath & "*.mdb")

use
strDbName = Dir(conPath & "PRJDB*.mdb")
--
Dave Hargis, Microsoft Access MVP


Prav said:
Thanks for that Klatuu.
As mentioned there are other databases in that folder (don't ask why :-)).
How can i get only the PRJDB20080105.mdb, PRJDB20080112.mdb and so on and
ignoring the rest?


Klatuu said:
You can use the Dir() function to loop through all the backup mdbs.
On each iteration of the loop, use TransferDatabase to link to the table.
Then use a DLookup to seach for the project. If you don't find it, Delete
the link and move on to the next mdb. (You don't say how you identify a
project, so I will assume a Long Integer ProjectID field for example
purposes. This function will return the name of the mdb where the project is
found or vbNullstring if it is not found. Help on Dir says the file names
will be returned in no particular order; however, my experience is they are
returned sorted by name. This would make sure you find the earliest date a
project was added.

But, if you are not getting the files in order (which for you would be by
date because of your wise naming), you may need to read the file names into
an array and sort the array before searching.

Disclaimer: This is "air code" written in this editor, so expect syntax
errors. Good testing an debugging is in order.

Private Function FindProject(lngProjectCode) as String
Const conPath As String = "B:\PRJDB_Backups\"
Const conTableName As String = "tbl_Prj"
Dim strDbName As String
Dim varResult As Variant

strDbName = Dir(conPath & "*.mdb")
Do While strDbName <> vbNullString
Docmd.TransferDatabase
Docmd.TransferDatabase acLink, "Microsoft Access", _
conPath & strDbName, acTable,conTableName, conTableName
varResult = DLookup("[ProjectID]", conTableName, "[ProjectID] = " & _
lngProjectCode)
Docmd.DeleteObject, acTable, conTableName
If Not IsNull(varResult) Then
FindProject = strDbName
Exit Do
End If
strDbName = Dir()
End Function
--
Dave Hargis, Microsoft Access MVP


:

Hi, a database is being backed up weekly into B:\PRJDB_Backups e.g
PRJDB20080105.mdb, PRJDB20080112.mdb .....
There is no date showing wwhen a project has been added in the tbl_Prj. I
need to find when a project was added i.e
if not in PRJDB20080105 but in PRJDB20080112 then we will say the project
was added on the 12/01/2008.
How do i scan all backup files (there are other files in that folder) to
find when projects were added? Cheers
Click to expand...
Click to expand...
Click to expand...
 
You will have to explicitly skip it. I am reposting the code. There is an
extra Docmd.TransferDatabase that doesn't belong in the code and I left out
the Loop statement. Here is the changed version to exclude PRJDB.mdb:

Private Function FindProject(lngProjectCode) as String
Const conPath As String = "B:\PRJDB_Backups\"
Const conTableName As String = "tbl_Prj"
Dim strDbName As String
Dim varResult As Variant

strDbName = Dir(conPath & "*.mdb")
Do While strDbName <> vbNullString
If strDbName <> "PRJDB.mdb" Then
Docmd.TransferDatabase acLink, "Microsoft Access", _
conPath & strDbName, acTable,conTableName, conTableName
varResult = DLookup("[ProjectID]", conTableName, "[ProjectID] =
" & _
lngProjectCode)
Docmd.DeleteObject, acTable, conTableName
If Not IsNull(varResult) Then
FindProject = strDbName
Exit Do
End If
strDbName = Dir()
End If
Loop
End Function

--
Dave Hargis, Microsoft Access MVP


Prav said:
I have another problem is a a file called PRJDB.mdb. How can i ignore that one?

Klatuu said:
In the first Dir call:
strDbName = Dir(conPath & "*.mdb")

use
strDbName = Dir(conPath & "PRJDB*.mdb")
--
Dave Hargis, Microsoft Access MVP


Prav said:
Thanks for that Klatuu.
As mentioned there are other databases in that folder (don't ask why :-)).
How can i get only the PRJDB20080105.mdb, PRJDB20080112.mdb and so on and
ignoring the rest?


:

You can use the Dir() function to loop through all the backup mdbs.
On each iteration of the loop, use TransferDatabase to link to the table.
Then use a DLookup to seach for the project. If you don't find it, Delete
the link and move on to the next mdb. (You don't say how you identify a
project, so I will assume a Long Integer ProjectID field for example
purposes. This function will return the name of the mdb where the project is
found or vbNullstring if it is not found. Help on Dir says the file names
will be returned in no particular order; however, my experience is they are
returned sorted by name. This would make sure you find the earliest date a
project was added.

But, if you are not getting the files in order (which for you would be by
date because of your wise naming), you may need to read the file names into
an array and sort the array before searching.

Disclaimer: This is "air code" written in this editor, so expect syntax
errors. Good testing an debugging is in order.

Private Function FindProject(lngProjectCode) as String
Const conPath As String = "B:\PRJDB_Backups\"
Const conTableName As String = "tbl_Prj"
Dim strDbName As String
Dim varResult As Variant

strDbName = Dir(conPath & "*.mdb")
Do While strDbName <> vbNullString
Docmd.TransferDatabase
Docmd.TransferDatabase acLink, "Microsoft Access", _
conPath & strDbName, acTable,conTableName, conTableName
varResult = DLookup("[ProjectID]", conTableName, "[ProjectID] = " & _
lngProjectCode)
Docmd.DeleteObject, acTable, conTableName
If Not IsNull(varResult) Then
FindProject = strDbName
Exit Do
End If
strDbName = Dir()
End Function
--
Dave Hargis, Microsoft Access MVP


:

Hi, a database is being backed up weekly into B:\PRJDB_Backups e.g
PRJDB20080105.mdb, PRJDB20080112.mdb .....
There is no date showing wwhen a project has been added in the tbl_Prj. I
need to find when a project was added i.e
if not in PRJDB20080105 but in PRJDB20080112 then we will say the project
was added on the 12/01/2008.
How do i scan all backup files (there are other files in that folder) to
find when projects were added? Cheers
Click to expand...
Click to expand...
Click to expand...
 
Back
Top