Hello again –

Actually all you might need for the quarter or year of data would be a view, not a stored procedure (or sproc for short). Views are very much like simple select queries in Access, and are updatable under some conditions, especially with SQL Server 2000, but cannot accept a parameter. So if you need a parameter (the start and/or end dates for example) then you would need a sproc. But if you are getting 350K records you are dealing with lots of data. On that presumption, you would bet 1.4M records for a year, and that’s pushing Access Jet data to the max if you have to store it in a local table and manipulate it. Is there any way to reduce the recordset further?

Sorry about the alphabet soup – MSDE stands for the Microsoft Desktop Engine, which is a slightly dumbed down version of SQL Server 2000. It will handle 3 or 4 users at once, has a limit of 2GB I believe, and has only rudimentary admin tools. However, if you can get the system guys to help you out, it can be administered with SQL Enterprise Manager and all of the normal SQL tools. It and SQL Server are the two options if you opt to go with an ADP rather than an MDB backend in Access 2000/2002. It can for example be populated with the Binary Copy Program, or with the Data Transformation Services that come with SQL Server. Either of those should be substantially faster than an Access append query using ODBC.

Wendell

OUCH! That’s a disaster in the making. As soon as your users start opening 97 databases, they are going to get prompts about converting them to 2000/2002. If somebody does, then the fun begins. We had 2000 for 9 months before our largest client started converting, and at that it took nearly 2 months to convert all the databases and the PCs. Best you start making lots of noise soon, or plan a long vacation about the time they do the upgrade.

Hi Cecilia,

I’m in the process now of converting my 97 apps to XP. So far I’ve only done a couple but they were big ones (lots of code). Plus, I upsized the backend of one of them to SQL 7 since it’s a multi-office app and our main office is converting first so I have to be able to use it in both 97 and XP. That was fun!

I’ve decided to convert them with the existing DAO code and then go back later to change it to ADO. With over 100 databases and a limited amount of time I don’t have much choice. It may not be the most efficient but as long as the database is compiled in Access97 before conversion so far it’s been a smooth process. And I haven’t noticed any performance difference yet. Anyway, I thought I’d mention it FWIW.

Excellent suggestion, Paul. We tend to forget that DAO still exists in Access 2002, even if Microsoft is hoping everyone will immediately convert to ADO.

In fact, I converted most of my A97 databases to 2000 without doing anything except disambiguating my code by specifically declaring objects as DAO.whatever. That way I could slip in an ADO reference and some ADO code going forward without going nuts trying to track down the errors but didn’t have to completely convert the apps to ADO. In A2k, at least, the DAO method is often faster than ADO for operations that involve interface/Jet objects, since DAO is optimized for Jet. I haven’t tested that in AXP but I would guess that you wouldn’t pay any real penalty for continuing to use the DAO code for a while.

I never had the habit of using the compatibility libraries to support obsolete code, so my 97 code was current to that version, not a patchwork of stuff from earlier versions which might cause problems in the conversion. It wouldn’t be a bad idea to check on those things prior to conversion, because there are a few techniques that definitely don’t work in the later versions of Access. The query engine changed somewhat between 97 and 2000, which can cause you some grief; and 200x is less forgiving of things like trying to close the database with a hidden form still open. You could spend your time chasing down the potential memory leaks and stuff that would otherwise get you in trouble and save yourself some grief later.

Better yet, get a copy of AXP and install it on your machine so you can try it out. Even the government sees reason occasionally (well, most of them … once in a while), so you might be able to sell them on that. It’s most likely the IT department rather than the “government” that is unwilling to give you an advance copy because they aren’t ready to support it. I was lucky when I was a government contractor because my government client had enough clout to get me the Office 2000 software and I had permission to install pretty much anything on my own workstation.

>> disambiguating <<

I like that word But does it realy exist?

Peter

Only in the mouths of software geeks since ADO was thrust upon us.