Passing an Updateable ADO Recordset To VBA Subroutine

[jhcoxx]

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?

Thanks for all suggestions and code!

James

 

[Stefan Hoffmann]

hi James,

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

From the OOP point of view, this sounds good. Make it a function, then
the users may use the simple event syntax
"=PublicFunction('theirQueryName")".

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?

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "QueryName", CurrentProject.Connection,..

see OH for the other parameters.


mfG
--> stefan <--

 

[jhcoxx]

hi James,



From the OOP point of view, this sounds good. Make it a function, then
the users may use the simple event syntax
"=PublicFunction('theirQueryName")".


Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "QueryName", CurrentProject.Connection,..

see OH for the other parameters.

mfG
--> stefan <--

Thanks, Stephan

I went with the following, which seems to work well. It always amazes
me how many ways there are to accomplish a single task in ADO....


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.