Solved

Access 2000 Save As

Posted on 2009-05-11
9
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

622 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