Solved

Save the data every so often

Posted on 2011-03-22
8
302 Views
Last Modified: 2012-05-11
I have a database I would like to update the data from every 10 records, or maybe every 5 minutes.  I only need to 'save' one table.

The bad news is that during this 'save' or 'export' process, I don't want to interrupt the form that is open.  

So, in short, I open a form on the database and people scan their ID cards to clock in.  Every 10th clockin, I want to export the table "MASTER CLOCK IN", but while people are still clocking in.  There might be 200 in line to clock in.

Can I do this without a "Please wait" message popping up?
0
Comment
Question by:TIgerV
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 84
ID: 35195391
Where are you going to save the data? You can use TransferText to export the data in a Text format, and it won't interrupt the process.

What's the purpose of doing this, by the way? There might be other methods you can use.
0
 

Author Comment

by:TIgerV
ID: 35195399
I am using this database for a rather important process.  If, for some reason (power failure, loss of LAN connection, etc) the system would corrupt, I need to go back to get as much data as possible.  We're messing with people's pay here.

I have another database I use daily and about once a month I have to rebuild a day's worth of data because someone closed it wrong or some other stupid reason- can't have that with this database.  I need to make sure I don't lose more than 5 mins of data no matter what.

TV
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35195601
"for some reason (power failure, loss of LAN connection, etc) the system would corrupt, I need to go back to get as much data as possible. "
"We're messing with people's pay here."
"someone closed it wrong or some other stupid reason- can't have that with this database."
 "I need to make sure I don't lose more than 5 mins of data no matter what."

Then you might want to also consider running this on a RAID, (http://en.wikipedia.org/wiki/RAID) where this type of redundancy is built in.
Then essentially you don't have to do much of anything in Access.

Also, depending on your needs, you might want to look into using SQL Server Express for the Back end, as it has backup utilities and Rollbacks, ...etc)


JeffCoachman
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:TIgerV
ID: 35195613
That's not an option, I have a standalone machine most of the time, and not a lot of flexability.  I just need the solution originally requested.  I am dealing with Company machines that come with a pre-loaded image.  :-(

If I set an "ON TIMER" event, can I run a macro to export the data to a text file while still accepting scans?, or should I throw up a "PLEASE WAIT" box for 3 seconds and run it, then close and reopen the form?

TONY
0
 
LVL 40

Expert Comment

by:als315
ID: 35196242
May be you can use other Access db, which will import data from your tables every 5 minutes? It will not interrupt first DB.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35196945
You certainly shouldn't use a Timer. This will cause more disruption than anything else.

You could use the Form's BeforeUpdate event to fire off a function that will store the data in a separate location. You can use straight SQL to do this:

DoCmd.RunSQL "INSERT INTO YourRemoteTable(Col1, Col2, Col3) IN C:\Folder\Database.mdb VALUES(" & Me.Field1 & "," & Me.Field2 & "," & Me.Field3 & ")"

Note that you must enclose Text values in single quotes, and Date values in Hash marks. So if Col1 is Text and Col3 is a date:

DoCmd.RunSQL "INSERT INTO YourRemoteTable(Col1, Col2, Col3) IN 'C:\Folder\Database.mdb' VALUES('" & Me.Field1 & "'," & Me.Field2 & ",#" & Me.Field3 & "#)"

In the few seconds it takes the next person to queue up and enter the information, this statement should run and you should be okay.

With that said: If this is truly mission critical, and you're concerned with losing data due to network interruptions or other issues not strictly managed by Access, then you would be well advised to follow Jeff's advice - or at the very least inform your supervisors of the dangers of managing this critical data in an environment which is known to cause corruption (you mentioned that you have another database which you must rebuild). Jet works very well in most cases, but it's nowhere near as fault tolerant as SQL Server Express (which is free and very easy to setup and manage).



0
 
LVL 18

Expert Comment

by:p912s
ID: 35197710
>>scan their ID cards to clock in

How many people clock-in in a minute? You stated you want to save after every 10th or every 5 minutes. Surely in 5 minutes you would have more than 10 people clock-in with 200 people in line.


>>I have another database I use daily and about once a month I have to rebuild a day's worth of data because someone closed it wrong or some other stupid reason- can't have that with this database.  I need to make sure I don't lose more than 5 mins of data no matter what.

These databases - both the time clock and the one mentioned above with corruption. Are they a single database each that contain both tables and forms? If so they should be split in to a front-end (forms, queries, reports) and a back-end (tables).

As for auto-saving data, I would on each save send the record to the remote database and (due to lan issue) export the record to a text file on the local machine. Both should be able to be accomplished without any interruption to your user.

Write the record to a text file. All you need to do is list the fields you want written separated by commas.
    Dim FF
    FF = FreeFile
    Open "C:\SaveMyData.csv" For Append As #FF
    Write #FF, Me.fld1, Me.fld2, Me.fld3
    Close FF
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35203559
In other words, you are demanding Mercedes Benz performance from a Chevy...
;-)

There is nothing wrong with a Chevy, ...just keep in mind that it is designed for a totally different market than a Mercedes Benz.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

679 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