• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Save the data every so often

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
TIgerV
Asked:
TIgerV
  • 2
  • 2
  • 2
  • +2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
TIgerVAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
"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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
TIgerVAuthor Commented:
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
 
als315Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
p912sCommented:
>>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
 
Jeffrey CoachmanMIS LiasonCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now