|
Upsizing to SQL Server
In order to get the NetHelpDesk database tables into SQL Server, you
simply upsize the MS Access database tables to SQL server using the MS
Access upsizing wizard. This caters for all versions of SQL server and
allows you to upsize from shared file systems at anytime.
Another option is to use the SQL Server Enterprise Manager to restore the
empty or example databases from the SQL Server 7 backup file image that is
included on the installation CD. This requires a little more SQL server
knowledge.
There are various combinations of SQL Server and MS Access versions that
you may own. Examples of 2 combinations are shown below. Please feel free to
contact us for assistance with the process.
Remember to schedule a backup cycle for the SQL Server database.
Enterprise Manager has tools for this. Check that the backup is working.
Combination One: SQL Server 7 and Access 2000
A. To run the upsizing wizard, first go to a computer with MS Access and
the wizard installed. Access 2000 includes the upsizing wizard as an
optional part of its installation.
B. Use Access to open the MS Access database you wish to upsize. For
example, the NetHDexample.mdb file. Select the menu option Tools, Database
Utilities, Upsizing wizard.
C. The upsizing wizard will then ask if you want to create a new SQL
database or use an existing one. Unless your SQL Server administrator has
pre-created a database for you, select to create a new one now.
D. The wizard will then ask which NT server the SQL database is to be
stored on and what name you want to give it. e.g. NTserver1 and
NetHDexample. You will also need to have a valid SQL user name that has
create table permissions in SQL server. Users and permissions are created
using SQL Server Enterprise Manager.
E. Once you have filled in those details the wizard presents you with a
list of tables that can be upsized. Choose all of them.
F. On the next screen you are asked if you want to have timestamps on the
records - choose no. Also you are asked if you want to link to the SQL
tables from Access - choose no for this too. (In Access 2000 this question
is asked on the next wizard screen.)
G. Finally click the finish button and the wizard will start the process.
It will create the tables, copy the data and create indexes. If you need to
repeat the process, you may wish to delete the tables using SQL Server
Enterprise Manager first.
H. Once complete, a report is produced and you can then use SQL Server
Enterprise Manager to verify that the tables have been created in SQL Server
successfully. You can also check that the SQL user you intend to use for
database access has the correct permissions to the SQL tables.
I. Now create a new .udl file or copy one of the existing .udl files and
adjust it to point to the newly created SQL Server database. It must use the
SQL server OLE DB driver that is selected on the first tab of the udl file
editor.
J. When you click on nethdclient.exe (with no command line parameters),
it will list all of the .udl files in the current directory. You can choose
the newly created SQL Server .udl file. Alternatively you can make the .udl
filename the first command line parameter of a shortcut to nethdclient.exe
in order to open it automatically.
Combination Two: SQL server 6.5 and Access '97
A. Create the SQL Server Database
Create the required SQL Server database using SQL Server Enterprise
Manager. The following steps accomplish the creation of an SQL database
called nethd. Your organization may have its own standards with regard to
database naming and creation.
1. Login to SQL Server as user sa. 2. Create a new database device called
nethd size20mb. 3. Create a new database called nethd using the device
nethd. 4. Create a user called nethd and give it access to the nethd
database - permit all 5. Edit the database and choose options - truncate on
checkpoint and give user nethd all permissions.
The database is now ready to accept the upsized tables.
B. Create ODBC DSN for the SQL Server DB.
On the PC that will be doing the upsizing from the MS Access databases,
create an ODBC DSN that points to the newly created nethd SQL Server
database. This is done in Control Panel using ODBC32 Manager. We suggest you
call the ODBC DSN nethd and login in as the nethd user. This is only a
temporary requirement for the upsizing process, as ADO/OLE DB connection
strings are used subsequently to access the SQL Server database, not ODBC.
C. Run the Upsize Program
Access 97 has an add-on product called the SQL upsizing wizard. This can
be obtained for free from the Microsoft web site and a version is included
on the installation CD.
The MS Access upsizing wizard asks for the ODBC DSN of the pre-existing
SQL server database to which the Access tables will be upsized. It will
prompt you to login to SQL server using the nethd username and password.
Choose all tables and click the upsize button. Once complete, check that SQL
Server now has the database tables that you upsized.
D. Edit the .udl file
Now create or copy and edit the sample .udl file. This specifies the SQL
Server name, username and password that are used to connect to SQL Server
database. There is a sample .udl file called SQLserv1.udl in the NetHelpDesk
programs folder.
E. Run NetHelpDesk
Finally run Nethelpdesk.exe from the program files location and choose
the .udl file that you have just edited. You can make this filename a
command line parameter to Nethelpdesk.exe so that it does not prompt you
each time you run it.
For support see the section entitled: Technical Support Contacts
|
