Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Save the data every so often

Posted on 2011-03-22
8
Medium Priority
?
308 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 85
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

670 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