Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Star Lounger
    Join Date
    Jan 2010
    Location
    Oregon
    Posts
    61
    Thanks
    9
    Thanked 0 Times in 0 Posts
    This piqued my interest and I did some looking. Wow, this is a bugger of a problem, especially if you wish to avoid an instance of Excel. Not sure if there is a solution for ADO.

    However, take a look at this article, specifically the third reply dated 9/12/2007. May have some useful info.

    Good luck. I know this has been a long-standing issue.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    An additional parameter here is the version of Office you are using - the ability to deal with long strings (>255) has varied from version to version. Also note that the article Reid pointed you to is using SQL Server directly - and that raises a question. Your code indicates the records are being written to SQL Server - you might be able to create an ODBC connection to the SQL Server table if that is really the case, and use ADO just on the Excel document. I'm not optomistic that will solve your problem however - I expect your issue is with the ADODB driver for Excel.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wendell & Reid - thank you both for your replies.

    The answer was to use ODBC instead of OLEDB, as below :

    '
    '-----------------------------------------------------------
    'This is the OLE DB driver
    '-----------------------------------------------------------
    '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
    '
    '-----------------------------------------------------------
    'This is the ODBC driver
    '-----------------------------------------------------------
    With cnnXLS
    .Provider = "MSDASQL"
    strConn = "Data Source=" & Me.XLSName & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
    .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DBQ=" & Me.XLSName & "; ReadOnly=False;"
    .Open
    End With

    Saved me from a lot of heartache

    Thanks again guys

    Bodders

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I got ahead of myself when I said that using the ODBC driver actually solved the problem.

    Instead of just ignoring the invalid rows where a cell had >255 characters, it now returns this error on the rstXLS.MoveNext statement :

    [Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

    What I really wanted to be able to do was to have some code that would check the field length, and then only move the first 255 characters into the database field - but the code will never reach this point as it will error on the MoveNext statement.

    Any more thoughts on this ?

    Bodders

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Have you tried linking to the Excel worksheet from Access? You would want to make sure you had a row with >255 characters in the first 10 rows or so of the worksheet. Also, what version of Access are you using? I believe some issues with the Excel import function have been corrected in SP2 of Office 2007. Finally, have you tried the TransferSpreadsheet function?
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wendell

    Thanks for your reply.

    I am using Office 2003 SP3 for Access and Excel. The code occurs in a class module which I am building to provide a generic function to import an Excel worksheet, or a range on a worksheet, into an Access database. TransferSpreadsheet is too limiting for this, eg it overwrites existing data in the table whether you want it to or not.

    I had not thought of temporarily linking to the spreadsheet, but I will give this a try. I cannot guarantee whether any cells have >255 chars, nor in which row they would appear if they did.

    Bodders

  8. #8
    Star Lounger
    Join Date
    Jan 2010
    Location
    Oregon
    Posts
    61
    Thanks
    9
    Thanked 0 Times in 0 Posts
    If TransferSpreadsheet works, then do so to a temporary table, then process that info. Personally, I would prefer this so I'm dealing with Access objects, eliminating any Excel issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •