Re: convert layered MS Access queries to .NET

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:u5qxie4aKHA.2184@xxxxxxxxxxxxxxxxxxxxxxx
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 wrote:
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.

"Bob Barrows" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:%23Z3Kp5uaKHA.5348@xxxxxxxxxxxxxxxxxxxxxxx
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 wrote:
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.


"perkinesed" <u56376@uwe> wrote in message news:9f74c813fd3cc@xxxxxx
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?

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



.

Relevant Pages

  • Re: convert layered MS Access queries to .NET
    ... these are all perfectly valid reasons for converting to SQL Server. ... you have to understand: sql requires a database engine. ... database engine in .Net so .Net can neither parse nor execute sql queries on ... This email account is my spam trap so I ...
    (microsoft.public.access.queries)
  • SQL Rules
    ... I am a SQL developer known personally to most of the MVPs (I have been one ... A client brought me an existing application that searched a large Jet table ... These are all very simple queries, filtering on a set of rows by 10 or so ... the most incredible feat is that Express creates a clustered index ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Bitwise And
    ... is saved/flagged as an 'ANSI' mode view or procedure. ... 'ANSI' queries can also (and this ... the SQL is also Jet complient SQL, ...
    (microsoft.public.access.queries)
  • Re: convert layered MS Access queries to .NET
    ... of the reason why I switched from JET to SQL-Server a few years ago: ... you have to understand: sql requires a database engine. ... execute sql queries on its own. ... This email account is my spam trap so ...
    (microsoft.public.access.queries)
  • Re: Official Status of SQLServer 2005 ADP
    ... "remains with Jet and linked tables or remains with Jet ... but go with SQL pass-through queries and unbound forms" ... RecordSource is a query with a where clause that limits the number of rows ... > queries under MDB was bad and worst than the one offered by ADP while you ...
    (microsoft.public.access.adp.sqlserver)