Copy an Access Table into a new MsSql database. Sql commands will be issued to create the database. This tutorial uses the C:\data\ as a working folder.
Preparation:
This tutorial uses guild.OL built from 1) Access Tables and Indexes . You can also begin by saving this file to C:\Data\:
| guild.OL |
This tutorial uses MsSql 2000 and assumes that you have Admin rights. It should work with any version of MsSql that has SqlOleDb support, including MsSql Express .
1) New Database
Pick the OleDb Tab in the Data window. From the Provider context menu pick Expand. Pick SQLOLEDB Enumerator... and Expand it.
Select your server and pick Initialize from the context menu.
If nothing appears in SQLOLEDB Enumerator: Open SQLOLEDB - Microsoft OLE DB Provider... and initialize Source - SQLOLEDB instead.
Once the data source is initialized, Sql Execute becomes available from the Source's Admin context menu.
Pick Admin/Sql Execute from the context menu and type CREATE DATABASE Offline into the Sql dialog. [OK] to close the dialog and execute.
If there were any errors running the Sql, information would appear in the Results window. For example, repeating the command would produce:
Sql: CREATE DATABASE Offline.
<?MSSQLError HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL Server" Description="Database 'Offline' already exists."?>
in the Results window.
Pick Uninitialize from the context menu of Source - Master. Pick Properties from the context menu and choose Offline. Pick [OK] to close the dialog.
Pick Initialize, and notice that the Source item has changed from Source - master to Source - Offline.
Pick Connection/UDL Save from the context menu of the source and save C:\Data\Offline.udl. Pick Close from the context menu.
2) New Table
This tutorial section assumes that a database Offline is available on an Sql server to which you have table creation rights.
PickFile/Open and choose C:\Data\grid.OL. Pick File/Data Link and notice that the connection string is an Access connections string similar to
All values other than Provider=Microsoft.Jet.OLEDB.4.0 and Data Source="C:\Data\Offline.mdb" are defaults. They may be missing, or different. No matter, we are going to replace the connection string.
Press [Change] and select Microsoft OLE DB Provider for SQL Server. Pick [Next].
Select your server name. Select the database Offline. Press [OK]. The connection string should now appear similar to:
Pick File/Save As and save C:\Data\guildsql.dgf. The only difference between guild.OL and guildsql.OL is the connection string.
Pick File/Write Data. Note the Could not CreateTable error message in the status bar. Pick the Results window and notice that the (Date) column data type is not supported:
Creating table "Guild" with 5 columns.
ITableDefinition: Microsoft OLE DB Provider for SQL Server Type is invalid.
Could not CreateTable.
Column "LastSeen" type (DATE) not supported by Provider.
Write Data failed.
Access supports DATE - OLE Date and Time. MsSql does not. Pick Grid/Column/Properties and change data type DATE to DBTIMESTAMP.
Pick File/Write Data. The table Guild, including the PrimaryKey index, is created.
Pick File/Write Data again. Notice in the Results window that 10 of 10 rows had changes:
Key columns (PrimaryKey): nKey.
Updating 10 rows.
Update complete (10 of 10 rows had changes).
Appending 0 rows.
Table Write
The 10 updates are unnecessary, because the same dates are being written. Pick File/Reload Data/Reload Update. Note that column LastSeen changed format.
Pick File/Write Data again. Notice in the Results window that 0 of 10 rows had changes.
Pick File/Save to save guildsql.OL. Pick File/Close.
3) Edit Table
Select Providers in the OleDb tab of the Data window. Pick Connection/UDL Load and choose C:\Data\Offline.udl. Pick Initialize from the Source - Offline context menu.
Pick Admin/Edit Columns to bring up a Column Properties dialog. Pick Column LastSeen and choose smalldatetime from the Provider drop-down list. [OK]. Press [OK] in the warning dialog to recreate the column.
Notice that there are two provider types with a DBTIMESTAMP data type, datetime and smalldatetime.
Pick File/Write Data to repopulate the LastSeen column.
Pick Grid/Column/Properties and select LastSeen. Notice that there is no provider type because the grid is unaware of the smalldatetime change made to the column in guildsql.OL.
Type smalldatetime into Provider. Press [OK]. Pick File/Save. Note that there is no drop-down because provider types are available only from an initialized data source.
Comment on Provider types: It is not required that smalldatetime be updated in the guildsql.OL. Since DBTIMESTAMP is used for both datetime and smalldatetime, there is little difference as far as the grid is concerned. However, if the table were to be dropped and recreated, guildsql.OL would then use the Provider name to recreate the column.
Complete. Additional tutorials.
