domain aggregate functions on linked spreadsheets?

  • Thread starter Thread starter james_keegan via AccessMonster.com
  • Start date Start date
J

james_keegan via AccessMonster.com

Hey folks - I'm lost and confused (again!)

I've got a linked spreadsheet as a table.
I've created a recordset reference to that table in my VB code
I'm trying to do a 'dcount' function on that recordset.
But I keep getting a run time error #3078 'ms jet can't find' the named
recordset.

Here's the salient parts of the code in question:
***
Private Sub btnFindRe_Enrollees_Click()
...
Dim tblNewEnrollees As Recordset
Dim tblPrevLines As Recordset
Dim CurCin As String, sqlstring As String
Dim r As Integer, cincount As Integer, reccount As Integer
...
Set tblPrevLines = CurrentDb.OpenRecordset("MMC-FHP-previousline")
Set tblNewEnrollees = CurrentDb.OpenRecordset("MMC-FHP-combined-roster")
...
CurCin = tblNewEnrollees![CIN PC]
reccount = DCount("[CIN PC]", "tblPrevLines", "[CIN PC] =" & CurCin)
...
end sub
***

Is there some other way I can do domain aggregate functions (or sql functions)
against a linked spreadsheet as a recordset in VBA? Or am I screwing up
something obvious?

I found I could do this the 'hard way' by looping through the tblPrevLines
and counting, but isn't there a way to make the domain aggregate functions
work? What I'm really trying to do is to do a make table query against the
PrevLines table selecting only those records where the [CIN PC] field match
the 'CurCin' variable. But I figured if I could get the domain aggregate
functions working, then the SQL functions would work too since they both rely
on Jet.

Any clues would be gratefully appreciated.

Thanks.
jk
 
Hi James,

I think you're muddling tables and recordsets. The "domain aggregate
functions" don't work on recordsets: the "domain" argument must be the
name of a table or a saved query.

If you want to get the number of records in a recordset that match
some criterion, you can set the recordset's Filter property, do a
MoveLast, and get the RecordCount.

Alternatively, use DCount() on the relevant table or query, or do
something like this (air code):

Dim SQL AS String
Dim rsR As DAO.Recordset

SQL = "SELECT COUNT(*) FROM MyTable WHERE blah blah blah"
Set rsR = DBEngine(0)(0).Execute (SQL, dbOpenSnapshot)
rsR.MoveLast
reccount = rsR.RecordCount
rsr.Close


Hey folks - I'm lost and confused (again!)

I've got a linked spreadsheet as a table.
I've created a recordset reference to that table in my VB code
I'm trying to do a 'dcount' function on that recordset.
But I keep getting a run time error #3078 'ms jet can't find' the named
recordset.

Here's the salient parts of the code in question:
***
Private Sub btnFindRe_Enrollees_Click()
..
Dim tblNewEnrollees As Recordset
Dim tblPrevLines As Recordset
Dim CurCin As String, sqlstring As String
Dim r As Integer, cincount As Integer, reccount As Integer
..
Set tblPrevLines = CurrentDb.OpenRecordset("MMC-FHP-previousline")
Set tblNewEnrollees = CurrentDb.OpenRecordset("MMC-FHP-combined-roster")
..
CurCin = tblNewEnrollees![CIN PC]
reccount = DCount("[CIN PC]", "tblPrevLines", "[CIN PC] =" & CurCin)
..
end sub
***

Is there some other way I can do domain aggregate functions (or sql functions)
against a linked spreadsheet as a recordset in VBA? Or am I screwing up
something obvious?

I found I could do this the 'hard way' by looping through the tblPrevLines
and counting, but isn't there a way to make the domain aggregate functions
work? What I'm really trying to do is to do a make table query against the
PrevLines table selecting only those records where the [CIN PC] field match
the 'CurCin' variable. But I figured if I could get the domain aggregate
functions working, then the SQL functions would work too since they both rely
on Jet.

Any clues would be gratefully appreciated.

Thanks.
jk
Click to expand...
 
John said:
Hi James,

I think you're muddling tables and recordsets. The "domain aggregate
functions" don't work on recordsets: the "domain" argument must be the
name of a table or a saved query.
Click to expand...

Thanks John.

And what about linked spreadsheets? Shouldn't I be able to a domain agregate
function against a linked spreadsheet?

What about sql? can the target of the 'from' clause of an SQL statement be a
recordset (or a linked spreadsheet?), or does that have to be a table or a
saved query too?

Also - now I have an additional wrinkle. I imported the spreadsheet that I
have linked so now I have a 'real' local table 'tblPrevlines'. I eliminated
the recordset pointers at this table, and now when I get to my dcount line it
errors out with a 'run time 2001 - you cancelled the previous operation'.

Huh? Talk about your uninformative error messages!

jk
 
Comments inline:
Thanks John.

And what about linked spreadsheets? Shouldn't I be able to a domain agregate
function against a linked spreadsheet?
Click to expand...

Assuming that "a linked spreadsheet" is what I'd call a linked table whose
data source is a spreadsheet, you can use the domain aggregate functions
normally.
What about sql? can the target of the 'from' clause of an SQL statement be a
recordset (or a linked spreadsheet?), or does that have to be a table or a
saved query too?
Click to expand...

The FROM clause needs to refer to tables (including linked tables) or SELECT
queries. It can't refer to a DAO or ADO Recordset object. (Maybe the
confusion is that tables contain sets of records, and SELECT queries define
or return sets of records.
Also - now I have an additional wrinkle. I imported the spreadsheet that I
have linked so now I have a 'real' local table 'tblPrevlines'. I eliminated
the recordset pointers at this table, and now when I get to my dcount line it
errors out with a 'run time 2001 - you cancelled the previous operation'.
Click to expand...

I don't know what you mean by "eliminated the recordset pointers at t his
table". What's your code?
 
The FROM clause needs to refer to tables (including linked tables) or SELECT
queries. It can't refer to a DAO or ADO Recordset object. (Maybe the
confusion is that tables contain sets of records, and SELECT queries define
or return sets of records.
Click to expand...

Yeah, I guess that is the confusion. Not all collections of records are
created equal I guess! Some are more equal than others!

I managed to get my domain aggregate function working on the table that's
linked to a spreadsheet, so that actually makes things simpler than creating
a new recordset object.

I was trying to avoid creating 'real tables' that were only needed during the
execution of the code by using recordsets (which I was thinking of as
'virtual tables') instead, but it looks like that can't happen that way.
(although I suppose I could create the recordsets I need by pointing directly
at the spreadsheets with an ADO connection - but why complicate things
unnecessarily!)

Thanks for your help

jk
 
Back
Top