Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Speed-up DoCmd.Close (97)

    Well, here I am in what I lovingly call the looney-bin, better known as the Access forum. <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    Anyway, for some reason, known only to the government, they didn't like my data in a spreadsheet, had to have it in a database. "The import code doesn't always work, please fix it Sam!" Actually, it looks like it does work, except the import (via DoCmd.TransferSpreadsheet) takes about 15 minutes, which is not a problem because you have a progress bar. But (finally the question) the DoCmd.Close acQuery takes 20 minutes with no feed-back During this time Access is using 80-90% of my dual-processor, each 2.2 Ghz, 1GB ram machine! Isn't this a little excessive? Which part of close does it not understand? Yes, there are 8,000 records and it seems to run fine with smaller imports, so I'm going to try to copy the big sheet to a bunch of smaller worksheets and put the import into a loop. Might work. TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Speed-up DoCmd.Close (97)

    How many columns does the spreadsheet have?
    If it has only 8000 records it should import at the speed of light (there is some conjecture of just how fast that is !!).
    If the spreadsheet has no sensitive data, post it and I will try it on my 300 machine (it's from the dinosaur era).
    Pat

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Speed-up DoCmd.Close (97)

    Spreadsheet is 24 cols x 8110 rows.
    Manual inport via File | Get External Data| Import takes 14.5 minutes, using 50% of the CPU time and 34 MB memory on the dual-monster.
    Our secretary can type that fast. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    Must be the 8K rows that is the killer, but why also in the query-close?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Speed-up DoCmd.Close (97)

    Be nice, Sam. <img src=/S/scold.gif border=0 alt=scold width=50 height=15> Some of us loonies would take exception to that! <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Why are you using DoCmd.Close on a query and what does it have to do with the import?
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Speed-up DoCmd.Close (97)

    One thing to try is to save the Worksheet as a .CSV file - I think you'll find the transfer goes much faster. We frequently pull in several thousand rows in seconds. I also second Charlotte's question about the "query-close" - why is that being used?
    Wendell

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Speed-up DoCmd.Close (97)

    > Why are you using DoCmd.Close on a query
    I knew you would make me do unreasonable things like understand the code! The code is cryptic, at least to me, but I'm working on it and trying to create a simple program with the problem, so bear with me. I'll try to have an answer today. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Speed-up DoCmd.Close (97)

    Great idea, Wendell! Unfortunately, when I tried doing this manually, Access choked with "Too large a record." Curious, what kind of monster have I been given? Using Word, turns out those 8K records by 24 columns have a whopping 8.5 million characters and the largest record has 4461 characters. Guess I'll have to take back some of the Bill-bashing, but not all. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Speed-up DoCmd.Close (97)

    Good grief - you have some records that are spanning pages in Access97. That causes all sorts of performance degradation, or else you have to use memo fields which does the same thing. After all, I sure it took Leo Tolstoy more than 15 minutes. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    Wendell

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Speed-up DoCmd.Close (97)

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15>, and guess what happened to all that data:
    DoCmd.TransferSpreadsheet... into a temporary table
    Get User's permission to update the real table
    OpenQuery "Imports" to retrieve the data that needs to be added
    RunCommand acCmdSelectAllRecords
    RunCommand acCmdCopy which puts a pointer to the data on the clipboard
    OpenTable "Entries"
    SetWarnings False
    RunCommand acCmdPasteAppend
    Close acTables, "Entries"
    Close acQuery "Imports" which copies everything to the clipboard since Warnings were off
    SetWarnings True but too late

    So, I cleared the clipboard before closing the query and thanks to you-all, I'm the hero.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Speed-up DoCmd.Close (97)

    I suspect you might be an even greater hero if you used an append query instead of doing a paste append. Native mode SQL tends to run much faster than commands, and you don't need to mess with the clipboard at all. BTW, you may be <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22> if you run this process a number of times - the file size limit for Access97 is 1GB. You will also need to compact and repair regularly as the database will grow like topsy if you don't, and then you'll discover it's too big to compact. Also, are you deleting data after a period of time - that would help greatly as well. Glad things got to working for you - now you just have to worry about the <img src=/S/alien.gif border=0 alt=alien width=14 height=15>s abducting things tomorrow.
    Wendell

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Speed-up DoCmd.Close (97)

    Sam,
    I'm with Wendell on this. That is a truly ugly way to go about it. Mimicking a manual process may work in Word or Excel but in Access it will bite you! Turn your select query into an append query and dispense with the rest. If you alias the temporary table or use the same table name for every import, you can build an append query that you can reuse each time. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Speed-up DoCmd.Close (97)

    Boy, now if I just knew what you said <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> But, I'll figure it out. First, I need to get out the bright lights and rubber hose and get someone to explain the big picture to me. Somehow, using very bizzare SQL (ie JOINS <img src=/S/flee.gif border=0 alt=flee width=25 height=25>), after the import the code figures out from whether to add, delete, and/or modify records. Need to understand and hopefully simplify that, then figure out the append query.

    You're also correct on the compact & repair, they have created a monster, but this is just a temporary prototype, until the customer can afford and get Oracle. But, someone needs to do some design work. As Charlotte said, mimicking the manual process is not usually the best way, but this was just a low-budget effort and unfortunately for now, I've run out of money, <img src=/S/broke.gif border=0 alt=broke width=31 height=23> but it will raise its ugly head again. Hopefully by then, I will have passed the Outlook MOUS test, so I can start learning Access. Thanks, again. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Speed-up DoCmd.Close (97)

    Actually you'll find the query route much simpler than the hoops you've had to jump through to get this to work as it is. And no need to understand all that "JOINS" stuff - there shouldn't be any in what you need to do. Just create a select query using the query grid and then tell it you want to do an append. All that should happen after the users figure out any editing in the temp table. Of course you could just do the editing in the permanent table, but that might make the users a bit nervous. Now about that Outlook stuff - if you ever pass the MOUS test you'll be so <img src=/S/confused.gif border=0 alt=confused width=15 height=20> that Access will look plain simple.

    As to Oracle, why not consider a small version of SQL Server? It can grow to huge sizes, and doesn't have quite the same overhead as Oracle. In fact, if you only have a single user, the developer version would be sufficient. Otherwise your best bet might be SBS - it includes SQL Server and 5 CALs for about the same price as the Standard SQL Server version.
    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
  •