Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Problem with Copy and Paste 55000 records?

    Does anyone know how to copy and paste 55000 some records in a table with the maxlocs error coming up?. I have the data in excel as well as access but I don't want to do 2000 rows at a time.

    Thanks.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Problem with Copy and Paste 55000 records?

    Originally posted by tjarvas
    Does anyone know how to copy and paste 55000 some records in a table with the maxlocs error coming up?. I have the data in excel as well as access but I don't want to do 2000 rows at a time.

    Thanks.
    What about some code? Is the data in one table and you want to copy it to another?

  3. #3
    Join Date
    Feb 2004
    Posts
    142
    Link to the excel worksheet as a table and use a query.
    KC

  4. #4
    Join Date
    Mar 2004
    Posts
    361
    I linked it to a query and ran it. All the data is there now but how can I get the data from the query into the table?

  5. #5
    Join Date
    Mar 2004
    Posts
    361
    Is there a way that i could run the form from the query?

  6. #6
    Join Date
    Feb 2004
    Posts
    142
    Yes you can run the form from a query but the data may not be updatable. And it will be fairly slow.

    You can, however, change your query to an append query or a make table query to import your data. What I do is run the query as a make table the first time and then look the data over to see how big the fields in the new table need to be. I then change the field sizes in that table to fit. Then I change the query to an append query for any future imports from the Excel sheet.

    If you get these Spreadsheets often then rename the current one after loading it, and rename the next one the same as the old name the first one was. As long as the sheet dosen't change, the append query will bring in the new data.
    KC

  7. #7
    Join Date
    Mar 2004
    Posts
    361
    I guess I don't understand what you are getting at. I have a query that has a column with 55000 some records that I need. There seems like there would be a way to take that info and turn it into a table. I guess I'm stuck.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you've got the answer there from AZ KC.

    simplest is to make a new query looking at your existing query
    menu Query¦Append and you add records to an existing table
    or
    menu query¦make table and you create a new table

    it is normally nicer to append - you get to control the table design.

    izy
    currently using SS 2008R2

Posting Permissions

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