Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Madison, Wisconsin, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    I am relatively new to Access and this is my problem. I have created a Database in Access. I have created all the tables, Queries, forms and reports and they all work with new data I type into the forms. I then have an Excel spreadsheet that I need to import to that database. I have gone into Excel and changed all the fields in Excel to match the name and field type in Access. My problem is that I can only import the spreadsheet as a new table it will not import into my existing table. (I get the error the "An error occurred importing the file. The file will not be imported) So I have to make the spreadsheet it's own table. What am I missing or doing wrong? How do I link the new spreadsheet table to the Access table so the data will appear in my tables and reports?

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    It sounds like you want to retain your existing table and periodically add to it by importing from Excel. If this is the case, then it's probably easiest to go ahead and create a separate Access link table that references your Excel worksheet. Then use an Append query to add the contents of the link table to your existing table (the append query will give you an opportunity to select which fields to append and you don't have to worry about your Excel column names matching the Access table field names). If you're new to Append queries and need more help, holler and I'm sure one of us Lounger's will quickly come to your rescue.

    Hope this helps.

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Location
    Madison, Wisconsin, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    I have imported my link table now but couldn't figure out the append query. I found it on the menu and activated it but now I can not view my query at all. It just tells me the data is going to change and then never opens the query.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    An update query is one of a general type called action queries. Action queries don't open in the way select queries do, they just perform some action on your data.

    If you open them in design view, you will see what they are going to do, and if you try to view them it shows you a list of the records that will be affected if you run the query. You can run the query from here by clicking the lartge exclamation mark on the toolbar.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    You are doing better than I am. When I go to File, Get External Data, Import, under Files of Type, I only have the options of Access or ODBC Database. Where do I say that I want to import an Excel file? I am running Access 97 SR-2. I seem to remember having imported an Excel table before, but I am not sure.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    George,

    I would guess that you have an incomplete or damaged Office installation. You should be able to import at least from Access, DBase, Excel, FoxPro, HTML, text files and ODBC.

    Open Control Panel
    Open Add/Remove Programs
    Select Microsoft Office 97, Professional
    Click Add/Remove...
    Select the option to repair your installation.

    Reagrds,
    Hans

  7. #7
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    I reinstalled Access, but the problem persists. There is a MS Knowledge Base article, Q160954, which address the issue. I attempted Method 1 to resolve it, but no good. Also, going back a bit, I found Lounge Post 157249 which addresses the same issue.

    This is frustrating. I will try to creat the file on another machine today, but that is not really a solution.

    Any other thoughts? Thanks for the help.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    Copy the Excel data you want in the database to the clipboard. Then select a new record in the table and paste from the clipboard. This only works if you have the same number of fields and the data types match.

  9. #9
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    When I Copy/Paste I get a message that the Text IsToo Long To Be Added. It appears that the information is all trying to go into the first field of the record. I tried Paste Append, but then it says that the first field is empty, which it is not. I can paste all of the fields one by one into the new record.

    Just an observacion - the information to be pasted does not appear in my Clipboard Viewer. I can paste the infomation into Word, for example, but not into Access.

    This solution would be great if we could get it to work.

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Importing Excel to Access (Access 97 SR-1 & Excel 97 SR-2)

    George,

    If the number of columns in excel and the access table are the same, however, your access table contains an autonumber ID, then you need to do the following. Highlight all of the fields in excel that you want to copy and press copy. Go to next available row in access where the data is to be inserted. Highlight the first available cell next to the autonumber id to the last available cell and then paste. If you do not have the autonumber id, you should be able to highlight the entire row. You will receive an error if using autonumber id in access and select the entire row when the autonumer id is not in excel.

    As to importing or linking to excel, if the system won't allow it, re-create the machine data source to do it.

    HTH
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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