Speed-up DoCmd.Close (97)

Topic

New Reply

Well, here I am in what I lovingly call the looney-bin, better known as the Access forum.
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

Reply | Quote

Replies

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

Reply | Quote

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.
Must be the 8K rows that is the killer, but why also in the query-close?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

, 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.

Reply | Quote

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 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 s abducting things tomorrow.

Reply | Quote

Boy, now if I just knew what you said 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 ), 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, 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

Reply | Quote

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 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.

Reply | Quote

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.

Reply | Quote

Be nice, Sam. Some of us loonies would take exception to that!

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

Reply | Quote

> 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

Reply | Quote