Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Attached is a spread sheet that I am trying to bring into an Access Database but I keep getting an error message stating it cannot be done. I realize this spreadsheet starts at column A and ends at column BE which is quite wide. The data is only numbers and text and counts about 6500 rows. Any idea what I am doing wrong. Another question, Is it possible to import formula into Access?

    Thanks
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    A couple of things to try:
    • Remove the first two blank rows. Column headings should be in Row 1
    • Remove special characters like # % from the field names
    No you cannot import formulae.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by MNN View Post
    Another question, Is it possible to import formula into Access?
    Further on this. When you import from Excel the import goes into a table. An Excel spreadsheet supports both stored values and calculations (using formulas)
    An Access table contains just stored values, so there is no scope for formulas in an Access table.

    In Access, calculations are performed in queries, in code, in form or in reports, and there is no scope to import to them. But importing is not that important. In Excel you need to repeat the formula for each row of data. In an Access query, you write the formula just once, and it is applied to every line of data.
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have been trying to import the attached sample into access but Access keeps denying . Any ideas what's wrong with this spread sheet.


    Thanks

    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I get a message that the search key was not found in any record when I try to import it, but I can link to it just fine. I used Access 2007, and I see you are using 2003. I suspect there is something hidden in the data which is causing the error, but it isn't obvious.
    Wendell

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Some of the Column Headings (eg Gross Pay ) begin with a space character. Try removing all leading spaces in column headings.

    ps: I have merge this thread back into the one where you first raised this issue.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you. I will eliminate the leading spaces and try again

  8. #8
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    It is probably not the spaces. You have two columns with the same name. When I put a 2 after one of them, I was able to import into Access 2007 just fine. I did notice a lot of leading blanks in row 1, so you might want to take a look at that. I did remove the leading blanks, but really think the duplicate heading was the problem.

    I hope this is a one time thing for a report and not the start of an Access database. It jumps out at me that you will end up with 4 sets of fields to designate four facilities (?). In a database you should have one set with 4 rows. But that is a separate subject.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Larry Engles View Post
    It is probably not the spaces.
    I did not notice the duplicate columns. But leading spaces have caused me a lot of angst in the past, so they were my prime suspect.
    Regards
    John



Posting Permissions

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