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!
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.
And changing to a ADP-Project is an immense project for us, we have a
project with 227 tables, 979 queries and 266 Forms.
Changing to .NET would be another option, but with even more migration
time.
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?
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
>
>
>Poor Andreas
>
>I am unfortunately a bit of an expert in this area. My call : stick
>with access 97. Unfortunately, this produce is getting a bit old.
>
>What happened is that when Access 2000 was built, VBA was thrown away,
>and VB6 grafted on to the product. In the process, the JET/ODBC SQL
>parser was rebuilt (since you can use VB in SQL in Access), but not
>tuned to the same degree (in fact, read : hopelessly broken).
>
>There are many posts about speed problems 97 to 2000/XP, but most
>concentrating on obscure and mainly irrelevant settings.
>The only way to successfully use ODBC SQL with Access 2000/XP i