Friday, September 28, 2012

Recreate Access Tables in SQL Server

by Roger Carlson

Introduction

Microsoft Access is a terrific prototyping tool for SQL Server. But upsizing your database structure to a SQL Server database can be a problem. It would be nice if you could simply export the table structure from Access to SQL Server like you can between Access databases, but you can't. Fortunately, it's fairly easy to read the table structure of an Access table and from that build a SQL Create Table script to recreate the table in SQL Server.

Problems with the Access Upsize Wizard

Of course, Access comes with the Upsizing Wizard, which attempts to recreate your Access table in SQL Server. But the Upsizing Wizard has its problems.

First of all, the Upsize Wizard doesn't give you much flexibility. It chooses the SQL Server data types for your table. You don't have a choice. For instance, the wizard will create your text fields as nvarchar, but suppose you want them created as varchar? What if you want your Yes/No fields created as tinyint rather than bit?

And then there are SQL Server specific properties, like Padding and Clustered Indexes, over which you have no control. The Upsizing Wizard will apply a default padding value, which you cannot change. It will also always create your primary key as a clustered index, whether you want it or not.

Another problem with the Upsize Wizard is you must be connected to a SQL Server database for it to work. This is fine if you have a connection, but suppose you don't have rights to create tables? What if the SQL Server DBA asks you for a script to create them? What if you need to send the table definitions to a remote location to be created there?

Lastly, the Upsize Wizard will only work with SQL Server. The following process could be easily modified to create your tables in Oracle or another SQL-based database.

SQL Server's Enterprise Manager has a function that will save the structure of a table as a Create Table statement in a text file. Your DBA can then to run this script in the Query Analyzer to create the tables for you. It would be really useful if Access had a similar facility, but it does not. Fortunately, you can write one of your own.

How it works

The following code module accepts the name of a table in your Access database. It will then read the field names and data types and build a SQL Create Table statement, converting the Access data types to SQL Server equivalent data types. It then writes this SQL statement to a text file named after the table and having a .SQL extension.

Next, it reads all the Indexes in the table, determines the fields in the index and whether it is Unique or a Primary Key index, and finally builds Create Index statements to recreate these indexes.

Public Sub RecreateTableInSQLServer(TableName As String)
On Error GoTo Err_RecreateTableInSQLServer

The first thing is to create some object and scalar variables.

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim fld As DAO.Field, idx As DAO.Index
Dim strSQL As String
Dim indexfields As String
Dim indexunique As String
Dim path As String

Next, you need to initialize the object variables, opening a connection to the current database and to the particular table you want to recreate.

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(TableName)

You also want to find the path to the folder where the database currently resides, so the program will know where to store the text file.

path = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

If you want some other directory, you could hard code a folder path here.

path = "C:\My Documents\Access"

In Access, any field can be defined as part of a primary key as long as there aren't any null values in the field. In SQL Server, however, you must define the field as Not Null ahead of time. Therefore, you have to know which fields are in the primary key before you start defining the fields. To do that, you have to loop through the Indexes collection of the TableDef object. When you find the primary key index (as determined by the Primary property of the Index object), assign it to a string variable (indexfields).

For Each idx In tdf.Indexes
    If idx.Primary = True Then
    indexfields = idx.Fields
    End If
Next

The fields that make up the index are stored in the Fields property of the Index object, but they are stored in an odd format. For a multiple field index, they're stored in a string that looks something like this: +TextField;+LongIntField. Fortunately, you can read this string for the fieldnames with the InStr() function. You'll do this later in the routine.

Now that all the variables are created and initialized, and you know what fields are in the primary key, you can begin to build the SQL statement. Every Create Table statement always starts with the word CREATE TABLE and the name of the table. So you'll write that to a string variable (strSQL).

strSQL = "CREATE TABLE [" & TableName & "] ("

The brackets surrounding both the table name above and the field names below are necessary for table and field names that have spaces or special characters in them.

Next, the code needs to read through all the fields in the table, read the data types, and in some cases, determine some field attributes. It will add the field to the field list of the Create Table statement and depending on the data type, add the corresponding SQL Server data type.

For Each fld In tdf.Fields
  Select Case fld.Type
    Case 4 'Long Integer or Autonumber field
      If fld.Attributes = 17 Then
        strSQL = strSQL & "[" & fld.Name & "] INT IDENTITY"
      Else
        strSQL = strSQL & "[" & fld.Name & "] INT"
      End If
    Case 10 'Text field
       strSQL = strSQL & "[" & fld.Name & "] VARCHAR(" & fld.Size & ")"
    Case 12 'Memo field
      
strSQL = strSQL & "[" & fld.Name & "] NTEXT"
    Case 2 'Byte field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 3 'Integer field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 6 'Single-precision field
       strSQL = strSQL & "[" & fld.Name & "] REAL"
    Case 7 'Double-precision field
       strSQL = strSQL & "[" & fld.Name & "] FLOAT"
    Case 15 'ReplicationID field
       strSQL = strSQL & "[" & fld.Name & "] UNIQUEIDENTIFIER"
    Case 8 'Date/Time field
       strSQL = strSQL & "[" & fld.Name & "] DATETIME"
    Case 5 'Currency field
       strSQL = strSQL & "[" & fld.Name & "] MONEY"
    Case 1 'Yes/No field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 11 'OleObject field
       strSQL = strSQL & "[" & fld.Name & "] IMAGE"
  End Select

This is where you'll use the index string of the primary key. If the field is part of the primary key, add NOT NULL to the field data type, otherwise just append a comma.

  If InStr(indexfields, fld.Name) > 0 Then
    strSQL = strSQL & " NOT NULL, "
  Else
    strSQL = strSQL & ", "
  End If

And go to the next field.

Next fld

There will be an extra comma at the end the field list that needs to be removed. You also have to close off the Create Table statement with a closing paren and semicolon. (Technically, the semi-colon is unnecessary).

strSQL = Left(strSQL, Len(strSQL) - 2) & ");"

Now, you need to create a text file and write the string variable to that file. The easiest way to do this is through low-level IO. The following Open statement will create a text file with the name of the table preceded by the word "Create" and followed with a .sql extension in the folder where the database resides. So for table "Customers", the text file would be "CreateCustomers.sql". Then it will use the Print command to write the SQL statement to the file followed by a blank line.

Open path & "Create" & TableName & ".sql" For Output As #1
Print #1, strSQL
Print #1, ""

Next you need to recreate the indexes themselves, so you need to read the indexes again.

For Each idx In tdf.Indexes

As I said earlier, the index property returns a string like this: +TextField;+LongIntField. To make it useful for a SQL statement, you need to remove the pluses and convert the semicolons to commas. This you can do with the Replace function.

  indexfields = idx.Fields
  indexfields = Replace(indexfields, "+", "")
  indexfields = Replace(indexfields, ";", "], [")

In the first Replace function, you will replace all the pluses with the empty string, effectively deleting them. In the second Replace function, you are replacing each semicolon with "], [". The brackets are necessary for field names that have spaces in them. The first and last fields in the field list will not have brackets on the outer edge, but we'll add them when we put the whole SQL statement together.

You also need to determine whether the index is unique. If it is, the Unique property of the Index object will be true.

  If idx.Unique = True Then
    indexunique = " UNIQUE "
  Else
    indexunique = ""
  End If

You are almost ready to put the SQL Statement together. There is just one more property you need to consider whether the index is a primary key. In Access, you can create the Primary Key index in the Create Index statement. However, in SQL Server, you have to create it in the Create Table or Alter Table statement. I've chosen to do it in the Alter Table statement.

So if the index is a primary key index, use the Alter Table statement to create the primary key. Otherwise, build a Create Index statement.

  If idx.Primary = True Then
    strSQL = "ALTER TABLE [" & TableName & _
    "] ADD CONSTRAINT [PK_" & TableName & "_" & idx.Name & _
    "] PRIMARY KEY ([" & indexfields & "]);"
  Else
    strSQL = "CREATE " & indexunique & _
    " INDEX [IX_" & TableName & "_" & idx.Name & _
    "] On [" & TableName & "] ([" & indexfields & "]);"
  End If

Since your text file is still open, you can simply write the SQL string to it. Then loop for the next index.

  Print #1, strSQL
  Print #1, ""
Next

When you're done reading the indexes, close the text file and destroy the object variables.

Close #1
Set idx = Nothing
Set tdf = Nothing
Set dbs = Nothing

Add error trapping, and you're done.

Exit_RecreateTableInSQLServer:
  
Exit Sub
Err_RecreateTableInSQLServer:
  
MsgBox Err.Description
   Resume Exit_RecreateTableInSQLServer
End Sub

Implementing the Routine

Implementing this routine is relatively easy. Create a form with a listbox and a button. Fill the listbox (we'll call it lstTableList) with all the tables in your database by putting the following in the RowSource property of your listbox:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "msys*"
  And (MSysObjects.Name) Not Like "~*")
  AND ((MSysObjects.Type)=1))
  ORDER BY MSysObjects.Name;

Then call the routine from the button like this:

Call RecreateTableInSQLServer(Me.lstTableList)

Figure1

Figure 1: Form used to implement the RecreateTableInSQLServer routine

The resulting text file might look like this:

CREATE TABLE [tblAllDataTypes] ([AutoNumberField] INT IDENTITY NOT NULL,
[TextField] VARCHAR(50) NOT NULL, [MemoField] NTEXT, [LongIntField] INT, [ByteField] SMALLINT, [IntegerField] SMALLINT, [SingleField] REAL, [DoubleField] FLOAT, [ReplicationField] UNIQUEIDENTIFIER, [DateTimeField] DATETIME, [CurrencyField] MONEY, [YesNoField] SMALLINT, [OLEObjectField] IMAGE, [HyperLinkField] NTEXT);

CREATE INDEX [IX_tblAllDataTypes_Index1] On [tblAllDataTypes] ([TextField], [LongIntField]);

CREATE UNIQUE INDEX [IX_tblAllDataTypes_LongIntField] On [tblAllDataTypes] ([LongIntField]);

ALTER TABLE [tblAllDataTypes] ADD CONSTRAINT [PK_tblAllDataTypes_PrimaryKey] PRIMARY KEY ([AutoNumberField], [TextField]);

Considerations for Access Versions

This routine requires a reference set to the DAO (Data Access Objects) object model. This is not a problem in Access 97, 2003 or 2007, but in Access 2000 and 2002 (XP), you'll have to set this reference.

To do that, go to the VB editor (Alt+F11), and from the menu bar select Tools > References. In the References dialog box, scroll down the list until you find Microsoft DAO 3.6 Object Library. Check the box next to it and click OK to close the box.

Access 97 has an additional problem. The Replace function used above does not exist in Access 97, so you'll need to create one. Fortunately, it's relatively simple.

Function ReplaceCharacter(Target As String, SearchChar, ReplaceChar) As String

Dim i As Integer
Dim tempstring As String
  For i = 1 To Len(Target)
    If Mid(Target, i, 1) = SearchChar Then
      tempstring = tempstring & ReplaceChar
    Else
      tempstring = tempstring & Mid(Target, i, 1)
    End If
  Next i
ReplaceCharacter = tempstring
End Function

To use this function, simply replace these lines:

indexfields = Replace(indexfields, "+", "")
indexfields = Replace(indexfields, ";", "], [")

with these:

indexfields = ReplaceCharacter (indexfields, "+", "")
indexfields = ReplaceCharacter (indexfields, ";", "], [")

Conclusion

There are a variety of circumstances where you might want to recreate an Access table in other database environments. This routine demonstrates a fairly simple method to save the table structure complete with indexes into a SQL script file that can be run in the target environment to recreate the table.

Sample Database

You can find a sample which implements this here: http://www.rogersaccesslibrary.com/forum/topic222.html

Friday, September 7, 2012

New Sample: GetDistance function for Latitudes and Longitudes

By Crystal Long

The shortest distance between 2 points is a straight line.  Here is a function to do that.  It does not take curvature or routes into account.
GetDistance Function for VBA

Function GetDistance(pLat1 As Double, pLng1 As Double _
   , pLat2 As Double, pLng2 As Double _
   , Optional pWhich As Integer = 1 _
   ) As Double
'12-13-08, 12-22
   ' calculates distance between 2 points of Latitude and Longitude
   ' in Statute Miles, Kilometers, or Nautical Miles
   ' crystal strive4peac2012 at yahoo.com
   ' http://www.rogersaccesslibrary.com/forum/topic604_post622.html#622
  
   'PARAMETERS
   ' pLat1 is Latitude of the first point in decimal degrees
   ' pLng1 is Longitude of the first point in decimal degrees
   ' pLat2 is Latitude of the second point in decimal degrees
   ' pLng2 is Longitude of the second point in decimal degrees
  
   On Error Resume Next
   Dim EarthRadius As Double
  
   Select Case pWhich
   Case 2:
      EarthRadius = 6378.7
   Case 3:
      EarthRadius = 3437.74677
   Case Else
      EarthRadius = 3963
   End Select
  
   ' Radius of Earth:
   ' 1  3963.0 (statute miles)
   ' 2  6378.7 (kilometers)
   ' 3  3437.74677 (nautical miles)
   ' to convert degrees to radians, divide by 180/pi, which is 57.2958
   GetDistance = 0
  
   Dim X As Double
   
    X = (Sin(pLat1 / 57.2958) * Sin(pLat2 / 57.2958)) _
      + (Cos(pLat1 / 57.2958) * Cos(pLat2 / 57.2958) * Cos(pLng2 / 57.2958 - pLng1 / 57.2958))
     
   GetDistance = EarthRadius * Atn(Sqr(1 - X ^ 2) / X)
End Function
 
You can find this sample here: http://rogersaccesslibrary.com/forum/getdistance-function-for-latitudes-and-longitudes_topic604.html

New Sample: Form_SplitFormSimulatedMultiple

By AD Tejpal

    This sample db demonstrates simulated multiple split forms on tab control pages. The split form has two adjacent subforms, one below the other, separated by a divider bar. Top subform serves as single form while the bottom one represents datasheet portion. There is two way synchronization between single form and datasheet. Comparative vertical space available for the two subforms can be adjusted at run time by dragging the divider bar up or down. Single form portion functions as the prime mover.

    Some of the salient features are listed below:
    1 - Ease of adaptation:

        The developer wishing to use this sample db for his own needs has to simply assign his single form as source object for the top subform in the main form included here. Everything else will get taken care of automatically. Based upon generic template, datasheet portion of split form gets generated programmatically in the bottom subform, displaying columns matching bound controls in the single form above. It is like a plug & play feature.

    2 - Consolidation of code in a wrapper class:

        Necessary code for integrated coordination between main form as well as its two subforms is consolidated in a wrapper class, instantiated in open event of main form. This class has pointers to both the subforms as well as the main form.

    3 - No added burden for data loading:

        Datasheet subform uses the recordset already loaded for single form, thus avoiding any additional burden.

    4 - Divider bar:

        (a) Divider bar can be dragged for dynamic expansion / shrinkage of datasheet and single form heights at run time.

        (b) On opening the split form, divider bar assumes the position last held in previous session.

    5 - Re-sizing of nominated controls on single form:

        For added convenience, certain controls on single form, e.g. text box bound to memo field or even an image control, can be slated for vertical re-sizing so as to best utilize the available space resulting from divider bar movement. Tag property of such controls should include the word ReSize. In the sample db, such a behavior is demonstrated for control named  Synopsis, bound to memo field.

    6 - Hiding / Un-hiding of datasheet columns:

        On opening, datasheet columns matching memo fields are in hidden state. The user can hide any other column by double clicking the same. Similarly, any column can be un-hidden by double clicking the matching bound control on single form.

    7 - Auto adjustment of datasheet column widths:

        (a) At any given stage, the width of displayed columns gets adjusted automatically, so as to ensure optimum utilization of available width of datasheet window, duly taking into account the latest status of hidden / un-hidden columns.

        (b) For a given single form, on opening for the very first time, datasheet columns assume equal width, suiting available space. Thereafter, if the user has manually adjusted the column widths, any further automated adjustment of column widths in response to hiding / un-hiding of columns is carried out in such a manner as to retain relative proportion of column widths.

        (c) On click of a command button, the user has the option to reset all column widths equally distributed, as if it were the first opening of form.

    8 - Highlights:

        (a) When a datasheet column is in hidden state, corresponding control in single form gets highlighted in light grey. As and when the column is un-hidden, matching control on single form reverts to its normal color.

        (b) Current row in datasheet gets highlighted in light green.

        (c) As the user tabs from one control to the other on single form, matching cell on current row of datasheet gets highlighted in distinct color (say light maroon).
        Note: Flicker effect on datasheet, due to conditional formatting, seems to be more pronounced in Access 2010 as compared to Access 2003.

    9 - Search Action - Positioning of destination row in datasheet window:

        Based upon search action performed via suitable controls (like combo box etc) on the single form, the destination row on datasheet gets positioned at middle of display window. This takes into account dynamic height of datasheet window, resulting from movement of divider bar. In Access 2010, use of search box on navigation bar, too, results in similar positioning of destination row at middle of datasheet window. 

Wrapper Class Usage:
    Integrated wrapper class C_SplitForm is used in VBA module of main form F_Main as follows:
    (1) Following statement is in F_Main's declarations section:
         Private mfm As C_SplitForm

    (2) Following code is in F_Main's open event:
        Set mfm = New C_SplitForm
        mfm.P_Init Me, Me.SF_A, Me.SF_B, Me.LbDiv, "T_ZZZRef"
Note:
    (a) Subform control at top, i.e. SF_A holds the single form while SF_B holds the datasheet form. Single form is the prime mover.
    (b) Reference table named T_ZZZRef holds divider bar position and columns widths from last session.
    (c) Label LbDiv serves as the divider bar. It can be dragged up & down as desired, for adjusting relative heights of single form & datasheet portions at run time.
    (d) Setting of form object to Nothing is carried out in close event of mfmMain in wrapper class C_SplitForm, so as to ensure proper termination of the class.
    (Care should be exercised Not To set mfm to Nothing in real form's module, for example in its close event, as that would interfere with smooth termination of wrapper class. - Also see remarks in close event of mfmMain in this class).
    (e) Class C_SplitFormControls is a child class of wrapper class C_SplitForm.
    (f) Generic datasheet form used in bottom subform control (SF_B) has no code in its VBA module. It has 200 unbound text boxes along with corresponding attached labels so as to cater up to 200 columns.

Version: Access 2000 file format.

You can find the sample here: http://rogersaccesslibrary.com/forum/Form-splitFormsimulatedmultiple_topic605.html