473,322 Members | 1,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Querydef problem in mdb>adp conversion (A2K)

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
Nov 12 '05 #1
7 2313
"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.
Nov 12 '05 #2
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
Nov 12 '05 #3
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?).

Nov 12 '05 #4
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

Nov 12 '05 #5
"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.
Nov 12 '05 #6
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

Nov 12 '05 #7
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

Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Jegger | last post by:
Hello! We have following situation; network with 100 users, aplication developed in Access, user DB deployed on SQL Server. Is it better to create query inside aplication (with code) and then...
7
by: brett valjalo | last post by:
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...
1
by: Beowulf | last post by:
I have a report laid out in Design View as shown at the end of this message. I have code that performs the following steps: 1. In main report's Report_Open(), DELETE any old rows in tblTOC for...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.