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