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

x
?
Solved

Access 2007 Form Table Update

Posted on 2014-03-20
21
Medium Priority
?
491 Views
Last Modified: 2014-03-21
I am creating a form in my Access 2007 DB that will have a Admin menu. On the admin menu I will have macro buttons that will delete and update tables.

How do I create a way to automatically show on the admin menu when a table was updated.

I would perfer a text beside the button showing the date last updated but if it needs to be in a log somewhere in the DB thats ok to.
0
Comment
Question by:CMILLER
[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
  • 10
  • 9
  • 2
21 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39942971
add a date field to the table, and update it when you press the button you use to update.
see http://office.microsoft.com/en-ca/access-help/store-the-date-and-time-when-a-record-is-modified-HA010241284.aspx
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39943105
Unless you are already storing a date field (Last_Modified is what I generally use to keep track of when records are updated), then adding a date field for every record may be a bit redundant.  

Another method would be to create a table that is used solely for that purpose; for keeping track of when you last clicked on one of those Delete/Update buttons.  Add an update SQL statement that updates the date field in that table for the appropriate table when ever the user clicks on one of those buttons.

Then it would simply be a matter of using a DLOOKUP against that table when the Admin form loads.  You could make the DLOOKUP( ) function the ControlSource for a textbox which is locked or disabled.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39943116
If he is already updating n rows he may as well update the date field at the same time (same SQL)
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:CMILLER
ID: 39943343
by: fyed (Dale Fye)

I like your suggestion but not sure what to type for the update SQL statement?
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 2000 total points
ID: 39943365
edit your existing update query - add
, set timestamp = Now() after the other fields that are updated

assuming 'timestamp' is your new field
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39943385
sorry Dale, maybe I missed a point.
CMiller
Is the intent to keep track of last 'batch' update, or last time each record is updated?
0
 

Author Comment

by:CMILLER
ID: 39943446
I use a macro "TransferSpreadSheet" to update my tables
0
 

Author Comment

by:CMILLER
ID: 39943449
Batch update
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39943471
Then Dale's plan is best: and (if you are not using SQL) extend you macro to update the new field in the new table.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39943559
@coachman99,

You had a valid point, thus my first sentence.  If the table already had a Date/Time column your suggestion would have made lots of sense.

I have several applications where I do bulk updates like this.  I generally store the name of the file uploaded, the date it was uploaded, and have an ID column associated with each upload, all stored in tbl_Uploads.

When I write the data being uploaded to its final destination, I also write the ID value associated with the upload to the table that the data is going in to allow me to quickly reverse an upload if the client realizes there was an error or wants to re-upload the file after making changes.
0
 

Author Comment

by:CMILLER
ID: 39943622
@fyed (Dale Fye)

How do I add it to my import module? I created a new tbl_Update to store the timestamp date.

DoCmd.TransferSpreadsheet acImport, 9, "TableName", strFile, True, "SheetName!"
0
 

Author Comment

by:CMILLER
ID: 39945237
@fyed (Dale Fye)

Is it possible to to add  , set timestamp = Now() into my ipmort module?

Over all still not sure how to add the timestamp into the DB
0
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 2000 total points
ID: 39945568
DoCmd.RunSQL "UPDATE tbl??? SET timestamp  = Now()"

add docmd.setwarnings = false before above line if you don't wish to be prompted

you may also wish to add a 'key' column that stores the button/macro name you ran (if you have multiple) and extend the sql to

DoCmd.RunSQL "UPDATE tbl??? SET timestamp  = Now() WHERE key??? = 'macroname'"
0
 

Author Comment

by:CMILLER
ID: 39945648
I am getting an error at:

DoCmd.RunSQL "UPDATE tbl_Uploads SET timestamp = Now()"
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39945659
what is the error?
try
UPDATE Table1 SET Table1.[timestamp] = Now();
0
 

Author Comment

by:CMILLER
ID: 39945675
I fixed the error but its not updating the timestamp field in the tbl_Uploads table

DoCmd.RunSQL "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = Now()"
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39945683
what is the error?
is timestamp defined as date/time?
0
 

Author Comment

by:CMILLER
ID: 39945700
I dont get an error now when I run it due to changing the syntax to the following.

DoCmd.RunSQL "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = Now()"

I checked and timestamp is defined as date/time, format is short date.

When I run it I get a popup stating that "0" rows will be updated.

**********************************************************************************

I just manually picked a date for the timestamp field, now when I run it, it updates the timestamp field.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39945717
so are we good?
0
 

Author Comment

by:CMILLER
ID: 39945724
I believe so, let me check something first. Thanks!
0
 

Author Comment

by:CMILLER
ID: 39945814
Its all good!

Thanks, guys!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

610 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