migrating to SQL

[mikela]

Hello all!

I have an application in access and it is too small for me now, and I need to migrate it to SQL.

I've never done something like that before, so I have no clue where to start...

I figure out I need to export all the data from the access database to SQL database. How can I do it?? Any tool from microsoft to do it?

After that, my form is done by the wizard, and I would need to change the connection string to be to the SQL database, wouldn't I?? But I cannot find the code to change it? How can I do it? Do I need to create the form again?

 

[SQL_Hell]

Hi there,

What version of access do you have?
Never refer to SQL SERVER as 'SQL' because they are 2 different things and it tends to confuse the person answering your question, SQL is a language for querying databases, SQL SERVER is a database engine.

What version of SQL SERVER do you have?
How many users do you have on this access database?
What is your reasoning for using SQL server (apart from size constraints)?

There is an upsizing wizard in access that will convert your access tables to SQL server tables, but that is only the tip of the ice berg and in all honesty you have a steep learning curve ahead of you, I would suggest buying a book and reading up as much as you can.

A book like this would help you a great deal.

http://www.microsoft.com/mspress/books/4305.aspx

Good luck

 

[mikela]

Hey!

Thanks for your reply! I'm using Access 2003, and SQL Server 2005...
I read about this upsizing wizard... and it looks easy...

The reason for the migration is only the size of the database... It is needed more space to store all the data...

 

[tehNellie]

The upsizing wizard can be handy, but as SQL_Hell suggests it only moves your table structure out of Access and onto the SQL Server. It doesn't implement any of the good stuff that SQL server can do.

This can be handy from a development point of view as it allows you to continue to use your access application to drive the database while you implement a full SQL Server database toolset against your table structure such as the use of stored procedures, removing the application from the table structure, security etc etc.

 

[mikela]

Right...

It is a very small application (but too big for access for the amount of data right now), what do you suggest? To create everything new using SQL Server and .Net or Java application? And using the upsize meanwhile?

Any help will be much appreciated!
Thanks a lot!

 

[tehNellie]

In some respects it does depend on your requirements.

If it's a quick "Hack'n'Slash" application that only you use but you've just exceeded the limits of Access then maybe just chucking the tables onto SQL server will do what you want. You just need to be clear in your mind that the Upsizing Wizard does just that rather than a quick easy means to convert an Access Application to a "proper" SQL Server application.

At the very least you might also want to rewrite your access SQL code to use passthrough queries or call stored procedures so that you're at least letting SQL server do the work on your tables and only returning the data you want. There's no reason not to keep Access as a front end, I'm a great believer in not re-inventing the wheel if I don't have to

 

[mikela]

First of all Thanks a lot for your help!

Well, the problem is that the form has been created using the wizard, so I cannot find the way to change the connection string anywhere... And there is no code, so I cannot adapt the code to let sql server do all the job...

 

[tehNellie]

The wizard doesn't alter the forms, all it does it migrate and link the table structure. From Access' point of view the tables are basically still local.

If you want to drop the linked tables in favour of stored procedures for example, you'd need to re-write your existing application to do that instead of continue to work on what are essentially, from an Access point of view, local tables.

 

[MSAccessRookie]

Mikela,

I have spent the last 3 months doing exactly what you are about to do. Everything that they are saying is true, and the Upsize Wizard can move all of the tables and the data to the SQL Server for you. It can also move some of the queries into views for you as well. It does tend to complain if you use Non-Server-Standard naming conventions for Column names, table Names, or View Names, and you may want to think about making some changes in naming conventions.

In your case, I suggest starting (as has been described prior) with moving ONLY the tables. Once this process has been completed, determine whether all of your functionality is working the same way. This alone might gain you enough performance enhancement to give you the time for a full conversion to a new application using SQL Server and .Net or Java application, if that is what your end goal turns out to be.

If this does not get you enough performance enhancement, the next step would be to convert queries to Views where possible using the following rules as a guide:

o All View Data is Read Only and cannot be used in an update query.
o Most GROUP BY options are supported, and those that are not create errors.
o ORDER BY is not supported and is ignored.
o Input Based Queries like [Enter First Name] are not supported.
o Queries getting Input from Forms are not supported

If there is enough functionality that can be moved to the SQL Server, then the performance will improve greatly.

This is as far as we have needed to go so far.

Additional possible steps include

o Creating Pass-Through Queries to handle Parameter Input issues
o Creating T-SQL Functions and procedures to handle writing to the Database
o Creating additional Macros for other special needs

and of course...

o Conversion to a new application using SQL Server and .Net or Java application

 

[mrgreen]

Hey guys realize this is an old post but I have been doing this same thing as well. I'm strictly self taught so I'm 100% sure I've picked up bad habits. The front end is still in MS Access 2003 and I pulled in the tables using MSSQL 2005 (not upsizing wizard). What I've done is created an ADODB connection that stays open while the user has the file open. I also have linked the tables (MSSQL as the backend). I am sending information to MSSQL using the ADODB connection. Is this not a good practice? I am only playing around with it at this point but if I do decide to release this I don't want to have tons of problems. This forum continues to be one of the best.

 

[Banana]

Maybe this will help a bit. (or not)

A guide for managing ODBC connections


To answer your question more directly, ADODB is generally optimized for external data source, whereas DAO is great for Jet Engine, so there's nothing wrong with what you are doing.