Solved

Access 2000 Save As

Posted on 2009-05-11
9
324 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 119

Accepted Solution

by:
Rey Obrero earned 500 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 119

Expert Comment

by:Rey Obrero
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 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

9 Experts available now in Live!

Get 1:1 Help Now