convert layered MS Access queries to .NET

[perkinesed]

I am trying to convert some MS Access queries to .net. These queries call
other queies, that call other queries, etc., sometimes up to five levels deep.
I am trying to keep from using subqueries because of the amount of testing
that would be required, some of the lower level queries are used in multiple
high level queries, and some of the lower level queries reference different
data sources.

Is there a way to use datatables or some other way to do what Access does?

 

[Bob Barrows]

I am trying to convert some MS Access queries to .net. These queries
call other queies, that call other queries, etc., sometimes up to
five levels deep. I am trying to keep from using subqueries because
of the amount of testing that would be required, some of the lower
level queries are used in multiple high level queries, and some of
the lower level queries reference different data sources.

Is there a way to use datatables or some other way to do what Access
does?

I don't understand ... why not simply call the saved queries? What does it
mean to "convert queries to .net"?

 

[Douglas J. Steele]

I don't understand ... why not simply call the saved queries? What does it
mean to "convert queries to .net"?

If any of the queries involved use VBA functions, then it's not possible to
call them from outside of Access, as Jet knows nothing about VBA. That could
be what "convert some MS Access queries to .net" means, in which case
there's really nothing that can be done.

 

[Sylvain Lafontaine]

If you want to go with .NET, you should switch to SQL-Server (the free
Express edition or one of the regular (not free) editions). JET is useful
when you use it with Access at the frontend but there is absolutely no point
in keeping it if you want to go with .NET. The fact that you already have
trouble managing queries covering five levels should have give you a hint
about that.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob Barrows]

Not that I disagree with your advice, but I'm having trouble understanding
your reason for offering it in this instance. How will using SQL Server make
his problem simpler? Instead of 5 levels of saved queries, he would now be
dealing with 5 levels of views, with the same problem he's now posting about
(not that I can say I understand the problem he is having).

 

[Sylvain Lafontaine]

And what about the lack of formating as a start?

It's because it's much more easier to write complex queries inside a
SQL-Server stored procedure than it is with queries in Access:

1- Formating

2- No need to cut your query into multiple pieces because otherwise JET will
spit you back its "query to complex".

3- Full syntax for writing complex queries with inner join and outer join
(Left, Right, Full) without the need to put parenthesis everywhere and with
full support for the ON operator in the case of outer join; possibility to
mix all these with subqueries, again without the risk of JET spitting back
its too familiar error message.

4- etc.

JET is practically the same old sql dialect and query engine acreated more
than 20 years ago and it has never changed since; excerpt for the switch to
Unicode and maybe a few bug here and there (but they are still many bugs
lurking in it). For the rest, it has a much evolued as a dead fossil.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Tom van Stiphout]

You need to be more specific. ".net" is not a database, while I assume
you consider "MS Access" a database (technically, Jet (or with A2007:
ACE) is the default database underlying MS Access - the forms
package).
What database are you trying to convert to?

-Tom.
Microsoft Access MVP

 

[Bob Barrows]

Again, these are all perfectly valid reasons for converting to SQL Server.
They just don't seem relevant to what the OP is asking for help with.

Please, perkinesed, respond to the various inquiries people have made in
this thread. We cannot answer your question as it stands.

Are you trying to replace your database with objects created in .Net code?
If so, you have to understand: sql requires a database engine. There is no
database engine in .Net so .Net can neither parse nor execute sql queries on
its own. It has to pass those sql statements to a database engine which can
do those things. Yes, ADO.Net can get you close with some of its datatable
functionality, but it is certainly not intended to be a replacement for a
real database engine.

 

[Sylvain Lafontaine]

Oh, sorry, I might have misunderstood your last reply.

I think that this is relevant to the OP because this is exactly one of the
reason why I switched from JET to SQL-Server a few years ago: with JET, I
was becoming more and more burried under a collection of small queries - by
the hundreds and then by the thousands - by each day until I realized that I
was simply drowning in them (ie., trying to manage them) instead of working
(ie, adding some new useful functionality).

It looks to me the path that the OP is now following is exactly the same
path that I have myself followed a few years ago and the solution was to get
out of JET a quickly as possible.

JET might be useful if you are using Access as the frontend and want some
exclusive functionality like the possibility of pessimistic locking for your
forms but inside .NET, none of these functionality are available. I just
don't see the point of keeping JET if you want to switch to .NET: you get
all the disadvantages of using JET and none of its advantages.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob Barrows]

Ah, I get you now. Thanks for clarifying.
The ability to use real stored procedures may also help the OP out of his
hole.