CONVERTING MS ACCESS 2000 TO SQL SERVER, WHERE TO BEGIN? 1

[cuok]

Hi Friends!

CONVERTING MS ACCESS 2000 TO SQL SERVER, from WHERE TO BEGIN?
I used in my program the dao 3.6 system.

thank in advance

CUOK

 

[mikeyb540]

Use the Upsizing Wizard

TOOLS - Database Utilities - Upsizing Wizard

 

[SleepingSand]

See Microsoft knowledge base article Q152032 for additional information. Moving to SQL Server will depend a bit on what version of Access you're using. With Access 95, download the upsizing wizard from the Access portion of the Microsoft
site (

http://www.microsoft.com/access,

or directly here) and use it to do the work for you. This is also true when using Access 2.0, you should use the Upsizing Wizard available for that environment.

Experience from one poster:
Performance will be slower than rewriting the application in VB or using stored procedures (actually, it may even be slower than straight Access and file server!) It may, however, let you support more users and achieve greater data integrity and simpler administration.

For a new SQL programmer, it is best to move one step at a time. Once the backend database is running smoothly, you can redo the front end to either use more passthrough TSQL statements or rewrite it in VB.

The real performance payoff of using SQL server comes only when transactional queries are moved into the server as stored procedures.

Some more caveats on the backend:
· Access cascade relationships are not mapped to SQL server, so deleting a master record in SQL does not delete the corresponding detail records.
· Use ODBC SQL driver 2.50 and not 2.65, or you will have difficulty deleting null records.
· Access dates and SQL dates have different origins. If you use date field for storing time, be prepared for some surprises when you transfer the data.
· SQL tables must have a unique index to be updatable. If your access tables are not strict relational, they cannot be upsized to SQL server.
· You may need to prevent user from searching on unindexed fields because that is extremely slow.
· Obviously Access naming that may include spaces are not allowed on SQL tables.
In general, an access application that was not designed with sql server in mind is much harder or impossible to upsize without rewriting it.
We did an upsizing project recently and that took three months, even though we know exactly what we were doing.
Additional Information:
Using Access 97, there is, as of this writing, no upsizing wizard available, though one is in the works according to Microsoft. No ETA on availability as of yet (1/26/97).

Books on the subject include:
· The Revolutionary Guide to Microsoft Access, and
· Special Edition Using SQL Server 6.5, Second Edition (make sure you get the second edition)
Note: to be fair, I (Steve Wynkoop) wrote these, there may be others. I do know the coverage is in these books. If you are interested in more info on these, they're located, along with more detailed information, on this site,

http://www.pobox.com/~swynk.

The alternative to either doing it manually from Access 97 or waiting for the wizard is to convert your tables to Access 95 format and use that environment, and its
associated upsizing tools, for the effort. Not elegant, but certainly an option.
There are some folks making tools out there, You should be sure to visit their web sites to check out their wares if this is something you'll be doing on any medium-tolarge
scale:
· Weir Performance Technologies produces upsizing tools and Access optimizer tools. More information on their web site. They also have some interesting conversion tools when moving between platforms. The tools know the differences between function syntax's across the platforms you're converting between.
· Aditi Technologies produces upsizing tools that will help you move your databases from Access to SQL Server. Visit their site for more information.

 

[cuok]

Thank you very much friends, I guess i'll came back to you!

CUOK

 

[tman24m]

I'd make sure you've got your relationships set up and cascading update/deletes are all correct. Using the upsizing wizard will allow you to change these into triggers so essentially it's the same thing as a cascade update/delete.