Solved

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

Posted on 2010-09-10
12
465 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:ca1358
  • 7
  • 5
12 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 33649042
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
 

Author Comment

by:ca1358
ID: 33649086
I need to keep the table name without the date.

Thank you.
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 33649138
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 14

Expert Comment

by:pteranodon72
ID: 33649154
Sorry, meant in the last line:
TableName yyyymmdd_hhnn ?
0
 

Author Comment

by:ca1358
ID: 33649839
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
 

Author Comment

by:ca1358
ID: 33649925
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
 
LVL 14

Expert Comment

by:pteranodon72
ID: 33650085
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
 
LVL 14

Expert Comment

by:pteranodon72
ID: 33650402
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
 

Author Comment

by:ca1358
ID: 33650678
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
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
ID: 33650778
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
 

Author Closing Comment

by:ca1358
ID: 33650789
Thank you for all your work!!!
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 33650819
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
type of query 11 40
Top 1 of each supplier 55 54
Publisher:   Unknown     VB.exe Application 1 17
Turn off MS Access Default=0 for Numerics 6 23
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now