Solved

Save the data every so often

Posted on 2011-03-22
8
304 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
[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
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

738 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