Upsizing Access 97

[Lind]

I have four complex Access 97 Databases which I need to upsize to SQLServer 7. The first thing I did was convert them to Access2000 because I was told that the upsizing wizard wal great. When I ran the wizard the report said that the queries couldn't be upsized because they had criteria which referred to a textbox on a form and that SQLServer couldn't handle this. When I remove the criteria, the queries seem to upsize very nicely. Dows anyone have a way to get the criteria information from the form to the query without having to do a major rewrite of database?

[Craig]

We have run accross this as well.

There are just some things that don't get upgraded to SQL server because they are not supported. In your case, it sounds like you will have to rewrite that portion of the application.


------------
Lind at 10/24/00 11:26:18 AM


I have four complex Access 97 Databases which I need to upsize to SQLServer 7. The first thing I did was convert them to Access2000 because I was told that the upsizing wizard wal great. When I ran the wizard the report said that the queries couldn't be upsized because they had criteria which referred to a textbox on a form and that SQLServer couldn't handle this. When I remove the criteria, the queries seem to upsize very nicely. Dows anyone have a way to get the criteria information from the form to the query without having to do a major rewrite of database?

[David Westmore]

I think the main thing you should bear in mind is that you don't necessarily have to upsize all of your queries to SQL.

You could just link the tables which were previously in Access (JET) format and leave the query in the MDB file and leave the queries as they are. N.B. this doesn't necessarily mean that the execution of the query runs client-side. I don't have any specific experience of using Form values as criteria in queries (I prefer to put the values into global public variables and use a public function to access the value in the query), but what happens in most cases is that Access (Jet) passes the query through to SQL, so it does run "server side" (which is obviously what one wants).

This would of course be heresy for many experienced SQL Server professionals, but it is important to understand that SQL 7 dramatically changed the performance relationship between static (views and stored procedures) and dynamic queries....I do a lot of this stuff and don't have serious performance problems, although I must admit that my databases are not enormous and I don't have large numbers of users (we measure volume in "tpd", transactions per day).

What I'm saying is that if you use Access as a front end to SQL, you can mix and match between using views and SPs and Access queries. Jet is a very very intelligent engine, it can figure out a lot of things on its own without us having to tell it what to do).

Of course, where you can, it is probably better to convert an Access query into a view, it's neater and tidier, you can use a serious security system to control who can see the view and what they can do with it, and it probably does have some small gain in efficiency.

I don't use stored procedures at all, I would just prefer not having to learn yet another language for programming (and a very ugly one).

Let's hope no serious SQL guru reads this reply, they'd probably come down on me like a ton of bricks (as we Brits say).

Hope this is useful...I am very interested in exchanging ideas and problems with other people using Access as a front end to SQL.

Regards

David Westmore
Universita' Cattaneo
Castellanza (Italy)
------------
Lind at 10/24/00 11:26:18 AM


I have four complex Access 97 Databases which I need to upsize to SQLServer 7. The first thing I did was convert them to Access2000 because I was told that the upsizing wizard wal great. When I ran the wizard the report said that the queries couldn't be upsized because they had criteria which referred to a textbox on a form and that SQLServer couldn't handle this. When I remove the criteria, the queries seem to upsize very nicely. Dows anyone have a way to get the criteria information from the form to the query without having to do a major rewrite of database?