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.
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.
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
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
In this tutorial:
- Upsizing Access to SQL Server
- Planning for Upsizing
- Boolean Data
- IMAGE, VARBINARY(Max), and OLE Data
- Cycles and Multiple Cascade Paths
- Replicated Databases and Random Autonumbers
- Unique Index and Ignore Nulls
- Timestamps and Row Versioning
- Schemas and Synonyms
- Upsizing Wizard and the SQL Server Migration Assistant
- Upsizing to Use an Access Data Project
- Comparing Table Conversion in the Upsizing Wizard and SSMA
- Comparing Query Conversion in the Upsizing Wizard and SSMA
- Developing with Access and SQL Server
- Stored Procedures and Temporary Tables
- Handling Complex Queries
- Performance and Execution Plans
- SQL Server Profiler