MS-Access / Getting Started

---

IMAGE, VARBINARY(Max), and OLE Data

SQL Server supports two data types into which you can transfer Access OLE Data. These data types are, IMAGE (an older data type) and VARBINARY(MAX). The recommended choice is VARBINARY(MAX), but if you encounter any problems when migrating or working with the translated OLE Data, then it is worth trying the alternative data type mapping using the IMAGE data type.

Memo Data

SQL Server supports two data types into which Memo data can be transferred: TEXT, (which is the older of the two) and VARCHAR(MAX) (and their UNICODE equivalents).

In many respects the better choice is VARCHAR(MAX); if you intend to bind .NET controls such as a DetailsView control, then updates with TEXT are not supported, but this will work with the VARCHAR(MAX) data type.

Driver limitations with VARCHAR(MAX):
You should be aware that there is a serious problem when working with VARCHAR(MAX) and either the Native Client Driver or Native Client 10.0 Driver. If you try pasting in a large amount of text, you get the error [Microsoft][SQL Native Client] String Data, right truncation (#0). In this case, you either need to stick with the older SQL Server Driver or revert to using the TEXT data type, which works with all three drivers.

Currency

SQL Server supports two currency types, MONEY and SMALLMONEY. The MONEY data type maps to the Access Currency data type.

Attachments and Multi-Value Data

Unlike Access and Microsoft SharePoint, SQL Server does not support multi-value fields (Access and SharePoint provide full support for these), This means that you need to normalize your data by creating a new table to hold the multi-value or Attachment field data.

For example, if you have a table called tblCustomer with a primary key CustomerID and an Attachment field called Documents, you need to create a new table called tblCustomer Documents with an AutoNumber primary key, a foreign key to CustomerID, and an OLE Data field called Documents. You then need to create new records in the new table to hold the documents for a customer. Unfortunately, there is no easy way to transfer Attachment data to an OLE Data field.

One strategy to convert your Attachment data is to first use the SaveToFile VBA method of an Attachment object to scan through your attachments and save them as files to a folder. The next step is to create a form bound to your new OLEData Type, and then using the following VBA code for a control called for example TheOLEObject, you can load the file into the bound control and save the record:

    Me.TheOLEObject.SourceDoc = MyPath
    Me.TheOLEObject.Action = acOLECreateEmbed
    RunCommand acCmdSaveRecord

Required Fields

In Access, an existing field can be changed to a Required field, but you can skip checking existing data. These fields are created in SQL Server with a NOT NULL attribute; during the process of migrating data, this can cause problems. The following code can be used to check if you have NULL values in any required field. To fix this problem, you can either go through the data, adding a safe default value for the field, or set the Required field attribute to FALSE, as shown in the following:

Sub modUpsizeTests_CheckRequiredFieldsMissingValues()
' Search through all Required fields
' List any containing NULL values in the data
    Dim db As DAO.Database
    Dim tdef As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim strSQL As String
    Set db = CurrentDb
    For Each tdef In db.TableDefs
	If Left(tdef.Name, 4) <> "MSys" And _
	    Left(tdef.Name, 4) <> "USys" And _
	    Left(tdef.Name, 4) <> "~TMP" Then
	    For Each fld In tdef.Fields
	        If fld.Required Then
		    strSQL = "SELECT Count(*) FROM " & _
			    "[" & tdef.Name & "] WHERE " & _
			    "[" & fld.Name & "] IS NULL"
		    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
		    If rst(0) <> 0 Then
		        ' List where missing values
		        Debug.Print tdef.Name, fld.Name
		    End If
	        End If
	    Next
        End If
    Next
End Sub