Solved

Save the data every so often

Posted on 2011-03-22
8
298 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 39

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access printing shortcut in Ms Access 10 35
Between formula (beginning of month) 11 18
Error in SQL Query 36 36
DSum between dates 5 0
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

762 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

20 Experts available now in Live!

Get 1:1 Help Now