Is it possible to work on a Word .docx or .docm file while Access is locked up creating a table?

Question

Is it possible to work on a Word .docx  or .docm file while Access is locked up creating a table.

My accdb file has a sub routine that takes 30 to 45 minutes to run.  Unable to have any laptop key respond during that time, except   Ctrl-Alt-Delete that is.

If no problems with code, it will take 30 to 45 minutes to add 300,000,000 records to tableOfTestValues.

So at the minimum I cannot even start Solitaire once sub routine is running then play Solitaire waiting for Sub routine to finish running.

Wouldn’t mind so much if while I was waiting, I could work on my documentation files in Word.

This code takes so long, I really don’t want to add any code that makes code take even longer to run.

Adding code so after so many records added to the table, run DoEvents line.  If DoEvents line runs, doesn’t that stop current code to let other code run? If I am right about that, then each time the line with DoEvents runs, it stops adding records to the tableOfTestValues. I sure don’t want the routine to take longer.

Almost the worst is finding out after 45 minutes, code got stuck at table record #45 of 300,000,000 and for 45 minutes nothing got done to table. And I was unable to work on my Word documentation files.

Would there be some way, somehow have Word already running with my Word file (.docx or .docm) already open?

Then just somehow switch to Word after start long running code?

Running Window 7 Ultimate, 64 bit, Office 2013 64 bit, Access 2013 64 bit, Word 2013 64 bit VBA 7.1 , Lots of Ram and Hard drive space, NOT part of GitHub, but do visit web site.

---

Mark J

Answer 1

Yes.   They are totally separate programs but they both will be using computer resources.    So the Access will be slowed a little more.

---

Build a little, test a little

Answer 2

Karl,

that is what I thought.

when I am running other access code. I have no problem running Outlook, Excel, Word and a access accdb file.

granted at those times I am not using Outlook to send out millions of emails(my email blast is 3 emails at once), or have any long running macros or code in Excel, Word or even Access.

I am unable to start task manager to check status of computer resources.

the closest I have come to THE answer is, only seems to lock up,

while running VBA code using DAO recordSet (based on a table) to add a "test value" to a new record and add that new record to the table.

so it seems while DAO is adding a record to a table, it is telling everybody else to wait till done adding ALL records.

when the number of records  I am adding, is under 9,000 no problem.  But that might be due to being able to add that many records in a such a sort time, I don't notice access has tied up all computer resources.

even with all programs I could turn off to free up resources, it still seems access is taking all the computer resources.

Running Window 7 Ultimate, Service Pack 1, 64 bit, Office 2013 64 bit, Access 2013 64 bit, Word 2013 64 bit VBA 7.1 , Lots of Hard drive space, Intel Core i7-3940XM CPU,  running at 3.20 GHz  and 32.0 GB of ram.  It seems I should have way more computer resources than needed to handle this.

---

Mark J

Answer 3

Hi Mark J,

>> am unable to start task manager to check status of computer resources. the closest I have come to THE answer is, only seems to lock up,

This depends on the resource on the computer. When we run the program on the computer, the program need CPU, memory and other resource. Basically, the process is running separately. However if both the program require the same resource, the resource is limited. The program may wait for other program.

For example, we may see the CPU was used 100%, at that time when you start a new program, the program may not started as soon as possible.  

And for you scenario, you were using the Access UI thread(VBE) to insert the data so it will block the Access UI. To get responding from the Access, I suggest that you developing a client(like C# console application) to insert data. And you can get more control of the process and thread so that it won't block other application.

More detail about inserting data into Access you can refer to the link below:
ADO.NET Code Examples

Regards & Fei

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 4

Mark -

1. That the work is being done in a Sub inclines me to think that it is being done a record at a time with VBA? You might go faster using a query, although 10 million records a minute is impressive. (I can’t recall ever having to deal with 3e8 of anything.)
2. Is the process time-linear depending on the number of records? Just curious.
3. Of course, if the TaskManager is opened prior to the execution of the work, I think it is possible (?) to lower the Access priority so you have more time to “do other things”.

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 5

Peter,

Sorry I was NOT as clear as I thought I was.

1. responses.

You are right in your # 1.     I am calculating a value to be added the record, before it is added one record at a time to a table.

I have used action queries.  Append one set of records to a different table, when that table is empty or does have records before I add the new records. 

I think I have my notes on how to add only record ( with data in record fields) to a table, somewhere. Using a query.  I didn't use that method because I was able to use DAO like this

to create the TEST Values.

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = OpenDatabase("C:\Users\Mark\Documents\HelpCodeAll.accdb", Options:=False, ReadOnly:=False)
    Set rst = dbs.OpenRecordset("tblTestValues", dbOpenDynaset, dbSeeChanges)

Dim lngCounter as Long

Dim lngMaxNumberOfTestRecords  As Long

lngCounter = 1

DoWhile lngCounter < lngMaxNumberOfTestRecords

    With rst
        .AddNew
        .Fields("TestValue") = lngCounter  * myRandomizingFunction
        .Fields("Status") = No
        .Update
        .Close
    End With

Loop

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

it was so much easier to code, and debug doing code this way, I didn't even consider using a query to add the records. 

it also seemed code needed to do this using a query would be more complicated, and would take longer to run.  other then easier to code and debug, I have no idea if I was correct in using a query would take longer.

as to your # 2 question.

Is the process time-linear depending on the number of records? Just curious.

As far I have been able to tell, if you mean, if I double the number of records, then I double the time it takes to finish the code? The only thing that would take longer the more records is. If it took longer to have a field store a bigger number, in a record in a table.

I have no code such that, more code, tests or branches are involved as the number of records increases.

As to your #3 I will try running Task Manger prior to the execution of the work. I would have sworn, I tried already.  At least as far as checking on the resources being used at the time.

your idea about lowering the priority of Access, sounds so great, I am going to try it.  At this time I can't swear that in the past I had opened Task Manger first. And for sure I didn't lower the priority.

Mark

---

Mark J

Answer 6

Fei,

Thanks for the link to ADO.NET Code Examples   Looking at Examples, makes it clear I need to do some research on ADO.NET to be able to figure out how to ask some questions, on terminology used in ADO.NET.

I don't know all the correct terminology I need yet, so 

I am wondering if by

Access UI thread(VBE)

you are referring to the Access user interface thread ?   in VBA 7.1?

If that is what you meant, are you saying I my code seems to be using the "thread" or the path way, to insert data, so that path way is busy so unable to get data from mouse or key strokes to where that data needs to go?

As for developing a client(like C# console application) to insert data. And you can get more control of the process and thread so that it won't block other application.

I have done some research on using threads in ways to make sure won't block other threads.  BUT seemed to be more for in VB not VBA I am using for this code.

Don't know C#, only console application I have done so far, is some very simple ones in VB.NET 4.5.2 where user inputs info.  And then console returns the results.

The code that seems to lock up the unit, seems to only lock up unit until code finishes adding LARGE amount of data that is only used to test my other code with a large data set. ( works with small data set--10,000 records-- very well and quickly ) a more real life data set is 300,000,000 plus records.

Mark

---

Mark J

Answer 7

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = OpenDatabase("C:\Users\Mark\Documents\HelpCodeAll.accdb", Options:=False, ReadOnly:=False)
    Set rst = dbs.OpenRecordset("tblTestValues", dbOpenDynaset, dbSeeChanges)

Dim lngCounter as Long

Dim lngMaxNumberOfTestRecords  As Long

lngCounter = 1

DoWhile lngCounter < lngMaxNumberOfTestRecords

    With rst
        .AddNew
        .Fields("TestValue") = lngCounter  * myRandomizingFunction
        .Fields("Status") = No
        .Update
        .Close    <<<<< move this statement
    End With

Loop

<<<<< to here

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

According to some further literature research, VBA is faster than a SQL statement in this case. To speed up the VBA, move the .Close outside of the loop.

Speed is often measured as a function of the number of times the calculation is performed, n. In the intense case, n = 300,000,000. If everything was being done in memory, I would expect the calculation time to be linear in n. If it’s not, (if it's n², for example) then it might pay to break the calculation into several parts, and assemble the parts when the calculations are complete.

Note that it might be economically feasible to run the calculations on (a) separate machine(s).

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 8

Peter,

Thanks for the info.

1. VBA faster in this case.

2. about moving the .Close outside the loop.

3. Is there a way to tell if everything was being done in memory?

Short of buying expensive test gear, or code products?

4. As to running on a different machine, I am running this on my fastest machine, with clock speed boosted a bit.

so it seems I might just have to plan on doing lots of other things, while code is running.

Mark.

---

Mark J

Answer 9

3. would require piercing the Access veil, and probably the Windows veil, as well. Best shot at that is the Task Manager, at least the one for Win10. Which, by the way, is not showing me how to change the priority.
On the other hand, this is an interesting discussion: http://scicomp.stackexchange.com/questions/19793/should-i-rent-computing-resources-or-buy-my-own-computers

and this is immediately on hand http://arc-ts.umich.edu/resources/rates/

 

 

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 10

3. would require piercing the Access veil, and probably the Windows veil, as well. Best shot at that is the Task Manager, at least the one for Win10. Which, by the way, is not showing me how to change the priority. 

On the other hand, this is an interesting discussion: http://scicomp.stackexchange.com/questions/19793/should-i-rent-computing-resources-or-buy-my-own-computers

and this is immediately on hand

http://arc-ts.umich.edu/resources/rates/

 

 

---

peter n roth - http://PNR1.com, Maybe some useful stuff