| Author |
Topic |
|
Phil D.
Starting Member
5 Posts |
 Posted - 2004-03-11 : 13:15:15
|
Greeting all. Nice forum here.First, the little scene on the app I've been asked to develop.I am in a manufacturing environment. When a mechanic finishes a job, he walks over to a "call" board. It's a clipboard that the quality inspectors periodically check. Manufacturing writes down his name, the current time, and the reason for the call. Hopefully, a Quality inspector will notice this. The Quality Inspector then walks over to the callboard, writes his name down, along with the time. He inspects the work as requested by manufacturing. If it's good, he signs approval. Otherwise he rejects.We've got thousands of these transactions happenning on paper every day, and for analysis, it's useless.SO, I'm trying to design an electronic version.I have SQL server 2000 available to me.The piece I'm stumped on is a "Upgrade" to the old method. I want to show the "callboard" for any given area on multiple PCs. So, if a manufacturing person logs up a call on one pc, it shows on all of them. Same for the inspector; if he answers a call, I want it to show up on all of them. Now I hope to be using VB for my app development, but I will likely be forced to use VBA. With this in mind, after I upload a new record to the server, how do I get the server to notify the workstations (that have the callboard program displaying information) that they need to refresh?My first solution is port communications over TCP/IP, Like a ping. But I REALLY don't want to do it that way. It would involve API calls to get the current IP, opening ports, etc. I'm hoping there is a native "replication sync" method for SQL server and maby some associated VBA plugins that keep the local and server tables mirrored.Alternatively, I could use a timed based update, but that would generate a lot of un-necessary network traffic, and the network people would probably not be happy about it.So, I'm hoping that someone here can give a suggestion or two, and maby point me to some resources.Thanks all Phil D. |
|
|
label
Posting Yak Master
197 Posts |
Posted - 2004-03-11 : 13:31:51
|
| This should be very simple. Just develop an intranet based web application. You can use ASP or ASP.Net to write the front end presentation layer and SQL as your backend. That way you have a single database to manage as well as one piece of Source code stored on the server so any modifications are simpy done once on the Webserver. |
 |
|
|
MuadDBA
628 Posts |
Posted - 2004-03-11 : 13:46:08
|
| How does that handle the automatic refreshing? |
|
|
|
label
Posting Yak Master
197 Posts |
Posted - 2004-03-11 : 14:09:57
|
quote:
Originally posted by crazyjoe
How does that handle the automatic refreshing?
Well, if the pages are going to be up all the time, simply put an http metatag in that refreshes the page at whatever intereval you deem neccessary. (i.e. every 30 seconds or something) |
|
|
|
Phil D.
Starting Member
5 Posts |
Posted - 2004-03-11 : 14:25:53
|
quote:
Well, if the pages are going to be up all the time, simply put an http metatag in that refreshes the page at whatever intereval you deem neccessary. (i.e. every 30 seconds or something)
I can to a time based update with anything. App or WebPage. I don't want timed traffic on the network though.What I'm really looking for is a way to update the datasets on a set of client machines (that may vary in number and location) when an event (update) occurs on the server. (I am limited to VBA).I'm not having much luck looking through API documentation thusfar...I just now came across pipes, but I'm not sure they would serve me any better than sockets.I really am hoping for a plugin (like activeX) that is ready to go, and links to the SQL Server to look for events.No such luck so far...Please keep the suggestions coming!  |
|
|
|
label
Posting Yak Master
197 Posts |
Posted - 2004-03-11 : 14:32:41
|
quote:
Originally posted by Phil D.I can to a time based update with anything. App or WebPage. I don't want timed traffic on the network though.
Ok, if you want to avoid timed traffic, write a little console application that sits on your SQL server determine when an update occurs. When an update occurs it sets a flag on the server (or client machines) that lets it know that it needs to refresh it's data. |
|
|
|
Phil D.
Starting Member
5 Posts |
Posted - 2004-03-11 : 14:44:24
|
quote:
Ok, if you want to avoid timed traffic, write a little console application that sits on your SQL server determine when an update occurs. When an update occurs it sets a flag on the server (or client machines) that lets it know that it needs to refresh it's data.
I am confined to SQL space on the server. I can not use any custom console apps. I have to use SQL Functions within SQL server, or any API that I can reference internally. I have to work in a secured space within the server where SQL is running.I am confined to VBA on the workstations. Must be a standalone Excel or Access program/file. Since I'm confined to VBA, I need a active indication (as an event on the client machine) that an update occured on the server, Using only SQL functions and VBA functions. I can reference standard APIs if necessary.On the server side, capturing an event is easy. It's sending this notice to the clients, and actually interpreting this notice as an event that I'm tied up on.Unless, as I mentioned before, there is another way to do this. |
|
|
|
label
Posting Yak Master
197 Posts |
Posted - 2004-03-11 : 14:57:52
|
quote:
Originally posted by Phil D.I am confined to SQL space on the server. I can not use any custom console apps. I have to use SQL Functions within SQL server, or any API that I can reference internally. I have to work in a secured space within the server where SQL is running.
Wow. Sounds like they've really tied both your hands behind your back and told you to paint a picture using only your mouth.  Well, I've really never used VBA as all the programming I've done have been actual VB/VB.NET Windows/Console applications or ASP/ASP.NET web based applications. I'm sorry, I really wish I could help but I just don't know enough about VBA to know how to help you out. They seem to have taken a pretty straight-forward function and made it as complex as they possibly could with their restrictions. |
|
|
|
Phil D.
Starting Member
5 Posts |
Posted - 2004-03-11 : 15:25:01
|
| Yes, everything is WAY to hard around here. But it's that or nothing.I just had another idea, I might be able to do mirroring to MSDE instances on the client machines...Still hoping for something better though. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-03-11 : 17:03:14
|
| Phil D. consider a SMB server messaging block Protcol messenger. There are some pretty basic ones available. That may allow you to present a prompt at the client gui's like "hey something is going on". I tapped into a tiny little program called LANTALK to send lanmessages to the shop floor from a Cad data submission and transfer queue which has some of the same elements you describe, signoffs, notify the transfer employee etc.The shop floor guys get this little app that whistles at them. "Hey; files a comin' to your work cell" with a direct link to the web item of concern. It's not exactly what you spec'ed out but the mechanism may be there. Make electronic submission -> broadcast to select clients this has happened. I was amazed that in this day and age that some were not aware of the need to refresh a web page, but they learned quick.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-03-12 : 11:20:09
|
| Thousands of transactions a day....is not a lot of traffic.It might be a lot simpler to 'amend the requirements'...ie the 'minimise network traffic' oneand deliver something that works, than persist to satisfy an aim that is unwise, impractical and maybe unworkable.take ... Clienta, Clientb, Clientc, Inspectord...Clienta,b,c raise call-tickets all during the day. Inspectord periodically reviews all call-tickets...and for instance approves/rejects 2 for clienta.I take it that only Clienta needs to be aware of this....and that it shouldn't be broadcast to clientb,c as well.One solution is to have Clienta be notified of 'something' that affects him...the next time he (and only he) goes to post a call-ticket...ie the app should 'check for relevent incoming actions for clienta' before posting a new action for clienta.I suspect you don't want to notify all Client(s) of Inspectord's action?...otherwise your network traffic problem will scale up, rather than down.I presume it's also important, that the notification of inspectord's actions get a guaranteed delivery to each client...the best (only) way to do that is to have the action recorded in the database and to have the client read the db before posting a new action, if there's nothing new there, the posting proceeds without interruption.....the traffic involved should be very little.Whether you use VB or VBA shouldn't affect this... |
|
|
|
Phil D.
Starting Member
5 Posts |
Posted - 2004-03-12 : 13:22:49
|
| I think you're right.Working is more important than 100% efficiency.I imagine if I set up a flag check, that woudl be much faster than actually checking the "new calls" table.Doing that periodically will keep the "hands off" refresh traffic acceptable (I hope)and doing a manual update whenever someone touches the machine is a no-brainer.I may be making this harder than it has to be.This sounds good.Thanks! |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-03-12 : 14:08:07
|
| In MS Access I put a Refresh Timer on the form. but I include a reset timer sub every time there is activity on the form so the refresh does not mess up entrys.JimUsers <> Logic |
|
|
|
|
New to SQL App Development; Need Suggestions