Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win



Posted on 2004-08-24
Medium Priority
Last Modified: 2012-05-05
Hi.  I am trying to find a way to Auto Increment or use the Current date when saving or copying tables in an Access Macro.
I have a macro that does the following:

    a.) Destination Database: _____blank________
    b.) New Name: Results Copy
    c.) Source Object Type: Table
    d.) Source Object Name: MEETING RESULTS

    a:) Object Type: Table
    b.) Object Name: MEETING RESULTS

3.) OpenQuery:
    a:) Query Name: LAPTOP DATA QUERY
    B.) View: Datasheet
    c.) Read Only

 *********This query is a Make Table query that creates a table named: Meeting Results.  I want this table saved with a unique name if possible, or renamed prior to the DELETE OBJECT step in the Macro. The Copy Object name "Results Copy" was created so I would not lose the previous results, but I want to retain ALL previous Results... I would love for the file name to be: Meeting Results XXYYZZ, where XX is the Month, YY is the Day, and ZZ is the two digit year.  If this isn't possible, it could simply be Meeting Results01, 02, 03, etc..

Of importance however, is that I run a report based on the "Meeting Results" table generated in the Make Table Query.  The final step of the Macro is to Generate the report, so I need to be sure that the OpenReport statement will point to the most recent Table..

Thanks!!!! The faster the better!!
Question by:InDashMP3
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
  • 2
LVL 12

Expert Comment

ID: 11884267
You probably should rewrite your Macros to VBA.

Public Sub SaveTable()
  Dim TableName As String
  TableName = "Meeting Results " & Now
  DoCmd.TransferDatabase acExport, , , acTable, "Meeting Reports", TableName, False, False
End Sub

This will create a new table as a copy of the table "Meeting Reports" with the current date as the table name.  

I don't believe that I understood your other requirements though. Could you explain a bit more?

LVL 52

Accepted Solution

Gustav Brock earned 2000 total points
ID: 11885397
Try this change:

    b.) New Name: ="Meeting Results " & Format(Date(),"mmddyy")

Your second request cannot be done in a macro.
But you could copy your newly created and dated table to a third table with a fixed name like "Meeting Results View" and base your query on this table. Kind of a "shoemaker solution" as we call it here ... but it will work.

I'll leave it to you to write that macro line ...

LVL 27

Expert Comment

ID: 11885412
Hi ,

Add the following to the Table name and the Report name:
Format(Date(), "MM,DD,YY")

if you create your Table like this:
DoCmd.RunSQL ("CREATE TABLE " & "Meeting Results" & " ([A] Text,[B] Text,[C] Text,[D] Integer);")
change it to
DoCmd.RunSQL ("CREATE TABLE " & "Meeting Results " & Format(Date(), "MM,DD,YY") & " ([A] Text,[B] Text,[C] Text,[D] Integer);")

You will have to explain the Report part "OpenReport statement will point to the most recent Table"

I hope this helps.

LVL 27

Expert Comment

ID: 11885435
Hey gustav

Great minds think alike, but one minute later. LOL


Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

604 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