Querying a Dataset

[benlinkknilneb]

Hey all...

I'm working on an app that pulls records from Access 2000 (still waiting for the $$$ to go to SQL Server). I know how to get records into a dataset object, That's no problem. Because of the flexibility I've tried to give the user, my queries/stored procedures need to be broken into 2 steps, you know, querying a query. I want to fill the dataset from the lowest-level query and then pare down my results from the dataset object, so that Access isn't under so much strain. How can I execute SQL Statements on a datatable object in VB.NET?

Thanks in advance.
Ben

 

[cpastre]

Take a look at the System.Data.DataView class. You won't be able to run verbatim SQL statements but you can use it to do simple filters/sorts/etc.

Charlie

Charlie Pastre MCSD, MCSE, MCDBA, CCEA, CCNA, CCDA
Transition/1 MAS
cpastre@t1mas.com

 

[benlinkknilneb]

Thanks, Charlie... but I'm needing a little more than filtering. I'm generating basic statistical results, like averages, percentages, and that sort of thing. The percentages, in particular, are not on a by-row basis; that is, the percentage returned to the user should actually be the percentage for the entire set of rows which meet the user's criteria... so even the SQL way isn't really pretty. Can I do all of that through a filter?

Ben

 

[BigRetina]

I think that U should look at the filter strings. I think that U can do aggregate functions using filters.
Check MSDN

 

[benlinkknilneb]

Hey guys, I started working on this again, and a reference book I have mentions DataRelations. Anybody familiar with them? I actually figured out how to relate the two tables, but I ran into a new problem:

My final sub-query in the project is a one-way join with some aggregating... specifically, 2 separate fields are summed when all the other data matches up. From what I understand of the DataRelation object, it's equivalent to saying "INNER JOIN"... is this the case? Can I set this thing up differently to conceptually say "LEFT JOIN"?

Ben

 

[sapbucket]

interesting question. I have to overcome the same hurdle very soon. I thought that what I would do is use the built in script language (in Access) to formulate my results and store them within an Access table, and then use simple SQL queries. Why write difficult SQL statements if the functionality is built into Access? I have been told my approach is bad for when I have alot of users accessing data, but, my app has max 3 people on at a time - so no prob there. Overload Access? Nahhh...Thats why we have so much computing power and RAM!
Anyway, you should repost your solution that you find so that the rest of us know what to do! (A lot of people in your shoes).

Tom

 

[benlinkknilneb]

Hey Tom...

I plan to post my solution; that is, assuming I find one! Concurrency issues are more of a problem for me... probably about 15 people with access to the app, and according to everything I've read, 10 is just about all Access can keep up with. So, I'm trying to use ADO.net and create disconnected copies of my data. The solution that I've come up with is to use a DataRelation object to simulate the join. I still haven't gotten it to work, but I'm making (slow) progress. I don't think that I've found the best way to do it... but nobody out there who knows the "better ways" seems to answer my posts.

I agree with the people who've told you that Access is not the *best* way to do it... but if you've only got 3 people to worry about, it'll do fine. The original version of our program was 100% Access, and we're trying to convert it now to handle the extra people.

Ben