Converting from Access to SQL

[libby]

I need some tips or reference material to convert my current access databases to SQL 7.0. <br>
<br>
Thanks

 

[calahans]

Hi Libby,<br>
<br>
There are a few things you should take note of:<br>
<br>
Why are you scaling to SQL Server, its a big job if you don't have a real need. If you need improved performance or more scalability, then its the right way to go.<br>
<br>
If you are using access forms to read/write data then if you scale to SQL Server you will have to link your tables from SQL Server into access.<br>
<br>
You are probably using the DAO Jet database engine to access your data currently, if you go to the SQL Server setup, I would advice a migration to ADO for data access.<br>
<br>
Your SQL and VB code should work okay with just a few tweaks. Although perhaps someone else can come back on this. I program with VB, but any time I have to look at access code it's the exact same (almost).<br>
<br>
Most SQL Server books have a section on scaling to SQL Server from access, if I can think of one I'll post it up.<br>
<br>
HTH<br>
<br>
C

 

[Guest_imported]

The truth is, its a very good move. But as you do it, I would first upgrade to Access 2000. Access 2000 is engineered to work directly with SQL Server 7 as its data store.<br>
<br>
When working with an Access project file (ADP file), Access provides a variety of tools that you can use to work with SQL Server 7.0 and 6.5 databases. These tools allow you to work with existing databases, create entirely new databases, and to work with the design of database objects. Access also provides the Upsizing Wizard, which helps you convert an existing Access database into an Access project file connected to a SQL Server database.<br>
<br>
By isolating all database files under the control of a database server, SQL Server can provide advanced features that cannot be furnished by the Jet database engine. For example:<br>
<br>
Online backup Use an automatic scheduler to back up your database without having to exclude users from the database.<br>
<br>
Durable transactions SQL Server logs transactions so that updates made within a transaction can always be recovered or rolled back if either the client or the server computer fails.<br>
<br>
Better reliability and data protection If either a workstation or file server fails while an Access database (MDB file) is being written to, the database might be damaged. You can usually recover a damaged database by compacting and repairing the database, but you must have all users close the database before doing so. This rarely happens with a Microsoft SQL Server database.<br>
<br>
Faster query processing Because an Access database is a file-server system, it must load the Jet database engine locally to process queries on the client workstation. For large databases, this can involve moving a lot of data over the network. In contrast, SQL Server runs queries on the server, which is typically a much more powerful computer than client workstations. Running queries on the server increases the load on the server, but this can reduce the network traffic substantially — especially if the database application is designed so that users can select only a small subset of the data at a time.<br>
<br>
Advanced hardware support Uninterruptible power supplies, hot-swappable disk drives, and multiple processors can all be added to the server with no changes to the client workstations.<br>
<br>
<br>
<br>
see also <br>

http://www.microsoft.com/Office/enterprise/prodinfo/msdtaeng.htm

<br>
for more information