Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2000 Save As

Posted on 2009-05-11
9
Medium Priority
?
337 Views
Last Modified: 2013-11-29
I'm trying to find a way to save the entire database as a new file name an incorporate it into a command button, preferably through a macro, but VBA could work as well.

Basically, the first step in the database form that I have when a user first opens it, is to save the file as a new month.  Any and all help is welcome!

Thanks!
0
Comment
Question by:Golfer219
  • 4
  • 3
  • 2
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 24359363
place this codes in a regular module

Option Compare Database
Option Explicit

Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long

Function CreateCopy(ByVal strOldFile As String, ByVal strNewFile As String) As Boolean

    Dim resp As Long
    On Error Resume Next
   
    resp = CopyFile(strOldFile, strNewFile, True)
    If resp = 0 Then
        MsgBox "File Copy failed."
        CreateCopy = False
    Else
        CreateCopy = True
    End If
End Function

in th load or open event of your statrup form

Private Sub Form_Load()
Dim x
x = CreateCopy(CurrentDb.Name, "C:\MyDB.mdb")
End Sub


0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24360729
Golfer219,

Can you explain why you need to do this?

Have you considered what would happen if the user clicked the button twice?
How would the "Month" named file deal with this?

Can you be specific on what you mean by:
<save the file as a new month>?

JeffCoachman
0
 

Author Comment

by:Golfer219
ID: 24360742
Thanks Capricorn1.

I pasted that into the regular module as well as the Sub Form, however, when it runs it says "File Copy Failed".  Any idea what caused this?

Thanks!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Golfer219
ID: 24360811
Thanks Boag2000.

Basically, I have it set up so that each month the user will open the previous month's database and Save As to create a replica file and maintain the previous month's data.

The database was built to provide analysis & reporting on a monthly data extract.  Each month within the process, the previous month's data is deleted and the new month's data is imported into the table that drives all the queries.

I'm just trying to be able to capture the Save As function within a command button.  Right now, I copy the previous month's file before opening it and paste/rename it in the folder.

Let me know if this isn't clear.  Thanks for your help!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24361514
Golfer219,

But again, what if the user clicks the button twice?
What if they forget to click the button until the middle of the next month?
What if they click the button too early?

This is not the standard way this is done.

1. If you do daily Backups (as you should be doing!) the last backup for the month can serve as the "Monthly Backup".
2. You are backing up the entire database anyway, so April would still contain March, February, January, ...ect, so what is the reasoning for calling this the "Previous Months Data"?
It is a backup of everything up until that point.

A database is an ongoing entity.

All the data should go back are far as you need it to. (or as far back as the 2GB limit will allow)

You can always query for April 2009 using something like this:
Month([OrderDate])=4 And Year([OrderDate])=2009.

(Note: You can even Create a "Make Table" quey of just that months data, instead of storing "Everything":
SELECT tblOrders.OrderID, tblOrders.OrderDate INTO tblApril2009
FROM tblOrders
WHERE (((Month([OrderDate]))=4) AND ((Year([OrderDate]))=2009));
But this can get complicated with a complex database)

There is nothing wrong with making a copy of the data, but what you are asking for is basically a full backup, done every month *Manually*.
This should be happening "Daily" and "Automatically" via your Server and/or Backup software.

Again, just FYI

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24361530
Golfer219,

But again, what if the user clicks the button twice?
What if they forget to click the button until the middle of the next month?
What if they click the button too early?

This is not the standard way this is done.

1. If you do daily Backups (as you should be doing!) the last backup for the month can serve as the "Monthly Backup".
2. You are backing up the entire database anyway, so April would still contain March, February, January, ...ect, so what is the reasoning for calling this the "Previous Months Data"?
It is a backup of everything up until that point.

A database is an ongoing entity.

All the data should go back are far as you need it to. (or as far back as the 2GB limit will allow)

You can always query for April 2009 using something like this:
Month([OrderDate])=4 And Year([OrderDate])=2009.

(Note: You can even Create a "Make Table" quey of just that months data, instead of storing "Everything":
SELECT tblOrders.OrderID, tblOrders.OrderDate INTO tblApril2009
FROM tblOrders
WHERE (((Month([OrderDate]))=4) AND ((Year([OrderDate]))=2009));
But this can get complicated with a complex database)

There is nothing wrong with making a copy of the data, but what you are asking for is basically a full backup, done every month *Manually*.
This should be happening "Daily" and "Automatically" via your Server and/or Backup software.

Again, just FYI

JeffCoachman
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24363495
Golfer219,

check the destination folder for a copy that already exists..
0
 

Author Comment

by:Golfer219
ID: 24364087
Thanks Jeff -

The reason why I want it renamed is that it is only used once a month and the previous months' data is deleted out prior to uploading the current month's file.  Instead of having a large volume of tables and data that increases each month, it utilizes only the newest data available for the previous month.

If they clicked the command button more than once, ideally they should just receive the Save As prompt but it sounds like that's not an option.  I'd love to be able to replicate this process:  1.  A user opens an Excel file for May.  2. They click Save As, and rename the file to June before making any changes.  Now you have a file with May's data and a new file that you can upload the June data for.

By no means am I an Access whiz, but I was just hoping that there was a way to save a new copy of the database before deleting the old records and uploading the new data.

Thanks,
Alan
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24366498
OK,,

If you have a system that you feel is best for your situation, then fine.

My point is that most "Backup" systems do not save data for Specific "Months"

JeffCoachman
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 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