Querydef problem in mdb>adp conversion (A2K)

[brett valjalo]

Hey Folks:

Long time no see! Hope everyone is well.

I have an old mdb I'm upsizing to an adp. There is a button on a form
which executes code similar to the following (this is a search form
where sql is dynamically generated based on 1-7 criteria fields chosen
by the user):

strSQL = strSQLBase & strWhere & strOrder
Set db = CurrentDb()
Set qdf = db.QueryDefs(strMasterQuery)
qdf.SQL = strSQL
docmd.openquery strMasterQuery

The idea here is that the user can then dump the results from their
parameter query to excel (tools>office links>analyze with excel) from
the query result window that's brought up by the openquery method.

Been racking the brain, so if anyone knows how to do this kinda thing
in an Access 2000 adp, I'd be eternally grateful.

(I would guess it's probably easier in Access 2K2-3, cause aren't
there 'extended properties' that sorta 'emulate' Jet? I actually do
have AK3 on this machine, but my users don't. If anyone could explain
how to do it in AK3, with the extended properties, that would be
helpful too, but the A2K solution is really the one I need for now...)

TIA,
brett

[Michael \(michka\) Kaplan [MS]]

"brett valjalo" <bv******@sfhp.org> wrote...

Long time no see! Hope everyone is well.
Hi Brett!
I have an old mdb I'm upsizing to an adp. There is a button on a form
which executes code similar to the following (this is a search form
where sql is dynamically generated based on 1-7 criteria fields chosen
by the user):

strSQL = strSQLBase & strWhere & strOrder
Set db = CurrentDb()
Set qdf = db.QueryDefs(strMasterQuery)
qdf.SQL = strSQL
docmd.openquery strMasterQuery

The idea here is that the user can then dump the results from their
parameter query to excel (tools>office links>analyze with excel) from
the query result window that's brought up by the openquery method.

Been racking the brain, so if anyone knows how to do this kinda thing
in an Access 2000 adp, I'd be eternally grateful.
Well, the key is that there is no CurrentDb, and no local queries, and not
DAO access to server side objects.
(I would guess it's probably easier in Access 2K2-3, cause aren't
there 'extended properties' that sorta 'emulate' Jet? I actually do
have AK3 on this machine, but my users don't. If anyone could explain
how to do it in AK3, with the extended properties, that would be
helpful too, but the A2K solution is really the one I need for now...)

I am not sure how they would specifically help here -- but to open an object
in the UI I think it has to exist (so you would have to open an existing
view, wouldn't you?).
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.

[Ted Theodoropoulos]

little things like this can sometimes be a pain in the ass even though
they are seemingly simple. i posted some code to the ng on how to do
something similar a few months ago. have a look. hope it helps...

http://groups.google.com/groups?hl=e...ing.google.com

[brett valjalo]

Hi Michael:
Still around I see? Good deal. I've been pretty scarce as I've been
doing vb.net and sql server work of late. Went to vbits two weeks
ago, can't wait for Whidbey, man! It looks friggin SWEET ... You
still working at MS? Working on that project at all?

Anyway, yeah, I mean, you've basically stated why it is that I have
the problem. I was just looking for a workaround, & knew this would
be the place to come if there was ;)

I just decided to assign my dynamically-built SQL to the recordsource
of a report in it's Open event, and have the user export to Excel from
the report instead of a query. I knew I could do that all along, but
I wondered if there was anything possessing props remotely like the
ol' querydef object in a .adp - suspected there was not, but it never
hurts to ask. Never know what you might learn about at CDMS, ya know?

Take Care,
Brett

"Michael \(michka\) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in message news:<40********@news.microsoft.com>...

"brett valjalo" <bv******@sfhp.org> wrote...

Long time no see! Hope everyone is well.

Hi Brett!

I have an old mdb I'm upsizing to an adp. There is a button on a form
which executes code similar to the following (this is a search form
where sql is dynamically generated based on 1-7 criteria fields chosen
by the user):

strSQL = strSQLBase & strWhere & strOrder
Set db = CurrentDb()
Set qdf = db.QueryDefs(strMasterQuery)
qdf.SQL = strSQL
docmd.openquery strMasterQuery

The idea here is that the user can then dump the results from their
parameter query to excel (tools>office links>analyze with excel) from
the query result window that's brought up by the openquery method.

Been racking the brain, so if anyone knows how to do this kinda thing
in an Access 2000 adp, I'd be eternally grateful.

Well, the key is that there is no CurrentDb, and no local queries, and not
DAO access to server side objects.

(I would guess it's probably easier in Access 2K2-3, cause aren't
there 'extended properties' that sorta 'emulate' Jet? I actually do
have AK3 on this machine, but my users don't. If anyone could explain
how to do it in AK3, with the extended properties, that would be
helpful too, but the A2K solution is really the one I need for now...)

I am not sure how they would specifically help here -- but to open an object
in the UI I think it has to exist (so you would have to open an existing
view, wouldn't you?).

[brett valjalo]

Thanks, Ted. Figured a workaround using a report instead. At least
you can still dynamically assign SQL to the recordsource of a report
in an .adp, my saving grace in this problem...

Peace,
Brett

te********@yahoo.com (Ted Theodoropoulos) wrote in message news:<f5*************************@posting.google.c om>...

little things like this can sometimes be a pain in the ass even though
they are seemingly simple. i posted some code to the ng on how to do
something similar a few months ago. have a look. hope it helps...

http://groups.google.com/groups?hl=e...ing.google.com

[Michael \(michka\) Kaplan [MS]]

"brett valjalo" <bv******@sfhp.org> wrote...

Still around I see?
Yep.
Good deal. I've been pretty scarce as I've been
doing vb.net and sql server work of late. Went to vbits two weeks
ago, can't wait for Whidbey, man! It looks friggin SWEET ... You
still working at MS?
Yes.
Working on that project at all?

Yes, my group owns large parts of the System.Globalization and System.Text
classes in the framework. I personally own collation (Compare/Sort keys) and
am anowner emeritus and part time bug fixer (wants fries with that?) for
locales and such.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.

[MacDermott]

So why do you need this in a querydef, rather than just in an ADO recordset?

AFAIK, the immensely useful recordsetcopy command works only with a DAO
recordset (I'd love to learn I'm wrong on that), but even with an ADO
recordset, you can loop through the records and fields and copy them into an
Excel spreadsheet with automation.
You could even use an Excel template to pre-format your output.

HTH
- Turtle

"brett valjalo" <bv******@sfhp.org> wrote in message
news:1f**************************@posting.google.c om...

Hey Folks:

Long time no see! Hope everyone is well.

I have an old mdb I'm upsizing to an adp. There is a button on a form
which executes code similar to the following (this is a search form
where sql is dynamically generated based on 1-7 criteria fields chosen
by the user):

strSQL = strSQLBase & strWhere & strOrder
Set db = CurrentDb()
Set qdf = db.QueryDefs(strMasterQuery)
qdf.SQL = strSQL
docmd.openquery strMasterQuery

The idea here is that the user can then dump the results from their
parameter query to excel (tools>office links>analyze with excel) from
the query result window that's brought up by the openquery method.

Been racking the brain, so if anyone knows how to do this kinda thing
in an Access 2000 adp, I'd be eternally grateful.

(I would guess it's probably easier in Access 2K2-3, cause aren't
there 'extended properties' that sorta 'emulate' Jet? I actually do
have AK3 on this machine, but my users don't. If anyone could explain
how to do it in AK3, with the extended properties, that would be
helpful too, but the A2K solution is really the one I need for now...)

TIA,
brett

[brett valjalo]

Sure, I certainly could do as you suggest, and it did cross my mind.
However, I don't have the time to write and thoroughly test that
automation code, and the users are used to the app (it's production
and created by someone else, and my piece is just the upsizing to
SS2K/.adp), so I was hoping for an easier solution with the exact same
look and feel that they are used to. Making a report as I described
in another post was close enough (they are used to hitting
Tools>office links>analyze w/Excel once the query is opened) to not
confuse them at all, so that's where I went with it. Thanks, though :)

"MacDermott" <ma********@nospam.com> wrote in message news:<l7****************@newsread3.news.atl.earthl ink.net>...

So why do you need this in a querydef, rather than just in an ADO recordset?

AFAIK, the immensely useful recordsetcopy command works only with a DAO
recordset (I'd love to learn I'm wrong on that), but even with an ADO
recordset, you can loop through the records and fields and copy them into an
Excel spreadsheet with automation.
You could even use an Excel template to pre-format your output.

HTH
- Turtle

"brett valjalo" <bv******@sfhp.org> wrote in message
news:1f**************************@posting.google.c om...

Hey Folks:

Long time no see! Hope everyone is well.

I have an old mdb I'm upsizing to an adp. There is a button on a form
which executes code similar to the following (this is a search form
where sql is dynamically generated based on 1-7 criteria fields chosen
by the user):

strSQL = strSQLBase & strWhere & strOrder
Set db = CurrentDb()
Set qdf = db.QueryDefs(strMasterQuery)
qdf.SQL = strSQL
docmd.openquery strMasterQuery

The idea here is that the user can then dump the results from their
parameter query to excel (tools>office links>analyze with excel) from
the query result window that's brought up by the openquery method.

Been racking the brain, so if anyone knows how to do this kinda thing
in an Access 2000 adp, I'd be eternally grateful.

(I would guess it's probably easier in Access 2K2-3, cause aren't
there 'extended properties' that sorta 'emulate' Jet? I actually do
have AK3 on this machine, but my users don't. If anyone could explain
how to do it in AK3, with the extended properties, that would be
helpful too, but the A2K solution is really the one I need for now...)

TIA,
brett