The great majority of my Access memo fields were implemented because 255
characters was not quite sufficient.
So I documented the length of data in each of the memo fields with the
following bit of code and decided that I could use varchar() with values of
2000, 4000 and 8000 in most cases. Two fields needed more so I converted
those to Text. I'll upgrade the Text fields to varchar(max) when I convert
the application to A2007 or A2010.
I also updated any varchar(max) stored proceedure parameters and user
defined table type columns.
All SQL Server 2008 varchar and text fields now link correctly.
Public Sub DocumentMemos()
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intSize As Integer
For Each tdf In DBEngine(0)(0).TableDefs
If Left(tdf.Name, 3) = "tbl" Then
For Each fld In tdf.Fields
If fld.Type = dbMemo Then
intSize = 0
strSQL = "SELECT MAX(Len(" & fld.Name & ")) AS MaxLen
FROM " & tdf.Name
Set rst = DBEngine(0)(0).OpenRecordset(strSQL)
With rst
If Not (.BOF And .EOF) Then
intSize = Nz(.Fields(0))
End If
.Close
End With
Debug.Print tdf.Name, fld.Name, intSize
End If
Next
End If
Next
Set tdf = Nothing
Set fld = Nothing
End Sub
Post by Patrick JackmanI see that one solution is to change VarChar(MAX) fields in SQL Server 2008
to Text.
I also see that this is fixed in A2007.
If I change all my VarChar(MAX) fields to Text, must I also change all my
stored procedure parameters? I'm using Access passthrough queries and
ADODB.Command objects to get data into SQL Server.
What are the correct Parameter types for Text and VarChar(MAX) when using
ADODB.Command objects?
Any other suggestions?
Patrick.
Post by Patrick JackmanI'm uspsizing an A2002/2003 project to SQL Server 2008. Some of the
Access tables have memo field so I created these as varchar(max) in the
SQL tables.
The data appears to have imported fine into SQL Server.
However, the linked tables show the varchar(max) fields as Text 255 and
data is being truncated.
What is the workaround for this?
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC
604-874-5774