Passing an Updateable ADO Recordset To VBA Subroutine

[jhcoxx]

(This post has been upped a level from
microsoft.public.access.modulesdaovba.ado - I'll echo comments made in
one group to the thread in the other)

I'm trying to create a end-user-friendly way to do a certain task. We
need to have a way to pull data out of a table and send it to a data
historian. We will have users who don't know much about VBA
programming, so I'm looking for a solution with a very low skill
level. Ideally this could be placed in a VBA module and spread around
to anyone who needed the capability.

My best thinking to date is to let them first create an named Access
query that works to get the rows and columns of data they want to send
to the data historian. Then, have them call the VBA sub (with the
name of that Access query as a parameter) as the action from a button
click on a form.

The VBA sub will need to open an updatable ADO recordset using the
name of their Access query and then read down the rows of the
recordset, getting the sets of values to write to the data historian
and setting the values of two fields in the recordset row - the
timestamp of the value written and the time at which the write
occurred (in general, not the same times). Typically, the maximum
number of rows for any day will be about 40 so blinding speed isn't a
big issue.

First question - anyone see any better way for this to be done and
still preserving low impact on the end user?

Second, I'm not at all clear about how to open up a read/write ADO
recordset, based on the name of an Access query - anyone have any code
snippet they could attach? Also, is there any reason to expect that
the update operation (after the two timestamp fields have been set on
each record) will involve anything more complex than
a RS.Update command?

Thanks for all suggestions and code!

James

 

[BillCo]

you are right to avoid having a situation where your users need to be
developers. but i think you need to step back and ask yourself exactly
what you are trying to achieve here?

first - what (or who?) is the data historian, and what does he/she/it
do with the data?

second - ADO is a mechanisim for interacting with a data source - it's
not a method for storing/ transporting data. Would a csv, or an xml or
even an excel doc be more appropriate here?

third - don't expect users to write queries. they can't / won't /
shouldn't be expected to be able to write correct queries. Even us
pro's get things wrong from time to time Is there an alternative
method you can program for them, e.g. a form that passes filter
criteria to a set of generic queries?

fourth - why do you need an updateable recordset? it doesn't take much
by way of table joins in a query to make it static. Why not copy the
data to a somewhere for delivery to the user(s) - xml, csv, excel,
temporary table - whatever, and allow edits and then perform updates
based on the result?

maybe if you give a more specific account, step by step of what you
want to do here, i could recommend a better method?

 

[jhcoxx]

Thanks for the comments, Bill.

I see that I do need to elaborate. 'Data historian' is a term I used
because I can't remember what it's really called: a MIS - Management
Information System? Maybe it would work to just think of it as a
place where data generated by a manufacturing process is stored by tag
name (a name for what is being measured) with the value of the
measurement and the time at which the measurement was made. The
values Access needs to write to the data historian are just a small
fraction of the data that goes into it - and are values based on human
actions, as opposed to the measurements of process temperature,
pressure, speed, etc.

The need for an update function is that we will want to record the
time the value is written to the data historian (when the database
user actually initiates the write operation) and the timestamp of the
value (on Monday, the database user might need to enter information
about events that took place during a weekend, so the data's timestamp
might be Saturday at midnight, but it might have been put into the
data historian at 9:30 AM on Monday). We need to capture in Access
those times and it seemed best to write them back to the Access
database to two more columns in the table where the data values are -
keeping all the information in the same record of one table.

The 'user' in my original note is actually someone who would be
creating the Access database application - we have folks that can set
up the data tables, forms and reports and really make them look and
work good (probably not with normalized tables and all that, but these
aren't enterprise-wide applications). They are good at writing
queries, but don't do VBA. They could, however easily be taught to
call a VBA subroutine from an Access macro sheet.

Because each of their applications will be different, I can't write
their queries because I don't know how they have set up the tables
(other than to recommend that they have the two time columns in the
table they are keeping the data to be written to the data historian).
That led me to the idea of just letting them write the query that will
select the information they want written (and the two blank time
columns) and then using that query in my VBA code to generate a read-
write dataset.

My code could then step through the dataset, reading the values and
writing them to the data historian (requiring code that uses the data
historian's API) and putting the timestamps into the dataset rows, one
row at a time - and then finally using the recordset to update the
Access database to get the two time column's information back into
the table.

Did that clarify the overall goal we're trying to achieve?


James

 

[BillCo]

honestly, i think you're making a mistake!!!!
you can't write a generic peice of code to transfer data from multiple
databases you dont know anything about (written by users outside of
your control) to an "MIS" system you don't seem to know much about.
in the words of donnie brasco: forget about it

 

[jhcoxx]

honestly, i think you're making a mistake!!!!
you can't write a generic peice of code to transfer data from multiple
databases you dont know anything about (written by users outside of
your control) to an "MIS" system you don't seem to know much about.
in the words of donnie brasco: forget about it

Gosh, Bill - didn't you even like the gift I gave my Mom for Mother's
Day? : ) : ) : )


Public Sub dev()

Dim rst As ADODB.Recordset
Dim con As ADODB.Connection

Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

With rst
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
End With

con.qry_Not_In_Historian rst

rst.UpdateBatch

Set rst = Nothing
Set con = Nothing

End Sub

Worked fine for the read-write dataset.

Took a bit longer for the data historian write - I decided to use the
SDK instead of the API and that made me dig through the docs a bit,
but I got a proof-of-concept version working this afternoon. It still
needs error handlers, which seem to take me longer than the actual
code, and I'll have to create a text document explaining how to set up
the queries...

Thanks,

James

 

[BillCo]

Gosh, Bill - didn't you even like the gift I gave my Mom for Mother's
Day? : ) : ) : )

hah! no... it was fine, just that a blender is a little
unimaginative

but I got a proof-of-concept version working this afternoon.

cool - i'll leave you to it so... it looks like i'm really not
properly grasping what you are doing here

Best of luck with it