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 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.
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?).
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
"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.
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
|
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...
| |