Database exceeding 2 GB during macro execution

[Vinod]

Hi All,

I've an issue with database size exceeding 2GB while the macro generating
xml through the following process:

1. tblUsers - consists of 1000 users
2. tblMasterData (225 colums) - consists of weekly sales detailes for region
wise of all employees (users)

Step1: Getting user name's through recordset
Step2: Copy tblMasterData (all regions detail for each user) to tblTmpCalc
table (clear pre-existed data) for user name retrieved from Step1
Step3: Perfrom some calculations in tblTmpCalc (225 columns)
Step4: Copy each record (calculated each region sales) from tblTmpCalc to
tblTmpXML table (clear pre-existed data) which consists of 36 rows(metrics
part of 225 columns in tblTmpCalc )
Step5: Export tblTmpXML as XML with UserName_Region
Step6: Repeate Step4 & 5 to generate XMLs with all regions specifice to the
user comes from step1
Step7: Do the same process from Step2 through Step6 for each user from
recordset (step1)

i.e., If a user belongs to 10 regions 10 XMLs for that user and tblTmpCalc
includes aggregation of 10 regions. In this scenarios 11 (10+1 - aggregate)
XMLs will be generated.

After generating 5000 XMLs I'm getting an error 'Invalid Argument' this time
I noticed that access database has reached to 2GB it was 130MB before start
of XML generation.

To overcome this I'm dropping and recreating the temp tables (tblTmpCalc
and tblTmpXML) instead of deleting pre-existed records in step 2 and 4 but
I'm unable to resolve the issue. Also I'm closing the recordset where ever I
opened after performing required steps (except step1) i.e., closing the
recordset which is opend after genertion of xmls for one user to another user.

I came to know that memory exceeding will be resolved if the database is
compacted. To do this user needs to stop the macro and perform compact disc
which causes to discontinue the sequence of user XMLs from where it stopped.
This process is quite against to the requirement where user doesn't want to
stop in the middle.

Please help me in resolving above said issue which will be appreciated.

Advanced Thanks
Vinod

 

[Stefan Hoffmann]

hi Vinod,

1. tblUsers - consists of 1000 users
2. tblMasterData (225 colums) - consists of weekly sales detailes for region
wise of all employees (users)

The number of columns is unusually high.

For the size problem: Use temporary databases files (*.mdb created in
code) to store the data and link these.

Or consider using SQL Server (Express) as back-end. Which will remove
the size limit (4GB for the Express version).



mfG
--> stefan <--

 

[david]

Put tblTmpXML and tblTmpCalc into a different database.
Link to the tables.

So they are linked tables.

This will be slower. Linked tables are slower than internal tables,
even when the linked database is local. But not always much
slower. It works OK for most people most of the time.

Every now and then, close the linked table, delete the linked
database, and create a new empty linked database by copying
from a 'template' copy that you made previously.

You may find that copying a database to a new name is faster
than deleting the table or deleting the records. In that case, you
can do it again for every new set of data.

Or you may find that copying a database to a new name is
much slower than deleting the table or deleting the records.
In that case, count, and only delete the external database
when you think it is getting to big.


If you do find that linked tables are too slow (unlikely), there
are things you can do to make it faster.

(david)

 

[Vinod]

Thanks David for your response,

I've gone through your response which gave me confident at work but I don't
have any knowledge on onfly creation of data base with linked tables and
delete them at the end of each user xmls generation like how I'm doing
deleting temp tables at this moment.

It would be great for me if you can provide macro code for what mentioned in
your response. And more over xml generation process will start on click of a
button on main form which displays a label with refreshing caption like
'<user> xmls are generating. Please wait.' So, it should be refreshed
automatically even when we use linked database.

Awaiting for your response.

Advanced Thanks,
Vinod

 

[Rick A.B.]

Thanks David for your response,

I've gone through your response which gave me confident at work but I don't
have any knowledge on onfly creation of data base with linked tables and
delete them at the end of each user xmls generation like how I'm doing
deleting temp tables at this moment.

It would be great for me if you can provide macro code for what mentionedin
your response. And more over xml generation process will start on click of a
button on main form which displays a label with refreshing caption like
'<user> xmls are generating. Please wait.' So, it should be refreshed
automatically even when we use linked database.

Awaiting for your response.

Advanced Thanks,
Vinod

Just my two cents, Tony Toews has a great download for using tmp
databases. You can find it at http://www.granite.ab.ca/access/temptables.htm
Hope that helps.

Rick

 

[Tony Toews [MVP]]

Just my two cents, Tony Toews has a great download for using tmp
databases. You can find it at http://www.granite.ab.ca/access/temptables.htm

You took the words right out of my keyboard. <smile>

Thanks for the kind words.

Tony