How to Docmd.CopyObject-- Pick last table with last date and time

The first piece of code makes backup tables into a backup database. This adds the current date and time to the table names.

In the morning databases are copied.  

Then second piece of code restores tables into a copy of the morning database after it has crashed with some our backup tables.  My problem is I am trying automate the process.  If the table has no date and time added no problem restoring.  My problem is that the tables with time and date.  

Is there a way to say give that last table with date and time?




Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 300000
End Sub
Private Sub Form_Timer()
DoCmd.RunMacro "macro1"
Me.Requery
Me.TimeRecord.Requery
Forms!form2!Form3.Requery
Dim XXXXXXXXX As String
Dim sNow As Variant
     sNow = Format(Date, " mmddyy") & "_" & Format(Time, "hhmm")
Dim strDestinationMDB As String
Dim dbsData As DAO.Database
strDestinationMDB = "\\XXXXXXXX\XXXXXx\XXXXXXX.mdb"
Set dbsData = DBEngine.OpenDatabase(strDestinationMDB, _
False, False, ";pwd=XXXXXXX")
    
    DoCmd.SetWarnings False

  DoCmd.CopyObject "\\XXXXXXXX\XXXXXx\XXXXXXX\XXXXXXXX", "Table1" & sNow, acTable, "Table2"

End Sub
=============================================================================================
Option Compare Database
Option Explicit
Private Sub cmdSendTables_Click()
SendBackupTables
End Sub

Function SendBackupTables()

Dim strDestinationMDB As String
Dim dbsData As DAO.Database

Str DestinationMDB = "\\XXXXXXXXX\XXXXXXXX\XXXXXXXX\XXXXXXX\XXXXXXXXXX\ExportDB.mdb"
Set dbsData = DBEngine.OpenDatabase(strDestinationMDB, _
False, False, ";pwd=XXXXXXXX")

    DoCmd.SetWarnings False

DoCmd.CopyObject "\\XXXXXXXXX\XXXXXXXX\XXXXXXXX\XXXXXXX\XXXXXXXXXX\ExportDB.mdb", Table2", acTable, " Table2 090810_0808"

MsgBox "Tables have been Exported", vbExclamation + vbOKOnly, "Tables Exported"

End Function

Open in new window

ca1358Asked:
Who is Participating?
 
pteranodon72Connect With a Mentor Commented:
ca1358,
That looks good except for a single space on line 18 between the first apostrophe and YourTableNameWithoutDate. Remove that sucker and you are good to go!

pT72
0
 
pteranodon72Commented:
ca1358,

The problem is that your current naming convention does not sort in date order across years.  If you change the way you create sNow to:

sNow = Format(Date, " yyyymmdd") & "_" & Format(Time, "hhnn")

the versions will sort alphabetically in chronological order through time. Then you can use

sPrefix = "YourTableBaseName"
sCrit = "Name Like '" & sPrefix & " ########_####' AND Type=1"
sLatestTable = DMax("Name", "MSysObjects", stCrit)

to get the latest saved table name.
Write back if you need to keep the current table names.

HTH,

pT72
0
 
ca1358Author Commented:
I need to keep the table name without the date.

Thank you.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pteranodon72Commented:
I'm sorry, I know you want the tables in the morning database to have no date in their names, but I meant to say, are you committed to having the backup versions in:

TableName mmddyy_hhnn

or can you change the standard to

TableName mmddyyyy_hhnn?

0
 
pteranodon72Commented:
Sorry, meant in the last line:
TableName yyyymmdd_hhnn ?
0
 
ca1358Author Commented:
I have not  got this to work.

What is the syntax of CopyObject statement?


DoCmd.CopyObject "\\XXXXXXXXX\XXXXXXXX\XXXXXXXX\XXXXXXX\XXXXXXXXXX\ExportDB.mdb", Table2", acTable, " Table2 090810_0808"

0
 
ca1358Author Commented:
Here is what I have

Option Compare Database
Option Explicit

Private Sub Command0_Click()
SendBackupTables
End Sub

Function SendBackupTables()

Dim strDestinationMDB As String
Dim dbsData As DAO.Database
Dim sPrefix
Dim sCrit
Dim sLatestTable

strDestinationMDB = "\\XXXXXXXXXXX\XXXXXX\XXXXX\ExportDB.mdb"
Set dbsData = DBEngine.OpenDatabase(strDestinationMDB, _
False, False, ";pwd=bigblue")

 DoCmd.SetWarnings False

sPrefix = "Table1"
sCrit = "Name Like '" & sPrefix & " ########_####' AND Type=1"
sLatestTable = DMax("Name", "MSysObjects", sCrit)


DoCmd.CopyObject "\\XXXXXXXXXXX\XXXXXX\XXXXX\ExportDB.mdb", "Table1", acTable, "sLatestTable"

MsgBox "Tables have been Exported", vbExclamation + vbOKOnly, "Tables Exported"

End Function

Open in new window

0
 
pteranodon72Commented:
Keeping your current table-naming system, you can use a query to create a date/time field that sorts the like-named tables.

Write a query named qryLatestTable with the SQL:

SELECT TOP 1 MSysObjects.Name, DateSerial(Mid([Name],Len([Name])-6,2), Mid([Name], Len([Name])-10,2), Mid([Name], Len([Name])-8,2)) + TimeSerial(Mid([Name], Len([Name])-3,2), Mid([Name], Len([Name])-1,2),0) AS TableDate
FROM MSysObjects
WHERE (((MSysObjects.Name) Like 'Table2 ######_####') AND ((MSysObjects.Type)=1))
ORDER BY DateSerial(Mid([Name],Len([Name])-6,2),Mid([Name],Len([Name])-10,2),Mid([Name],Len([Name])-8,2))+TimeSerial(Mid([Name],Len([Name])-3,2),Mid([Name],Len([Name])-1,2),0) DESC;

Then, replace line 43 in your code above with:

DoCmd.CopyObject "\\XX\X\X\X\X\ExportDB.mdb", "Table2", acTable, DLookup("Name", "qryLatestTable")


If you change the format of sNow to yyyymmdd_hhnn, you can skip the query and use

DoCmd.CopyObject "\\XX\X\X\X\X\ExportDB.mdb", "Table2", acTable, DMax("[Name]", "MSysObjects", "Type=1 AND [Name] Like 'Table2 ########_####')

HTH,

pT72
0
 
pteranodon72Commented:
Remove the quotes around sLatestTable in ID:33649925, line 23.

The syntax is:
DoCmd.CopyObject DestinationDatabase, NewName, SourceObjectType, SourceObjectName

Can you go through the code step-by-step and make sure the Dlookup is finding the table?

You can also try :

Docmd.TransferDatabase acExport, "Microsoft Access", DatabaseName, acTable, Source, Destination

as shown on
http://accessblog.net/2006/04/how-to-export-objects-to-secured.html
0
 
ca1358Author Commented:
This seams to work.

I changed the formatting to copy over to this.

TableName yyyymmdd_hhnn

Please look at it and tell me if you think it looks ok.
Option Compare Database
Option Explicit
Private Sub cmdSendTables_Click()
SendBackupTables
End Sub

Function SendBackupTables()

Dim strDestinationMDB As String
Dim dbsData As DAO.Database

strDestinationMDB = "\\XXXXXXX\XXXXXXXXXX\XXXX\XXXXXX\XXXXX\ExportDB.mdb"
Set dbsData = DBEngine.OpenDatabase(strDestinationMDB, _
False, False, ";pwd=XXXXXX")

    DoCmd.SetWarnings False

DoCmd.CopyObject "\\XXXXXXX\XXXXXXXXXX\XXXX\XXXXXX\XXXXX\ExportDB.mdb", "YourTableNameWithoutDate", acTable, DMax("[Name]", "MSysObjects", "Type=1 AND [Name] Like ' YourTableNameWithoutDate ########_####'")

MsgBox "Tables have been Exported", vbExclamation + vbOKOnly, "Tables Exported"

End Function

Open in new window

0
 
ca1358Author Commented:
Thank you for all your work!!!
0
 
pteranodon72Commented:
Oh, and add these lines after the MsgBox:

dbsData.Close
Set dbsData = Nothing

to close and de-reference the database you previously opened with a password. The code will run without it, but it's better practice to close anything you opened and Set any objects to Nothing.

pT72

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.