The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Access to SQL Server Queries

We have a very large database in MS Access and need to convert it to MS SQL Server. We already upgraded all tables, but have more then 600 queries which needs to be upgraded too.
Can anyone recommend a tool which does this automatically?
Don Nobody
Monday, July 11, 2005
You don’t mention if you are keeping ms-access as the front end. (after all, sql server don’t let you make forms, and it don’t let you make reports). So, are you continuing to use ms-access here? (what are you going to use for the UI part of this application now?)

600 quires * 2 minutes per query to cut and paste and, perhaps some editing into the Enterprise manager = 1200 minutes, or 1200 minutes / 60 = 20 hours.  So, you are talking about 20-30 hours of work here (my starting guesstimate). This assumes a good fluency in JET sql, and also sql servers sql.

Further, you don’t mention what programming environment etc. you are replacing the ms-access with. If you are continuing to use ms-access, then you can leave most of the queries as is unchanged. You then approach the problem just like refactoring code (you clean things up AS YOU use them – not all at once). You see, about 90% of the time you leave the query “AS IS” in ms-access. Note that 100% of the queries in ms-access if left in place (in ms-access) will work, but SOME will perform POORLY. (I am assuming linked tables in ms-access to sql server here – with linked tables, you don’t have to change the quires at all).

When you come across a query that runs too slow, that is the one query you re-factor (just take the whole query, paste it into a new view with the enterprise manager, and then create a linked table TO THAT VIEW in ms-access. You performance will be fixed. Further, this means that you likely only have to convert about 10% of the queries (you just made the job very manageable). Further, all quires can continue to be used AS YOU re-factor, so again this approach means no down time, and you can now use the data from the new system.

I don’t know of a automated tool, but the syntax, and use of functions in sql server is somewhat different then the sql used in ms-access. (prompts for parameters etc is also not available). I am willing to bet that MANY of your quires have parameters, and again this means the sql will not be easily changed to sql server (when you start having JET parameters, then you will likely to start using stored procedures in sql server..and NOT a view).

And, while we are at it, 600 quires sounds like WAY too many. So often, you see 10 or even 15 quires that are identical, but only with a few different conditions (this is sheer some one just cut and pasted the sql over and over and over – BAD BAD BAD!!). The original application should had ONE query, and prompted the user for the parameters, not re-copy the sql over and over to make a big huge mess (since, if you have to change the sql used, you now have to change 15 quires – or just leave them sitting there un-used (but, you can’t delete them either..since they might be used in the application)).

In fact, in a ms-access application, parameters in sql SHOULD NOT BE USED, or very rarely at best.

Other conversion issues also exists. Do note that expressions in JET sql can actually call VBA code. Further, expressions in JET sql code OFTEN USE VBA expressions (again, you can’t do that in sql server). So, there is really not a way to convert all the sql anyway due to it using VBA stuff (the expressions issue makes this hard). So, the solution is NOT to move the queries, but keep them in ms-access and ONLY modify the ones that run too slow.

Again, not know how often parameters, VBA expressions etc are used, then there are still some unknowns to how much work you got here. Also, as mentioned, perhaps that 600+ queries is the result of un-normalized data, or duplicated sql. If you can throw out 90% of the stuff during conversion, then you save big time, and start real clean (no need to spend money convering tons of sql that is not needed anymore).

So, perhaps some proper data normalizing, and cleaning out of the dead wood should be considered here BEFORE you move this to sql server (no use moving a mess from ms-access to sql server, all you accomplish is increasing the cost of maintaining the mess in a more expensive environment!!).

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Monday, July 11, 2005
Sorry, forgot to mention:
The front end is VB6.
Don Nobody
Monday, July 11, 2005
You might want to check out Access' "Upsizing Wizard" if you haven't already.  It's fairly limited in what it can convert, but it might do a decent job with your simple queries.  Here's some text from a MS document describing what the Upsizing Wizard tries to do when converting Access queries for use with SQL Server:

The Upsizing Wizard creates the Access project file, displays the Data Link Properties dialog box so that you can connect to an SQL Server database, and then upsizes all the database objects from the Access database to the Access project:

    * Queries  The Upsizing Wizard changes queries by converting them to either views or stored procedures and upsizing Access SQL syntax to use SQL Server SQL syntax. Select queries are converted to views. Sorted queries are converted to a combination of views and stored procedures to allow for nesting and sorting (Views can be nested but can't contain ORDER BY clauses; stored procedures can contain ORDER BY clauses but can't be nested). Parameterized queries, queries that depend on a parameterized query, and action queries are converted to stored procedures. You may need to manually convert queries that did not upsize (such as those that were nested too deeply). SQL Passthrough queries, data definition queries, and union queries are not upsized.
(from )
Herbert Sitz Send private email
Monday, July 11, 2005
==> 600 quires * 2 minutes per query to cut and paste and, perhaps some editing into the Enterprise manager = 1200 minutes, or 1200 minutes / 60 = 20 hours.  So, you are talking about 20-30 hours of work here (my starting guesstimate). This assumes a good fluency in JET sql, and also sql servers sql.

Normally, I highly respect your opinion, but on this one, I gotta say you're out of your mind if you think you can do this in less than 20 days let alone the 20 hours you propose. I'd give it closer to 20 *weeks* after you throw in testing. 600 queries have got to be hit by literally hundreds, if not thousands of screens/forms/reports/jobs/etc. By the time you test 'em all ('cause you just _know_ there's no automated testing in place already <grin>) you're looking at the impossible dream to complete in 20 hours.

Good luck on that. I'll check in by Friday. There's easily 20 or 30 hours between now and then. Let us know how it goes.
Sgt.Sausage Send private email
Tuesday, July 12, 2005
I recently went through this hellish process too.  Partly, it was hellish because we simlutaneously refactored - made all the queries conform to good naming, get rid of extras, etc.

There's got to ba a tool to help automate this process.  But I don't know what it is.  I only know what it isn't - "Access Upsizing Wizard".

Good luck,
Bankstrong Send private email
Tuesday, July 12, 2005
OP doesn't say, but as Albert K. alluded to, if you're going to be keeping the Access app as front end for SQL Server, you might want to consider using an ODBC connection from Access to SQL Server.  When you do this the ODBC drivers will automatically translate many of the JET SQL queries into Transact-SQL syntax.  Have to be careful, though, because in some cases JET will still try to handle parts of the query locally, i.e., load more data than necessary from the server and do part of query processing in the local JET engine.  If this sort of setup is possibility you should check MSDN docs for more info.
Herbert Sitz Send private email
Tuesday, July 12, 2005
Given that it's Access, it's likely that all 600 queries are actually the same query, only query #423 runs for #1/1/2004# - #1/31/2004# whereas query #541 pulls all employees LIKE "CLEM*OGER*" or like "BOGGS*WADE*" because they requested that report one time in the mid-nineties.

By writing a (more complicated) report that uses multiple optional parameters (probably by using a complicated "Report Dialog" form), you can reduce all the queries down to 1, so you still have hope!

But then again, all the comboboxes and form data sources possibly have SQL statements embedded into their RecordSource property.  Also: beware DLookup() and its aggregate nephews.
pds Send private email
Wednesday, July 13, 2005
Hi Don,

I encountered a similar situation last year. Except rather than a VB6 front we had an Excel front – all forms driven via an add-in.
Also we had some 130 users (not concurrent), with a 60 mb mdb file (and another 100 mb or so archived off). We had about 250 queries –and for pds’ sake – none had hard coded parameter values. I’d almost take that ‘Given that it's Access’ as being derogatory ;-)

The migration of the data was the simple bit. I couldn’t find any tools to automate the query migration. One difficulty is that many Access SQL statements don’t translate well (or at all) into T-SQL. For example, T-SQL doesn’t have an immediate translation for the TRANSFORM (cross tab query) statement, although there are ways to achieve the same result.

We settled for a compromise. All the data (including the archive) was migrated to SQL Server 2000.
The queries were retained in an Access database although we compiled it into an MDE file. This was then included in the install routine so it was installed on the user’s C: drive with the front end.

The only real change to the front end was in the connection string (redirected to the local drive rather than the file server). The performance gain was huge (recordsets returned in an average of 3 seconds against 20). It was also not possible for any user to corrupt or lock the database (if their machine crashed).

It also meant that we had ongoing control of maintaining the queries without having to put work requests through to an already over-loaded IT dept.

BTW, we also found performance gains using DAO over ADO when querying Jet directly. If you’re intending to query SQL Server, you’ll be using ADO.
Marcus from Melbourne
Monday, July 18, 2005
>gotta say you're out of your mind if you think you can do this in less than 20 days let alone the 20 hours you propose.

We are talking about moving the queries here, not any work in respect to the application part. At the time of my post, NO IDEAS were given in respect to the application side, and what is the front end being used (in fact, there was a possibility that NO front end application part existed). So, we are JUST talking about moving the queries, and running them. Gee, 30 hours is not a bad starting estimate for that task at all.

Without question, if those 600 quires are dispersed throughout a VB application that interacts with those queries, then yes, we got a problem here. That 20-30 hours is simply to move the quires to sql server, and run them. This seems like a very reasonable estimate if one has good fluency in both JET sql, and sql server sql. I see this as no problem at all.

Further, you can see that I also mention the possibility of LEAVING the quires in ms-access, and LINKING the tables to sql server.  You can make well note that another poster in thread confirms this as a reasonable approach (again, assuming this approach can be used).

So, hum…30 hours to move those quires…sure, I don’t see why not at all. Why would this take any more time?

However, issues of the application, and how it interacts with those queries is certainly a big deal, and I would agree with you this could be a very large task ahead, and especially so if my further advice cannot be used.

So, at the end of the day, you need someone with experience on both platforms who can make a judgment call as to how much work this is going to be. And, I would think it was quite obvious that my 30 hours is ONLY that of moving the sql.

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Monday, July 18, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz