setting Access query criteria

[Aussie Rules]

Hi,

I have a access 2007 database with a cross tab query.

Based on the selection critieria the cross tab may contain a different
number columns in the result. For example if the query is 'select * from
crosstab where project =1' would retun 6 columns, but if I change the query
to project =2 then the result would be 20 columns.

In my VB2008.net application I create the sql select statement. The problem
seems to be when I do this I get back 20 cols, even if I am after project 1.
the columsn from 7-20 are empty, but they are still there.

When I execute the query within Access 2007, and build the selection
criteria (project =1) into the crosstab query, it works properly and only
returns 6 cols.

So I figure I have to some how set the selection criteria in the cross tab
query in the access database, then use vb to get the results.

How can you in Vb.net, set the Access queries selection criteria and then
execute the query ?

Hope that makes sense...

 

[Cor Ligthert[MVP]]

This depends completely where you have keep the results of the resultset the
database is returning and how you retrieve that resultset.

You write not one character how you do it in VB. How or what kind of
resultset you get is not important for VB.

Cor

 

[Aussie Rules]

I have in my vb.net application

"select * from crosstabQry where project =1"

This creates a result set in vb of 26 cols, which is correct.

If I change the vb.net query to be '.... where project =2' then I get a
result set of 26 cols, which is not correct. there are only 5 cols of data,
the others are empty.

Now if I go into Access2007, and edit the actual cross tab query in the
access query designer and set a criteria, I get back the proper number of
columns for each query.

So I figure that I have to pass in the project id into the crosstab query,
have access do the query internally and then hand back my vb application the
result set.


Does that make more sense ?

 

[aaron.kempf]

have you tried doing the same thing with SQL 2005 Express 'PIVOT'
keyword?

I just dont get it; why would someone use Access Database with the
shiny new programming language.
Does VB even support the .ACCDB format?

I think that this shows that MDB format is depecrated.

-Aaron