Real Time Data Feed

  • Thread starter Thread starter chris_j_adams
  • Start date Start date
C

chris_j_adams

Hi,

I would like to record data from an external feed as it happens, not
when an OnTimer event fires.

Is this possible in Access? If so, could someone kindly advise on a
good way to achieve this please.

To illustrate, I have an Excel formula that will connect and retrieve
the real time data feed. I have incorporated this formula into a UDF
function in a Form module. I have then tried to show the result of
that function on the Form. The intent is to show the data being
updated on the form (sort of like a clock ticking, except the updates
won't be at regular intervals).

I can achieve this in Excel, but the data builds up and so I would
rather do it in Access, but so far I am not succeeding.

Many thanks in advance for reading this and especially if you can
provide any assistance.

Regards,
Chris Adams
 
Hi,

I would like to record data from an external feed as it happens, not
when an OnTimer event fires.

Is this possible in Access? If so, could someone kindly advise on a
good way to achieve this please.

To illustrate, I have an Excel formula that will connect and retrieve
the real time data feed. I have incorporated this formula into a UDF
function in a Form module. I have then tried to show the result of
that function on the Form. The intent is to show the data being
updated on the form (sort of like a clock ticking, except the updates
won't be at regular intervals).

I can achieve this in Excel, but the data builds up and so I would
rather do it in Access, but so far I am not succeeding.

Many thanks in advance for reading this and especially if you can
provide any assistance.

Regards,
Chris Adams
Click to expand...


Provided your Excel "Form module" is written in VB, can't you just port that
over to a module within Access?
Then two possiblities are:
1) Bind the Form controls to table fields. Set the form controls when new
data comes in, then use Form methods for new record and save record.
2) Leave the Form contols unbound. When new data comes in, then set the Form
control values. Use DAO or ADO to insert a new record into the table.
 
Hi,

I would like to record data from an external feed as it happens, not
when an OnTimer event fires.

Is this possible in Access? If so, could someone kindly advise on a
good way to achieve this please.

To illustrate, I have an Excel formula that will connect and retrieve
the real time data feed. I have incorporated this formula into a UDF
function in a Form module. I have then tried to show the result of
that function on the Form. The intent is to show the data being
updated on the form (sort of like a clock ticking, except the updates
won't be at regular intervals).

I can achieve this in Excel, but the data builds up and so I would
rather do it in Access, but so far I am not succeeding.

Many thanks in advance for reading this and especially if you can
provide any assistance.

Regards,
Chris Adams
Click to expand...

And depending on how your control loop works (or if you have one), you may
have to invoke a DoEvents so the UI can update what the Form shows.
 
Much appreciated for the response - gives me something to think about.

Also, when you mention the control loop, this is a big problem for me.
That is, I'm really not sure how to get the data into the table.

My function Application.Excel.RTD("call data in from external app when
data changes") works ok. I can call it from another sub no problem.

However, I need Access to recognise that the value of the RTD function
has changed, rather than OnTimer checking every second, or having the
external app fire over the data (impossible).

But maybe I have to go to OnTimer. I really don't know Access well
enough to know whether this is the solution.

Many thanks again for your thoughts.
 
Much appreciated for the response - gives me something to think about.

Also, when you mention the control loop, this is a big problem for me.
That is, I'm really not sure how to get the data into the table.

My function Application.Excel.RTD("call data in from external app when
data changes") works ok. I can call it from another sub no problem.

However, I need Access to recognise that the value of the RTD function
has changed, rather than OnTimer checking every second, or having the
external app fire over the data (impossible).

But maybe I have to go to OnTimer. I really don't know Access well
enough to know whether this is the solution.

Many thanks again for your thoughts.
Click to expand...

For monitoring changes there are typically two approaches (from the higher
level API viewpoint).
These are polling, and interrupt or event driven (with clock driven CPUs, at
the lowest hardware level maybe everything is polling)

In polling, you have to check periodically. Using OnTimer (even though it is
a timer event) is actuallly a polling method.
The only way to be truly event driven, is that "the thing that knows when
the data has changed" has to contact or tap into your application.

I do not understand what Excel has to do with the matter, except maybe
that's just what you started out with. Nor do I understand why you have to
mix Excel and Access. It seems you should just do everthing in one or the
other.

Excel is capable of displaying data on the screen. Using the ADO libraries
you can get Excel to insert data into a Jet database table.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/htm
l/acchap2.asp
Technically speaking Access uses the Jet database as its native data storage
engine.

Conversely RTD("call data in from external app when data changes") could be
an Access module.

If you absolutely must stick to the Excel+Access paradigm, there is the
Worksheet_change event within Excel, which will fire whenever a cell
changes. I strongly advise against this.

If you give more (much more) details on what this "external feed" is and
what RTD is, someone else may chime in with good ideas. The important
factors such as what is changing, how it is changing, and who knows it is
changing are missing.
 
Thanks again for the comments, appreciate your input.

Not sure if I'm running on the right road, but I am currently trying an
invisible text box on the form that references the initial text box
(which contains the RTD formula).

Within that I am trying various combinations of AfterUpdate events and
Recalc methods to try to get the RTD formula to continually update,
feed the invisible box, and then fire the event to repeat the process
when it changes again. Or something like that (not sure how the logic
of the loop will pan out).

Yes, correct, I started the project in Excel. I'm reasonably fluent
with that app and had something working there. I just ran up against
the problem that its a spreadsheet, not a database, hence the move to
Access.

Don't know if there's a theory about not much difference between Excel
and Access in practice, but I'm finding in practice there's a world of
difference, so many thanks again for your informed comments and advice
- very helpful and appreciated.

Chris Adams
 
Back
Top