Converting from Access to SQL

Topic

New Reply

I need to convert an Access 2003 database into a SQL database for a large/complex application. Is the ‘Upsizing Wizard’
the way to go or would you recommend a vendor purchased sql conversion application? Does SQL offer some type of
‘Access Conversion Wizard’ also? Which is the best way to go?

Also, once the application is converted and all of the conversion errors have been cleaned up, would the resulting SQL application
be a VB6, a .NET or a C++ application? Thanks very much…..Ray

Reply | Quote

Replies

Welcome to the Windows Secrets Lounge!

The upsizing Wizard in 2003 works reasonably well – 2007 works even better. But in the long run you will want to do some tweaking of what gets upsized. I would suggest if you are an experienced Access developer that you install the Express version of SQL Server as a test environment – it’s a free download from the Microsoft website – 2008 is the current version. After you have it installed, run the upsizing wizard and tell it you want to link to the tables in your Access database. Once you have done that there is no need to create a new front-end application if your Access design has served your users well. If you would like further advice, I’m sure one of us Loungers can help – several of us frequently used Access as a front-end to SQL Server applications.

Reply | Quote

I need to convert an Access 2003 database into a SQL database for a large/complex application. Is the ‘Upsizing Wizard’
the way to go or would you recommend a vendor purchased sql conversion application? Does SQL offer some type of
‘Access Conversion Wizard’ also? Which is the best way to go?

Also, once the application is converted and all of the conversion errors have been cleaned up, would the resulting SQL application
be a VB6, a .NET or a C++ application? Thanks very much…..Ray

Theoretically, after you convert the backend database to SQL server, you can just link to your existing frontend without changes. You probably won’t see much of a performance advantage (if any); but it is a starting point; and you have a functional database at a relatively low cost.

From there, you can start exploring the use of Pass-through queries and Stored Procedures, especially for reporting, processing, as rowsources of comboboxes and listboxes, etc. SQL syntax is different in SQL server vs Access, but you’ll get the hang of it. Basically, as you notice a process or maybe a report that seems to take too long, you try to convert it (I primarily use pass-throughs).

Reply | Quote

Thanks so very much for your quick responses. My ‘customer’ is insisting on no Access whatsoever, so it appears that the Access 2007
(or possibly Access 2010?) Upsizing Wizard is the way to go with a lot of recoding DoCmd.RunQuery statements as pass-through queries.

Question #2 still remains however…..what will I have when I’m done; VB6, .NET, or C++, or something else? Thanks again, Ray

Reply | Quote

What does he mean by “..no Access what-so-ever”? Does that mean you can’t use Access as the frontend? Then you are going to have to develop the frontend in some other language; and why is that unknown language better than Access? If he is OK with tossing out all prior development work (and his investment) and starting all over from scratch, then you’ll make some money. Good luck.

Reply | Quote

…
Question #2 still remains however…..what will I have when I’m done; VB6, .NET, or C++, or something else? Thanks again, Ray

I think Larry has raised an excellent question:

I’m curious what is meant by “large/complex”. If you don’t mind, what size of database is being converted?

It would help us to understand size issues and the complexity if you could give us an approximate size of the backend and front-end databases – if it’s not split, then that should be your first step. In addition it would be useful to know the maximum number of rows in your largest table, the number of tables, and the number of forms, reports and modules in the front-end. Finally, how many users actively do data entry, and how many access the data on a regular basis, and how many connect remotely?

As an example of the kind of projects we do, we have an Access front-end that is about 60 MBytes, connected to a SQL Server back-end that is about 1.5 GBytes. This database is tracking people, their addresses, and their accomplishments. There are about 500K people records, with some 4 million accomplishment records (the largest table), and the SQL Server back-end contains 128 tables, 89 views, 27 stored procedures and 2 user defined functions. It is also replicated to a web server – more about that in a bit. The Access front-end has 19 local tables used for menu functions, 353 queries, 88 main forms, 78 subforms, 171 reports, 52 subreports and 19 modules. There are about a dozen users, 4 of which do most of the record updating. However there are some 700 external users who can view data and submit updates on selected individuals via a web interface to the replica on the web server. This application has been evolving, as most do, for about 6 years. I consider this to be a fairly complex application, although not a very or extremely complex one. So the issue of what is large or complex is a relative term that means one thing to one developer, and something else to another.

Finally, if your “customer” insists on using a different front-end, and you end up developing it, you will find there is a significant learning curve with any of the other user interface options that are available. I have personally been involved in two “development disaster” projects where clients insisted on replacing an Access application with other technology. Both took 3 years to develop and never worked as advertised. One cost the client over $3 million dollars, and was essentially catestrophic.

Reply | Quote

I second what Mark said. There as absolutely no reason to throw the baby out with the wash. Ask for details on why the thinks Access is a bad idea as a front-end. He does have to pay a licensing fee for it, but if he has Office then the incremental cost isn’t that much. And if that’s an issue, you can always do a runtime version. But where your client will really get hammered is the development cost and the stability. We conservatively estimate it takes between 1/4 and 1/3 of the time to develop a project in Access than it does to use a language like VB or C#, and it will invariably be more stable and less buggy – and ongoing maintenance will be less.

There are some tools that you can use to develop applications using .NET, but then your user app is limited to what can be done in a web browser, and you have all sorts if issues in that environment. If he absolutely insists that Access not be used as a front-end, then I would tell him to go buy a commercial product. Some systems can be customized (at a cost) but it depends on the industry and the application functions.

Reply | Quote

I need to convert an Access 2003 database into a SQL database for a large/complex application.

I’m curious what is meant by “large/complex”. If you don’t mind, what size of database is being converted?

Reply | Quote

Is your project currently split into a front end and back end? ie is the data in one mdb and all your forms, reports etc in another with attachments to the tables in the former?

If not then your first step is to remove the data from your database into either a separate MDB or a SQL Server Database. Access provides wizards to accomplish either.

If it is separate then you can also use the wizards to move the data into a SQL Server database. Using either the wizard or third party applications will create some rubbish in your SQL database – contraints and stuff so it is better to hand craft the tables and relationships in the database then use Access append queries to move the data. more detail later.

Your aim is –

to have SQL Server installed (you can start with SQL Server Express 2008 which is free to download and install).

to create a System DSN on each machine pointed to this database.If you need guidance then ask and I can upload full instructions.

attach the tables to your front end MDB application. As you would normally attach tables (File>Get External Data>Link Tables) then select Files of Type – ODBC Databases, then on the tab ‘Machinde Data Souce select your DSN. Select the tables you want and click OK. Then you may need to rename the attachments to remove the ‘dbo’ prefix so they have the same name as the original tables.If you created the tables by hand then you can make append queries to move the data from the old tables into the new dbo… tables then delete the old table, then rename the new table without the dbo prefix

At this point you application is back up and running with no changes to code, forms or reports required. If you used the wizard then you can accomplish this in an hour or two.

If you want to then rewrite your entire application in another language then you can attack that later although sanity should prevail and you should realise that this just isn’t a good idea. If this is a complex application then you’d be wasting months of work for no gain.

An Access application with a SQL server database behind it is robust and fast (if you use the pass through queries and stored procedures Mark Spoke about) so unless you want to move your application to the web then converting the frontend is not going to achieve anything apart from billable hours.

If you do want to go to the web then you can write a new frontend using Visual Web Developer, or whatever, linked to the same database.

Reply | Quote

..
to create a System DSN on each machine pointed to this database.If you need guidance then ask and I can upload full instructions.

kentg –
I’d be interested in getting instructions on this, if that’s still possible. Thank you.

Reply | Quote

kentg –
I’d be interested in getting instructions on this, if that’s still possible. Thank you.

The process is fairly simple – you go to Administrative Tools in Control Panel, and choose Data Sources (ODBC) and choose the System DSN tab, click ADD, and then specify the SQL Server driver you wish to use – we recommend the Native Client 10 driver. You will then be prompted for a name for your data source and the server that the database resides on. From there on you simply follow the Next until you get to the Test Connection dialog, and if you’ve done it correctly you should be able to connect to that database. Then the next step is to link to the tables in the SQL Server database – Access will ask you for the data source and you specify ODBC.

Reply | Quote

Hi Ray,

I know it’s been half a year since you started this thread. I haven’t been in the lounge for quite some time. I am just curious how your conversion of the MS Access database to SQL Server project has progressed.

I only ask because for the last several months I have been upgrading my own framework for creating Native Windows (C++) front-ends for SQL Server database applications. Having converted my own clients from MS Access to SQL Server on more than one occasion, I was curious about the approach you have taken and, of course, to find out if you still needed any help.

Charles.

Reply | Quote

Easy enough – Thank you, WendellB!

Reply | Quote