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

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

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

Unfortunately, I am a bit of an expert on this topic. In short : stay
with Access 97.

There are quite a few posts on the net dealing with slow performance
upgrading Access 97 to 2000/XP. Nearly all of them focus on obscure
and mainly insignificant recordset properties, etc.

The real problem is that when Access 2000 was built, VBA was thrown
away and VB6 grafted on to Access. In the process, the Jet SQL parser
was rewritten (since you can use VB operators in SQL statements in
Access), but not fine tuned in the same way as Access 97 (read :
hopelessly broken for all but the simplest queries)
So it is simply not possible to get the same performance in 2000/XP
using ODBC.
The MS solution is to use the ADP format project, which throws away
JET and uses SQL server direct, also throwing away most of the reasons
you would want to use MS Access.
You can use ADO to connect in code, and possibly use that to pass
recordsets to forms, but its a huge workaround.

As an example of problems with the 2000/XP parser, you can try
executing a query on an ODBC linked table :

SELECT IDValue FROM VeryLargeTable WHERE IDValue=25

this should be instant if IDValue is the indexed PK.
now write

public function Return25 () as long
Return25 = 25
end function

and try

SELECT IDValue FROM VeryLargeTable WHERE IDValue=Return25()

On my database, in Access 2000/XP the first ran instantaeously, the
second took 19 seconds (clearly a full table scan as you relate in
your post).
In Access 97, both run instantaneously.

So : my conclusion, after much testing and hair tearing -- its busted.
RIP ODBC.
If you find any magic tricks to cure these ills PLEASE let me know
(post here !).
Failing that, I'll eagerly await Access 2060 to see if the problem is
fixed. Unfortunately, the longer we wait, the less chance of this
happening, I think!

As an aside, Sagekey software make a great Wise/Installshield install
script for about $350 US which installs the Access 97 runtime and
allows it to coexist with any other version of Access (2, 95, 97,
2000, XP). You can bundle your A97 databases with this and then the
client doesn't have to worry about having Office 97 anymore.
This is what I do.

ODBC/Jet works fine in Access 97, and what's more, i _like_ it. It's
fast and has a lot of advantages. You get two databases for the price
of one (a front and back end database engine, no less, talk about
distributed computing !).
VIVA LA ACCESS 97 !!

All in all, MS have kneecapped ODBC development in new versions of
Access. I'm getting ready to move to Visual Studio .NET, as they
probably want me to. Access was solving too many problems for its
price tag.

commiserations,

Ben McIntyre
Self confessed Access 97 fan.

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

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
> >

Hi Ben,

thank you very much for your answers.

We went back to ACC97 for this time, because it's running well with
ODBC ( few exceptions ) and we don´t have enough time now to migrate
to any other platform, though we would like to do.

A few questions:
1) How could I use VB code in SQL?
2) I have another problem with blanks in ColumnNames on the SQL-Server
when I link this table in Acc97. I write another articel about this.
Maybe you know a solution.
3) Is there a possibility to update linked views?
( I had the problem, that you can´t use the requery command of a
recordset, when one of the queries used in the query is a
Pass-Through-Query. I solved the problem: I created a view on the
SQL-Server via code and linked the view as a table in Acc97. Works
fine. But those views ( tables in Acc97 ) couldn´t be updated. Is
there a solution to update them nevertheless? ( I know that some views
couldn´t be updated, but I talk about view, on which you can run a
INSERT-Statement with SQL-Query-Analyzer ) )



Thanks for taking so much time!

Andreas Lauffer, easySoft. GmbH, Germany.

On 11 Sep 2003 17:23:54 -0700, ben_spam@mailcity.com (Ben McIntyre)
wrote:

>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
>> >

Andreas Lauffer <a.lauffer@easysoft.de> wrote in message news:<fmldmvglfsjaldqj5a9ikc3labtd6ss2pb@4ax.com>. ..
> Hi Ben,
>
> thank you very much for your answers.

No worries. This issue has taken a lot of my time. No point
reinventing the wheel, if we can avoid it.
Sorry this reply took a while. It was too long since my last
pass-through query and I had to do some experimenting. Busy at the
moment.

>
> We went back to ACC97 for this time, because it's running well with
> ODBC ( few exceptions ) and we don´t have enough time now to migrate
> to any other platform, though we would like to do.
>
> A few questions:
> 1) How could I use VB code in SQL?

By that, I mean things like the following are legal in JET :

SELECT SomeVBFunction(sometable.somefield2) FROM sometable

or

SELECT somefields FROM sometable WHERE
somefield1=SomeVBFunction(somefield2)

You can use the builtin nz() function, or any public user designed
function. This means you can integrate sophisticated functionality
directly in to the SQL.
The VB function will be evaluated once for each row.

You can also use form controls in a query (as is common in access) :

SELECT somefields FROM sometable WHERE somefield1 =
forms!someform!somecontrol

This makes User Interface design really easy. For example, you can
just requery a listbox control when the value of a control it depends
on is changed. Otherwise, you have to write code to construct a new
SQL query with the WHERE clause containing the current control values.

The point I make is that using ADPs in Access, or VB, you cannot embed
this stuff in the SQL, so you are forced to take the second path and
construct SQL statements manually using code. This is very time
consuming.

> 2) I have another problem with blanks in ColumnNames on the SQL-Server
> when I link this table in Acc97. I write another articel about this.
> Maybe you know a solution.

Sorry, not that I know of. The best solution is to do a
global-search-and-replace of all objects in access and eliminate the
spaces from the table and column names.
I have a routine which will do this (search/replace all code, forms,
reports, queries, and SQL from form recordsources or control row
sources). Let me know if you want a copy.

Given that, it's not that hard to create an automated tool to do this
operation to each column in the database.
Unfortunately, ODBC just can't cope with spaces. Apart from if they
are inside square braces [], and it doesn't seem to use braces in the
default linked table/query drivers.

> 3) Is there a possibility to update linked views?
> ( I had the problem, that you can´t use the requery command of a
> recordset, when one of the queries used in the query is a
> Pass-Through-Query. I solved the problem: I created a view on the
> SQL-Server via code and linked the view as a table in Acc97. Works
> fine. But those views ( tables in Acc97 ) couldn´t be updated. Is
> there a solution to update them nevertheless? ( I know that some views
> couldn´t be updated, but I talk about view, on which you can run a
> INSERT-Statement with SQL-Query-Analyzer ) )
>

To clarify an earlier statement :

ODBCDirect workspaces :
The ODBCDirect workspace communicates direct with the back end via
SQL, bypassing JET entirely. Because of this, it can do anything or
return any datasets you could do by executing commands in Query
Analyser. However, it can ONLY be used in code. The recordsets
cannot be bound to forms or reports, and ODBCDirect objects are _not_
visble in the database window.
We have ended up with a whole bunch of objects which cache values from
ODBCDirect recordsets and can feed it to listboxes (and more) and then
back to the database at the end of the process.

For info about ODBC Direct in Access 97 look for help on :
data access overview
Again, I'm happy to send you some code to set up the default
ODBCDirect objects if you'd like.

JET Workspaces :
All the ODBC linked tables and pass-through queries are in fact
members of the default JET workspace. Not sure exactly how the linked
tables/queries maintain session data, but they use a Connection String
to find and authenticate with the back end.
Now ALL SQL which is passed through JET gets 'pre-processed'
(=mangled) by the JET engine. This causes problems with complex
queries using linked tables, and is what causes the speed problems in
Access 2000 and XP.
And yes, even the pass-through queries, which you would expect to
actually 'pass-through' are extensively changed by JET.

But now back to the question ...

Not sure about pass-through queries, I have hardly ever used them.
The problem you mention is probably why.

What I have done in this situation is link the view to Access as a
table, then create a query in Access selecting the view-table
(read-only) joined to the linked table(s) you want to update using
only 1-to-1 inner joins. Bind this to your form, and you should be
OK. The view/table provides the complex information, and the tables
provide the updateability.

cheers,

Ben McIntyre
Horticulture Software Solutions

>
>
> Thanks for taking so much time!
>
> Andreas Lauffer, easySoft. GmbH, Germany.
>
> On 11 Sep 2003 17:23:54 -0700, ben_spam@mailcity.com (Ben McIntyre)
> wrote:
>
> >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
> >> >