  |
|
|
 |
|
MS-SQL Server - Upsizing
from Access 2000
If you have already designed your
database using Microsoft Access 2000, it is easy to upsize that
Access 2000 database into LaMarSOFT's SQL Server.
In this example, I have created a very simple Access 2000 database
using Access 2000 on my local PC. You can download this simple
Access DB by clicking here, but that is not
a required step.
Follow these 16 steps to create an ODBC Data Source on your local
Win9x/WinNT/Win2000 PC that connects to the LaMarSOFT SQL Server:
1) Open ODBC on your local PC that maps to the SQL Server at LaMarSOFT.
NOTE: To open the ODBC on your local PC, click Start, Settings,
Control Panel, ODBC Data Sources.
2) Select the System DSN tab.
3) Click the ADD button.
4) Select "SQL Server" and click the FINISH button.
5) Enter the correct information into the form and click the NEXT
button.
NOTE: This is the information you entered when you created the
ODBC connection in the Domain Control Panel.
6) click on the "Client Configuration" button.
7) Make sure the network library on your local PC is set up to use
TCP on port 1433. Click the OK button.
8) Select SQL Server Authentication.
9) Enter your Login and Password.
10) Click the NEXT button.
11) When the next screen appears, click NEXT. (Nothing to
change here)
12) When the next screen appears, click FINISH. (No changes here
either)
13) Click on the "Test Data Source" button.
14) It should run a test and say "TESTS COMPLETED
SUCCESSFULLY!"
15) Click OK to close test window.
16) Click OK to close summary window.
Congratulations! If you got this far, you have successfully
created an ODBC Data Source on your local PC that connects to the
SQL Server located at LaMarSOFT. The next step is to use that ODBC
link to upsize an Access database into SQL Server.
Follow these 12 steps to upsize an existing Access 2000 database
into the LaMarSOFT SQL Server:
1) Download the Access database from the LaMarSOFT server cluster
using FTP. You will need to have the Access database on your local
Windows PC in order to upsize it to the LaMarSOFT SQL Server. You
will also need to have Access 2000 (either standalone or as part
of Office 2000) installed on your local Windows PC.
2) Open the Access 2000 database that you want to upsize into SQL
Server. In this example, I am going to open the simple mydb.mdb
3) In Access, click on Tools, Database Tools, Upsizing Wizard.
4) Select "Use Existing Database" and click the NEXT
button.
5) Access will ask you to select a data source. Click the Machine
Data Source tab. Select the ODBC link that you created in the
above 16 step process. In my example, I create an ODBC link called
SQLDB101 on my local PC. Click the OK button.
6) You will be prompted for the SQL Login and Password. Enter the
Login and Password for the SQL database that you are upsizing
into. In this example, I am upsizing into a database called
SQLDB101. The login that can connect to SQLDB101 is also named
SQLDB101, so I use that as the login. Then I enter the password
that I entered when I created this SQL database using the web
control panel. Then click the OK button.
7) Select the tables that you want to upsize into SQL Server. The
double right arrow will select all of the tables. Click the NEXT
button.
8) The upsize wizard will show a menu that lets you choose which
object to upsize. In most cases, you will just want to click the
NEXT button.
9) The upsize wizard will show a menu that lets you select what
changes you wish to make to the existing Access database. No
application changes is selected by default. Since we are only
interested in migrating the tables, data, and indexes into SQL
Server so we can use SQL Server within our ASP and Cold Fusion
pages, we do not need to make any changes to the existing Access
database. Click the FINISH button.
10) The upsizing wizard will show you a report to let you know how
successful the wizard was in its attempt to upsize the information
in the Access 2000 database into LaMarSOFT's SQL Server. In the
mydb.mdb example, the report is just 2 simple pages. If you have a
lot of tables and information, the report may be considerably
larger. It is a good idea to read the report to make sure the
upsizing was successful. Some Access databases do not upsize
properly into SQL Server, so do not simply assume the report
contains good news.
11) At this point, you can change the DSN name in your ASP or Cold
Fusion pages to use the ODBC DSN of the SQL Database instead of
the ODBC DSN of the Access database.
12) Now check your database driven website to make sure everything
works as expected.
|
© 1996, 1997,1998,1999, 2000,
2001 LaMarSOFT.com. All rights reserved. All other trademarks are
the sole property of their respective owners. We only recommend
products that we have personally tried or use regularly. Performance
of any product or service is the sole responsibility of its
creator/owner. We assume no responsibility for advice, tips,
scripts, or programs provided here since we have no control over
their ultimate use or implementation. As always -- make a backup
first! Your privacy is important to us. Please read our Privacy
Statement for details. We may be contacted at webmaster@lamarsoft.com.
Last Updated:
Sunday February 10, 2002
|
|
|