Solved

Detect new record in multi user database ?

Posted on 2004-04-05
17
360 Views
Last Modified: 2008-02-01
Hi,

I have a MS Access application where 15 users are connected to a shared mdb database on a "server". Everytime a user adds a new record to the database (only 1 table) from a client computer, the info that was added to the table must be printed out as a report.

I do not want to share the printer on the "server computer" and use the shared printer on all 15 computers, I just want to send data from the client to the server computer and print it. Nice, clean and simple. Though I say simple I have no idea how to detect that a new record was added to the table ? I do not want to scan the table with a filter or a query by using a timer in form in order to detect new table entries...

There must be an elegant way to code this ?

An idea anyone ?

Thanks,
0
Comment
Question by:shadowhunter
  • 7
  • 3
  • 3
  • +2
17 Comments
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 50 total points
Comment Utility
Greetings shadowhunter!

  Use the AfterInsert event of the Form the user enters the record on to trigger a call to a function

  if me.NewRec then
    'set up the chain of events leading to the print out.
  endif


I don't quite understand about the printer requiremetns

regards
jack
0
 
LVL 18

Expert Comment

by:Data-Man
Comment Utility
Maybe I'm missing something, but couldn't you just create the report as part of the saving process.  The user's form would still be pointing to the new record; the report could be based on that information.

Mike
0
 

Author Comment

by:shadowhunter
Comment Utility
Hi,

Thanks for the quick answers...

Right now I have set it up as follows:

1. A server with mdb in a shared folder
2. Shared printer on that server
3. 10 user computers connected to the shared folder/database
4. Same 10 computers connected to a shared printer on the sever mentioned in point 2

So, the userform is opened on every of the 10 (must be 15 in the future, damn XP limitations) computers. When the user "saves" his data by hitting a button on a form a report is printed out via the shared printer. Thus every client computer is sending information to the printer qeue on the server.

What I need is that only the data is send to the table on the server, added to the table, detected as new and the report send to the printer (by the sever, not by the user computer)

The point of all this is that I don't need to install a printer on any of the client computers... this is not flexible enough, critcal for changes and too slow...

I think the idea of Jadedata might be a good lead but I still can not figure out how trigger a printjob from a client computer on another computer (server).

All ideas are most welcome !
0
 
LVL 18

Expert Comment

by:Data-Man
Comment Utility
If you aren't worried about timing...you could run/open the database on the server via a scheduled task.  When the database opens, it could automatically run a procedure that would check for all new records...you could add a field Printed (data type Boolean) to keep track of what was printed and what wasn't.  After you print the report, update the value to true so it won't print again.  Run this database as ofter as you need to.  

Thanks,
Mike
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
Unless you are prepared to write com+ components you are stuck with printing from each client because Access is a file server and not a service so nothing actually runs on the server.

Steve
0
 

Author Comment

by:shadowhunter
Comment Utility
@ Mike,

Yes, that was me idea too, however this system will be used to log visitors. The printers are actually badge printers. Each visitor must enter his name and profile on one of the 15 computers.
This must be processed and send to the enterance where his badge must be ready when he or she passes by.

The system needs to be able to log and print badges for 1000 visitors in approx. 30 minutes (there will be a pool of 4 badge printers)

This is why I'm afraid of the timing part and was hoping to write a more professional designed system that what I already have.

Do you think quering the table every 500mS will be a heavy load on the CPU ? There are of course only 1000 entries... but I hope you agree it's sloppy coding :-)

Thanks
0
 

Author Comment

by:shadowhunter
Comment Utility
It would be nice to be able to trigger the system only when new data is added rather then repeatedly requiry the table...
0
 
LVL 18

Assisted Solution

by:Data-Man
Data-Man earned 100 total points
Comment Utility
I do agree that is is sloppy from a server point of view, but if your goal (and your sticking to it) is not to install the printers at the client level, then your not left with too many choices.

You could setup a form with a timer value set to, 20 seconds (20000).  The timer event could then run the code I talked about....printing the new records (all reocrds where the Printed flag was False).

This would be server intensive, but if it is your only option and your looking for something fast...you could have this up in 30 minutes or less.

Thanks,
Mike
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 50 total points
Comment Utility
5 badges printed every 9 seconds .... you have 15 PCs ... each visitor only has 27 seconds to enter their information, you must have a very busy office...

"(by the sever, not by the user computer)" ...
this is only going to cause greater overhead as you will have to be running an instance of Access on the server in order to run a query and print, in fact I think you will find it becomes a bottleneck itself if you get something working with a timer.

"Do you think quering the table every 500mS will be a heavy load on the CPU?"
Yes, and that will slow down all of the requests from the client terminals.

Another bottleneck is that Access can really only handle 1 request at a time, everyone else queues up waiting their turn so while the query is running no other read/write can happen to that database.

I am trying to think of something better but not coming up with much ... perhaps have all requests write to a table with only 1 field that has the number of the record to print and have your seperate process (I would use a seperate PC and not the server) read from that table to see if anything needs to be printed at all and if so print in a batch.

Steve
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
I'm with steve on this.  
In addition the lack of control that a manager has over printing concerns me some.
These report requirements could be queued to a table that a manager or someone could maintain control over instead of hoping that the print out will take place as planned.

Where are these hardcopies going after print and who is tracking to see that all of them printed correctly??
0
 

Author Comment

by:shadowhunter
Comment Utility
Hi,

Thanks all for the replies. This application will not be used in an office but for a private meeting. One time event only... and yes it will be busy so I can afford that something goes wrong :-)

You are confirming what I was afraid of... thanks for that.

So summerized it would be best to have the shared printer installed on every client rather then sending data from client to server and let the server do all the work...

Would there be another way besides Access to accomplish this goal ? I was thinking php, but I have never used it.

All tips are welcome. I will devide the points to all who replied but I really hope a "magical" solution will pop up ;-)

Greetz
0
 
LVL 2

Accepted Solution

by:
joshco earned 300 total points
Comment Utility
Hopefully this is your magical solution:

1.Create a form in the database called PrintMonitor
2.In the events tab for the form,  set the timer interval to be 15 or 30 seconds or something (adjust as needed)
3.Create an event procedure for the ontimer event, or select a macro if you are not a coder which takes care of what you need to do to print the badges.
Leave the form open on the server and it will fire the event every x seconds.

an example of a routine which might print your badges for you would be something like this:
1. Create a module called printingautomation,  it should look something like this:
'*****************************************************
Option Compare Database
Global lastrecordprinted As String

Public Sub printrecords()
Dim rst As dao.Recordset
Set rst = CurrentDb.OpenRecordset("select badgeid from badgeinformationentrytable where badgeid >" & lastrecordprinted)
rst.MoveFirst
If rst.EOF = True Then Exit Sub
Do Until rst.EOF
lastrecordprinted = rst![badgeid]
DoCmd.OpenReport "BADGEREPORT", acViewNormal, , "badgeid=" & rst![badgeid]
'This will filter the report automatically for you and the global will hold the lastbadgeidprinted until the program is closed,
'You will need to come up with a way to set the lastrecordprinted value back to the right place if you have to close and reopen the program
'If this is a one off, just do it from the debug window
rst.MoveNext
Loop
End Sub

*********************************************************************************************************

2. Set up the form_timer event to call it every X seconds

Private Sub Form_Timer()
printrecords
End Sub



Hope this helps
-J
0
 
LVL 2

Expert Comment

by:joshco
Comment Utility
I have re-read the comments above, and would add this (This is a very quick and dirty solution, there are more elegant ways to accomplish this, however I am not going to teach dcom programming this evening):

I intentionally did not mess with writing a printed flag to each record, so that if he wanted to optimize this code, he could do so by creating dbOpenSnapshot (read only snapshot) recordsets and not having to lock the table during the print operation.

If you do not want to have to run hard queries each time,  then simply create a table which has one row and one column, call it printingneeded, call the field needprinting
If you edit my printrecords function to add these two lines:

if dlookup("needprinting","printingneeded") = false then exit sub
and
currentdb.execute("update printingneeded set needprinting = false")

and add the following line to your "Save Badge Info" button
currentdb.execute "update printingneeded set needprinting = true"

This will remove the overhead of continuous hard queries, only hard querying when the flag has been set.


Public Sub printrecords()
Dim rst As dao.Recordset
if dlookup("needprinting","printingneeded") = false then exit sub
currentdb.execute "update printingneeded set needprinting = false"
Set rst = CurrentDb.OpenRecordset("select badgeid from badgeinformationentrytable where badgeid >" & lastrecordprinted)
rst.MoveFirst
If rst.EOF = True Then Exit Sub
Do Until rst.EOF
lastrecordprinted = rst![badgeid]
DoCmd.OpenReport "BADGEREPORT", acViewNormal, , "badgeid=" & rst![badgeid]
'This will filter the report automatically for you and the global will hold the lastbadgeidprinted until the program is closed,
'You will need to come up with a way to set the lastrecordprinted value back to the right place if you have to close and reopen the program
'If this is a one off, just do it from the debug window
rst.MoveNext
Loop
End Sub
0
 

Author Comment

by:shadowhunter
Comment Utility
Thanks Joshco,

I will try it tomorrow at work right away !

Kind regards,

ShAdOwHuNtEr

0
 

Author Comment

by:shadowhunter
Comment Utility
Thanks guys, it aint perfect but I hope it will do the trick !
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
shadowhunter:  
Please tell me you misinterpreted the EE grading standard and didn't mean to issue the lowest possible grade after all this help.
0
 

Author Comment

by:shadowhunter
Comment Utility
Hi,

Good heavens ! Of course not... I'm very, very greatfull to you all... I must have missed something... See, this was the first time I was able to give points, all other questions ever asked be my (Visual Basic related) were never answered and eventualy closed. So I must, in all my hurry, have done something wrong !

To correct this EE (please feel free to explain me what it means) grading: THANKS ALL FOR YOUR SUPPORT !

If a mod can change this, please do so !

Again thank you all,

Kind regards,

ShAdOwHuNtEr


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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

728 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

14 Experts available now in Live!

Get 1:1 Help Now