Andreas Lauffer <a.lauffer@easysoft.de> wrote in message news:<5ic0mvsvh6h6v513ngnh80injhnam66v54@4ax.com>. ..
> We was also very contented with Access97, but I had a problem with
> Access97 and some queries with linked ODBC-Tables:
>
> SYMPTOMS
> When you create a nested query against linked SQL Server tables and
> the top level query contains an outer join, you receive the following
> error message when you try to run the query:
>
> ODBC--Call Failed.
>
> [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix
> 'name' does not match with a table name or alias name used in the
> query. (#107)
>
> Microsoft tells about this error, I copied this text from this link:
> http://support.microsoft.com/default...b;EN-US;300830
>
> But this wasnīt any solution for Access97 and Jet 3.51, so I just
> tried this special query with this problem on AccessXP, and it worked!
Firstly, sorry about the double post before (the second one is
complete - a random hand move clicked the mouse exactly on the 'post'
button !)
Look, I have had all sorts of problems using ODBC linked tables with
any form of sophisticated query.
However, it's easy to bypass the problem by opening up a DAO
ODBCDirect workspace. This communicates direct with the back end
(bypassing JET completely, and I think even avoids the ODBC
preprocessor), and you can run literally ANY query, not to mention DDL
commands, ie. creating stored procedures, views with hints, etc, and
do back end transactional work (BEGIN TRANS, COMMIT, etc).
And to return result sets direct form the back end, you can use an
ODBCDirect Query.
I tend to use the ODBC linked tables only for binding to forms or
doing GUI work or simple lookups, and because you can mix VB code in
the SQL, put control references in there, etc, they are extremely
handy.
This is also where, if you want to shift some complex processing to
the front end, you can run a gnarly query (say a crosstab) in Access
JET using the ODBC linked tables, taking the load off the server.
All serious back end work MUST be done with ODBCDirect.
Not sure if you know about this, so I won't go further now, but I can
send you some code if you like to set all this up (my email is :
no_benmc_ham@bigfoot.com.au, remove the no_ and _ham)
>
> Shall I migrate back to Acc97? What about all the third-party
> Controls? They will stop developping their controls for Jet 3.51 in
> the near future, if they just hadnīt stop already.
>
Yep, it's getting more obsolete every day. Most are ADO as well, and
aren't compatible with Access 97.
> And changing to a ADP-Project is an immense project for us, we have a
> project with 227 tables, 979 queries and 266 Forms.
>
I hate ADP's. You lose all the abilities to put VB code or control
references into your SQL. What's the point ? You may as well use
VB6.
> Changing to .NET would be another option, but with even more migration
> time.
>
Long term, .NET offers amazingly sophisticated features (as good as
Java, or better). But it is like trying to fly a Jumbo when you're
used to the Access Cessna.
Your products probably rely heavily on Access 97 infrastructure.
> The other points is that, that 95% of our clients use a Jet-Database
> as server and only 5% use SQL-Server. Our software should support both
> databases with as less as possible different code.
> Or our Clients who uses Jet must upgrade to MSDE.
>
> What shall I do?
That makes it _really_ difficult.
I would have to recommend working around the ODBC linked queries that
have problems, by using ODBCDirect. Write separate SQL/VB code only
for these queries.
Long term, there are only two options :
1. Get MS to fix the ODBC parser.
This might seem like a long shot, since ODBC is a bit long in the
tooth now, but there is such a HUGE legacy riding on it, it really
would be in their interests.
I have been too lazy to complain, but we could send examples of fast
vs slow ODBC linked queries and at least prod them to get back to A97
functionality (which, as I said, is still not great for really complex
queries).
2. Redesign the project
Migrate to a later version of Access (but not sure how to set it up to
talk to SQL, with ODBC broken and ADP sucking so badly).
Visual Studio .NET. once you learn how to drive it, is incredibly
powerful and offers other features like ASP.NET which allows closer
integration and reduces web design time by a huge factor, and web
services, COM objects etc can be built practically at the touch of a
button.
You can use all your legacy VB code.
Migrating VBA code to VB6 is really easy, I haven't tried it en masse
to VS .NET yet, but you can usually stick with DAO/ADO by declaring it
specifically.
You could migrate to the .NET paradigms (ie. Database access methods)
as it becomes appropriate.
regards and good luck,
Ben McIntyre
Horticulture Software Solutions
>
> Andreas, easySoft. GmbH, Germany
>
>
>
> ---------------------------------------------
>
> On 10 Sep 2003 18:33:50 -0700, ben_spam@mailcity.com (Ben McIntyre)
> wrote:
>
> >Andreas Lauffer <a.lauffer@easysoft.de> wrote in message news:<3ijulvk01f2lk1chiajomp62rvs4c13bc9@4ax.com>. ..
> >> I changed from Access97 to AccessXP and I have immense performance
> >> problems.
> >>
> >> Details:
> >>
> >> - Access XP MDB with Jet 4.0 ( no ADP-Project )
> >> - Linked Tables to SQL-Server 2000 over ODBC
> >>
> >> I used the SQL Profile to watch the T-SQL-Command which Access ( who
> >> creates the commands?) creates and noticed:
> >>
> >> 1) some Jet-SQL commands with JOINS and Where-Statements are
> >> translated very well, using sp_prepexe and sp_execute, including the
> >> similar SQL-Statement as in JET.
> >>
> >> 2) other Jet-SQL commands with JOINS and Where-Statements are
> >> translated very bad, because the Join wasnīt sent as a join, Access
> >> collects the data of the individual tables seperately.
> >> Access sends much to much data over the network, it is a disaster!
> >>
> >> 3) in Access97 the same command was interpreted well
> >>
> >> Could it be possible the Access uses a wrong protocol-stack, perhaps
> >> Jet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server or
> >> Jet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead of
> >> Jet to ODBC and ODBC direct to SQL-Server
> >>
> >> Does anyone knows anything about:
> >>
> >> - Command-Interpreter of JetODBC, Parameters, how to influence the
> >> command-interpreter
> >> - Protocol-Stack of a Jet4.0 / ODBC / SQL-Server application
> >>
> >> Thanks , Andreas
> >