Access 2003 versus Access 2007 Excel Import

Discussion in 'Access VBA Modules' started by clk, Aug 7, 2012.

  1. clk

    clk Guest

    Hi. I have a database that takes an Excel spreadsheet from an outside
    vendor (have no control on the spreadsheet format) and imports it into
    a database. This process updates records for custoemrs.

    The problem is the code works PERFECTLY in Access 2003. I take that
    same database and open it in Access 2007 or 2010 and the import
    process is not consistent. The Policy Number field which is
    alphanumeric field and set to a text field in Access will drop the
    entries if they have letters in the field. Treating it like a number
    field. In Access 2003, it imports 100% correctly.

    This is the line of code:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
    "AAExportData", strImportFilePath & strImportFileName, True

    Another thing, if I manually import it in to Access specifically
    stating that field is a text field it will work. Even if I save
    import specs it doesn't seem to work.

    Should the code be different for Access 2007/2010?
     
    clk, Aug 7, 2012
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.