473,847 Members | 1,513 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(st rMasterQuery)
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 2343
"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(st rMasterQuery)
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.microsof t.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(st rMasterQuery)
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********@yaho o.com (Ted Theodoropoulos) wrote in message news:<f5******* *************** ***@posting.goo gle.com>...
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.Globaliz ation 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.goo gle.com...
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(st rMasterQuery)
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********@nos pam.com> wrote in message news:<l7******* *********@newsr ead3.news.atl.e arthlink.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.goo gle.com...
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(st rMasterQuery)
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
3434
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 pass it to SQL Server for execution or is it better to have all these queries saved like stored procedures and then called from aplication?
7
497
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 where sql is dynamically generated based on 1-7 criteria fields chosen by the user): strSQL = strSQLBase & strWhere & strOrder
1
2202
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 this username. 2. In main report's CategoryHeader_Format(), add a row to tblTOC with the current category name and the current page number. 3. In the table of contents subreport, Cancel if NoData event fires.
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9734
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10991
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10653
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9490
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7061
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5725
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4540
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.