ADODB and Access Reports

[Tim Marshall]

I am trying to set a report's recordset property with an ADO recordset.
I do the following and it works on a form. What do I need to do to do
this correctly on a report?

Private Sub Report_Open(Cancel As Integer)

Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim strS As String

Set c = New ADODB.Connection

Set r = New ADODB.Recordset

strS = <a valid SQL statement>

With c

.CursorLocation = adUseClient

.ConnectionString = cTmarConnect 'constant for a connect string

.Open

End With

With r

.ActiveConnection = c

.CursorType = adOpenDynamic

.LockType = adLockReadOnly

.Open strS

End With

Set Me.Recordset = r

End Sub

This works fine on a form - take the exact same code and place in the on
open event of a form and I get the results I expect. In a report, I get
an error message that tells me

Run time error '2593'

This feature is not available in an mdb.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David Lloyd]

Tim:

The following KB article and the associated white paper addresses the issue
you are seeing. The bottom line is that you can only bind a recordset to a
report in an Access project (.adp). It will not work in a .mdb file.

http://support.microsoft.com/default...b;en-us;301987

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:db**********@coranto.ucs.mun.ca...
I am trying to set a report's recordset property with an ADO recordset.
I do the following and it works on a form. What do I need to do to do
this correctly on a report?

Private Sub Report_Open(Cancel As Integer)

Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim strS As String

Set c = New ADODB.Connection

Set r = New ADODB.Recordset

strS = <a valid SQL statement>

With c

.CursorLocation = adUseClient

.ConnectionString = cTmarConnect 'constant for a connect string

.Open

End With

With r

.ActiveConnection = c

.CursorType = adOpenDynamic

.LockType = adLockReadOnly

.Open strS

End With

Set Me.Recordset = r

End Sub

This works fine on a form - take the exact same code and place in the on
open event of a form and I get the results I expect. In a report, I get
an error message that tells me

Run time error '2593'

This feature is not available in an mdb.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[lylefair]

I have done this in a ADP file.

I have found the report behaves so strangely afterwards that I have
discarded this procedure from my repertoire.

[Tim Marshall]

David Lloyd wrote:

Tim:

The following KB article and the associated white paper addresses the issue
you are seeing. The bottom line is that you can only bind a recordset to a
report in an Access project (.adp). It will not work in a .mdb file.

http://support.microsoft.com/default...b;en-us;301987

Thanks David, I'll look at this. A frantic google search later last
night confirmed the bottom line you mentioned. 8(

Forgive the profanity, but what half-arsed moron(s) at Microsoft needs
to be (figuratively, not literally) impaled on a giant razor blade for
this gross neglect? The ability to use ADODB DSNless connections was
something I was quite excited about; the Access report writer is an
absolutely wonderful tool with its ability to bind to data sources. I
would really like to kick somebody's ass for this. Goddamned bastards.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

ly******@yahoo.ca wrote:

I have done this in a ADP file.

I have found the report behaves so strangely afterwards that I have
discarded this procedure from my repertoire.

And, sadly enough, from David Lloyd's link and further research I've
done, ADPs are for SQL Server and I'm an Oracle user. I can still use
my DAO methods, but my hopes of dropping the shackles of having to run
around to users' stations to set up DSNs have been dashed.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:db**********@coranto.ucs.mun.ca:

ly******@yahoo.ca wrote:

I have done this in a ADP file.

I have found the report behaves so strangely afterwards that I
have discarded this procedure from my repertoire.

And, sadly enough, from David Lloyd's link and further research
I've done, ADPs are for SQL Server and I'm an Oracle user. I can
still use my DAO methods, but my hopes of dropping the shackles of
having to run around to users' stations to set up DSNs have been
dashed.

Why? It's always been possible to do DNS-less ODBC connections. You
just can't have linked tables. You could write the connect strings
into saved queries, though, and use those to replace your linked
tables.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc