I have a problem trying to copy some data from an Excel spreadsheet into a table in an Access database. I am using ADO to import the data, so that I do not have to open an instance of Excel itself.
The problem arises when there are more than 255 characters in a cell in the XLS. The code just ignores these records without generating an error. For instance, the recordset tells me it contains 3554 records, but it only ever imports 3552, excluding the 2 rows with the cells containing >255 characters. Putting a check in such as “If Len(fld.Name) > 255..” does not work because ADO never sees the record with the problem. Changing the data type to Memo does not solve the problem either – the records are still ignored.
Is there a way to get round this ? Here’s the code :
Set cnnXLS = New ADODB.Connection
With cnnXLS
.Provider = “Microsoft.Jet.OLEDB.4.0”
strConn = “Data Source=” & Me.XLSName & “;Extended Properties=””Excel 8.0;HDR=YES;IMEX=1″””
.ConnectionString = strConn
.Open
End With
‘
‘———————————————————–
‘The range selected to import is from the start row/col to
‘the last row/col allowed in Excel (IV65536). Excel will only
‘import the actual number of populated rows – it works this
‘out automatically
‘———————————————————–
strSQL = “SELECT * FROM [” & Me.WksName & “$”
strSQL = strSQL & Chr$(Me.StartCol + 64) & Me.StartRow & “:”
strSQL = strSQL & “IV65536]”
Set rstXLS = New ADODB.Recordset
rstXLS.Open strSQL, cnnXLS, adOpenStatic
‘
‘———————————————————–
‘Import the records into SQL Server using BatchUpdate
‘———————————————————–
‘
Set cnn = New ADODB.Connection
cnn.ConnectionString = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = Application.CurrentProject.Connection
.CommandType = adCmdText
.CommandText = “Delete FROM tblTempImport”
.Execute
End With
Set cmd = Nothing
strSQL = “SELECT * FROM tblTempImport WHERE (1=2)”
Set rst = New ADODB.Recordset
rst.Open strSQL, Application.CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdText
rstXLS.MoveFirst
intBatch = 250
intRecsCopied = 0
blnEnd = False
Do Until rstXLS.EOF Or blnEnd
intRecsCopied = intRecsCopied + 1
If intRecsCopied Mod intBatch = 0 Then
rst.UpdateBatch
rst.Close
rst.Open
End If
rst.AddNew
For Each fld In rstXLS.Fields
rst.Fields(fld.Name).Value = fld.Value
Next
rst.Update
rstXLS.MoveNext
Loop
‘rst.ActiveConnection = cnn
rst.UpdateBatch
rst.Close
Set rst.ActiveConnection = Nothing
varReturn = SysCmd(acSysCmdRemoveMeter)
‘cnn.Close
Set cnn = Nothing
‘
Grateful for any help
Bodders
