The_Kurgan
Active Member
- Joined
- Jan 10, 2006
- Messages
- 270
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:
METHOD 2:
Any ideas would be greatly appreciated!
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:
Code:
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:
Code:
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!