Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2000 Save As

Posted on 2009-05-11
9
Medium Priority
?
333 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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

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
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

722 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