Excel to Access: trouble with Memo data type

[The_Kurgan]

Hi all.

I am automating the inserting of data from Excel to an Access table. The problem that I'm running into is one field containing a large string (up to several sentences). The maximum number of characters is 500. Everything inserts (transfers) fine except for that one field which ends-up being empty in Access. I've tried switching the data type in the Access table to Memo, but still no dice. I can copy/paste the data with no issues... it's just something in the automation. I've tried two different methods with the same results:

METHOD 1:

Public Sub TRANStest2()
Dim cn As New ADODB.Connection
Dim stDB As String, stSQL As String, stProvider As String
Dim strSQL1 As String
stDB = "[URL="file://\\fimcfs001\CORP\Accounting-Shared\Reporting"]\\fimcfs001\CORP\Accounting-Shared\Reporting[/URL] Team\Reporting and Analytics Tool\Access\xxx.accdb"
stProvider = "Microsoft.ACE.OLEDB.12.0"
dbWb = Application.ActiveWorkbook.FullName
dsh = "[" & Application.ActiveSheet.Name & "$]"
'Opening connection to database
With cn
    .ConnectionString = stDB
    .Provider = stProvider
    .Open
End With
'Create the Recordset object
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
strSQL = "INSERT INTO Assigned_Data ([Done], [Suspense_Notes], [Branch], [Prod_Code)"
strSQL = strSQL & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
rs.Open strSQL, cn, adOpenStatic, adLockBatchOptimistic
'Close everything
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub

METHOD 2:

Set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase "[URL="file://\\fimcfs001\CORP\Accounting-Shared\Reporting"]\\fimcfs001\CORP\Accounting-Shared\Reporting[/URL] Team\Reporting and Analytics Tool\Access\xxx.accdb"
AccessApp.DoCmd.TransferSpreadsheet TransferType:=acImport, _
        SpreadSheetType:=acSpreadsheetTypeExcel12Xml, TableName:="Assigned_Data", Filename:=Application.ActiveWorkbook.FullName, HasFieldNames:=True, Range:="ERDB Data$A1:AJ" & cnt
AccessApp.CloseCurrentDatabase
AccessApp.Quit
Set AccessApp = Nothing

Any ideas would be greatly appreciated!

 

[The_Kurgan]

Bump

I'm also seeing that the list of ImportErrors shows "Type Conversion Failure" for each record. How can that be if I'm plugging into a Memo field?

 

[The_Kurgan]

Ok, never mind. I created a work-around.

I found that if I create a dummy record with a simple string in that field (something like "abc123") AND make that the first record to be imported, Access then accepts all subsequent records with that field fully in tact. I can then programmatically go in after the fact and delete the dummy record. It seems silly to have to jump through those hoops, but hey... it works.