Solved

Access 2000 Save As

Posted on 2009-05-11
9
323 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When I recently replaced my image transfer kit on my office HP color laserjet 5550dn printer, I had a slight problem.  The left bracket that holds the transfer kit got stuck in the upright locked position instead of being at a 45 degree angle facing…
Printers have changed substantially in the last 30 or so years, not just in technical capabilities but in cost and usage as well.  Printers were originally used for interfacing with the operator, not necessarily for printing copy or pictures. In …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

708 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

17 Experts available now in Live!

Get 1:1 Help Now