Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have some code that links an Excel spreadsheet to an Access database then does some work with the data in that linked table.

    One of the fields has traditionally held numeric values, but recently we've been receiving some that have text values (A123456789 instead of 1234567890) and the link fails to recognize it, and the column is created as Numeric.

    How do I fix it so I don't lose my Text values?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Are you actually changing the data in the Excel workbook, or are you doing things in a temporary table or query. If you are actually editing the Excel data, that becomes a challenge. The linking process that decides what sort of data you are dealing with and sets the field values looks at somewhere between 10 and 100 rows - I'm sure it must be documented somewhere - and then says text or numeric. If on the other hand you are importing it into a temporary table, then you can set the data type using queries on linked tables, or you can use the TransferSpreadsheet with an import specification. If some of that doesn't make sense, post back.
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I have found that it checks the first 25 rows to determine the data type.
    What i would do is import the excel sheet using TransferSpreadsheet as Wendell suggests into a new table and check the data types against the data types of the field that you are going to append to.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Another option is to open the spreadsheet in code prior to linking, and sort on the problem column, to force a text value to the top.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My code links the table then take each record and inserts it into other tables based on certain criteria.

    I've tried formatting the Excel sheet column to text and I've tried putting the non-numeric values at the top, but to no avail.

    This is the code I use:
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "blahblah", <FilePath><FileName>, True, ""

    It's right after this piece that I can check the linked table and see that the column has the wrong data type.

    Sign me Very Frustrated!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The issue is that you are using the acLink option. To really solve this kind of problem, you will have to create a table with field names that match the columns in the Excel worksheet (or specify that there are no field names) and use the acImport option. Then do your manipulations using the table you imported the worksheet (or range) to. I was incorrect in indicating that there was a import specification you could use with TransferSpreadsheet. That can only be used with TransferText - which is an option if you are willing to save your Excel workbook as a .csv file (which I doubt you would be).
    Wendell

Posting Permissions

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