I wish there were a way to directly maipulate the big colorful data tables
that Excel can now directly import from my database. =(

The only way I've found to handle data tables is using the old ADODB
connections...

So I put some combo boxes on my sheet to select the records I want. This
works minimally well because I can't just put objects into these boxes, I can
only put strings in, and I can't read the index of the string selected, I
have to take the string back, unfortuanetly the Find function on the ADODB
dataset doesn't work so I had to manually implement a good old linear search.
OK... not much time wasted...

Now I have my data set selected and I need to do two things with it. First,
I need to load it, here's the query:

####
SELECT assay_data_set.assay_data_set_id, assay_data_point_id, sample_name,
date, activity, assay_data_point.comments
FROM assay_data_point, assay_data_set
where assay_data_point.assay_data_set_id = assay_data_set.assay_data_set_id
and date is not null and feed_line_id = 11
UNION
SELECT assay_data_set.assay_data_set_id, assay_data_point_id, sample_name,
date_recieved as date, activity, assay_data_point.comments
FROM assay_data_point, assay_data_set
where assay_data_point.assay_data_set_id = assay_data_set.assay_data_set_id
and date is null and feed_line_id = 11
order by date
####

Pretty, huh? That doesn't include the date range part of the select. ;)

Well, here's where it works: It works in MySQL navigator, It works in the
colorful data table displayer in Excel '07, it works in Microsoft's Query
browser, even though I can't edit it there, it works when I print it out and
sing it from my rooftop... But guess where it doesn't work? -- Yeah. There. I
can't make it work in the ADODB connector, where I can give it parameters and
such. There's some limitation with the Union statement that I can't figure
out and doesn't seem to be documented anywhere. =( I'm not sure there's a way
I can code around this. Ideally I'd be able to use the big colorful built in
table interface. Which brings me to my second problem. There doesn't seem to
be any way to programmatically access my workbook connections and, ideally,
be able to set them up with my ADODB recordsets or, just as well, simply give
them new query strings and tell them to refresh. =(

For my next trick, I'll be grouping those by date and computing a bunch of
statistics from them...

I would do the following:

1.save your query as a stored procedure in the database, with all
parameters needed
2. write a module that gets all parameters from a userform/cells in
spreadsheet/ input boxes
3. connect to the db
3. build the stored procedure string: sp_MyStoredProcedure @param1,
@param2 .....
4. execute (docmd.execute TheString)
5. get data into a recordset
6. drop data to spreadsheet
7. define the region as a Excel table, with chosen format.

Does this help?

Docksi.

There is a lot of information here but this is one observation:

SELECT assay_data_set.assay_data_set_id, assay_data_point_id, sample_name,
date, activity, assay_data_point.comments

Is date a field name? Maybe there is a conflict with the reserved word.

Perhaps changing the field name to a non-reserved word, or using brackets
will help e.g.:

SELECT assay_data_set.assay_data_set_id, assay_data_point_id, sample_name,
[date], activity, assay_data_point.comments...

and/or specify the source table e.g. tableName.date, or tableName.[date].

--
Tim Zych
http://www.higherdata.com
Workbook Compare - free and pro versions

"AlonzoTG" wrote in message
...
I wish there were a way to directly maipulate the big colorful data tables
that Excel can now directly import from my database. =(

The only way I've found to handle data tables is using the old ADODB
connections...

So I put some combo boxes on my sheet to select the records I want. This
works minimally well because I can't just put objects into these boxes, I
can
only put strings in, and I can't read the index of the string selected, I
have to take the string back, unfortuanetly the Find function on the ADODB
dataset doesn't work so I had to manually implement a good old linear
search.
OK... not much time wasted...

Now I have my data set selected and I need to do two things with it.
First,
I need to load it, here's the query:

####
SELECT assay_data_set.assay_data_set_id, assay_data_point_id, sample_name,
date, activity, assay_data_point.comments
FROM assay_data_point, assay_data_set
where assay_data_point.assay_data_set_id =
assay_data_set.assay_data_set_id
and date is not null and feed_line_id = 11
UNION
SELECT assay_data_set.assay_data_set_id, assay_data_point_id, sample_name,
date_recieved as date, activity, assay_data_point.comments
FROM assay_data_point, assay_data_set
where assay_data_point.assay_data_set_id =
assay_data_set.assay_data_set_id
and date is null and feed_line_id = 11
order by date
####

Pretty, huh? That doesn't include the date range part of the select. ;)

Well, here's where it works: It works in MySQL navigator, It works in the
colorful data table displayer in Excel '07, it works in Microsoft's Query
browser, even though I can't edit it there, it works when I print it out
and
sing it from my rooftop... But guess where it doesn't work? -- Yeah.
There. I
can't make it work in the ADODB connector, where I can give it parameters
and
such. There's some limitation with the Union statement that I can't figure
out and doesn't seem to be documented anywhere. =( I'm not sure there's a
way
I can code around this. Ideally I'd be able to use the big colorful built
in
table interface. Which brings me to my second problem. There doesn't seem
to
be any way to programmatically access my workbook connections and,
ideally,
be able to set them up with my ADODB recordsets or, just as well, simply
give
them new query strings and tell them to refresh. =(

For my next trick, I'll be grouping those by date and computing a bunch of
statistics from them...