Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel import (2007)

    I would like to import the results of an access query into a preexisting worksheet. If I use the standard import options the list of available queries does not include the requisite one. If I select OBDC as a source I do get a full list with the appropriate query. However when a I attempt the import into either a range or a new worksheet I get the following error [Microsoft][ODBC Microsoft Access driver] Too few parameters. Expected 1.

    When I went to check the spelling the initial portion of the error message disappeared It is 'Microsoft''ODBC Microsoft Access driver' with brackets instead of ' marks

    As a second issue the query in question is a parameter query the criteria being a form field (primary key ID field) Is this an additional problem?

    Any thoughts?

    nebbia

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel import (2007)

    Hi Nebbia

    Can we see the SQL string you are using, it may be a syntaxial thing. It is normally a spelling mistake of one of the field names.
    Jerry

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel import (2007)

    The SQL string works perfectly in access. It is
    SELECT Visit.Visit, Visit.Weight, Visit.BMI
    FROM Visit
    WHERE (((Visit.PatientID)=[forms]![patients]![patientID]))
    ORDER BY Visit.Visit;

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

    Re: Excel import (2007)

    You can't use a parameter query - ODBC can't handle those, and it will display the error message "Too few parameters. Expected 1".

    An alternative would be to export the results of the query from Access to an Excel workbook.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel import (2007)

    That would actually be preferable as I would like to automate the export of the query from within access. Perhaps has to do with the particular excel file but I get this error message when I attempt the export. "Reserved error (-5016); there is no message for this error"

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

    Re: Excel import (2007)

    That error message isn't very helpful - it would be nice if Access provided a real clue.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel import (2007)

    Hi Nebbia

    This error can be caused when sending data from a database on your local PC to a networked drive. It is normally cused by a slow connection. Am I right to assume you are trying to send to a network? If so, have you tried to export locally?
    Jerry

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel import (2007)

    I've attached a very stripped down db as well as the excel file I would like to export the query to.
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel import (2007)

    Please see prior attachment. The 'real' database has its backend on a LAN. The frontend and excel file are in the same folder. Though these are 2003 file formats I am running office 2007

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

    Re: Excel import (2007)

    Perhaps you stripped down the database too far. I can export the query to the workbook without problems, both interactively and using DoCmd.TransferSpreadsheet. (I created a form Patients).

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel import (2007)

    I will have to add more to the db to see if I can find the problem. Can I export the query into a particular range, on a particular worksheet?

    Thanks

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

    Re: Excel import (2007)

    The query will always be exported to a sheet with the same name as the query; if this sheet already exists, it will be overwritten. The export will always start in cell A1.

    If you need to export to a specific range on a specific sheet, you must use open a recordset that selects the records you want, then use Automation to open the workbook in Excel and write the data to the desired location.

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel import (2007)

    Thank you

    nebbia

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel import (2007)

    On my end the problem appears to be with the workbook. I can export the qry (parameter) to a new excel workbook but not to the specific workbook(workbook.xls) that I would like. I can not find what about the workbook is preventing the export. I still get the same error messages 5061 or table is not properly formated. I have attached the database and the workbook

    nebbia
    Attached Files Attached Files

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

    Re: Excel import (2007)

    Have you tested it with the version of the database and workbook you attached? Once again, I have no problem exporting qryWeight to the workbook.

    I'm using Office XP (2002), I don't have Office 2007 yet, so if this is something specific to Office 2007 I cannot test it. Perhaps a Lounger who has Office 2007 will be kind enough to try it.

Page 1 of 2 12 LastLast

Posting Permissions

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