ACCESS FEED

Senin, 04 September 2023

Re: [MSAccessProfessionals] Error Out of Memory - How big is too BIG for MS Access?

Crystal,

Thanks for your input.
I believe I released them almost any time I used them.

Example :
Dim CurrDB as Dao.Database
SET CurrDB = CurrentDb()

' Some code
' Some code
' Some code
CurrDB.Close
SET CurrDB = Nothing

That article is nice, thanks.

Question : is the number of objects I have, beyond what Access can handle?

Best,
Ozair

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] Error Out of Memory - How big is too BIG for MS Access?

hi Ozair,

maybe there are places in your code that you're not releasing objects, and that is keeping those connections "alive" unnecessarily. As a general rule, at the end of each procedure, I add code to release object variables for that procedure

Daniel has a helpful article. He calls this sort of thing "self-healing-object-variables"

https://www.devhut.net/self-healing-object-variables

kind regards,
crystal

On 9/5/2023 12:49 AM, ozair wrote:

Hi,

I need experts advice on how big to too BIG for a single MS Access file?
We are struggling with a nasty "Out Of Memory" bug that stops us far too frequently.

As soon as it appears, it undoes the most recent written code - eventually failing to compile the file.

What I already have tried (temporarily solves the problem)
=============================================
- Compact & Repair
- Creating a brand new ACCDB then exporting all objects to it.
- Decompile

Maybe we have a really large MS Access application that consists of
1452 Forms
1500 MS Access saved queries
750 Tables (MS SQL 2012)
450 Reports

Other Information
==============
We use RDP to connect to the Server.
Windows Server 2012 R2 on Xeon CPU with 154GB RAM
MS Office 2016 (64-bit)

Any help is greatly appreciated.

Best,
Ozair

[MSAccessProfessionals] Error Out of Memory - How big is too BIG for MS Access?

Hi,

I need experts advice on how big to too BIG for a single MS Access file?
We are struggling with a nasty "Out Of Memory" bug that stops us far too frequently.

As soon as it appears, it undoes the most recent written code - eventually failing to compile the file.

What I already have tried (temporarily solves the problem)
=============================================
- Compact & Repair
- Creating a brand new ACCDB then exporting all objects to it.
- Decompile

Maybe we have a really large MS Access application that consists of
1452 Forms
1500 MS Access saved queries
750 Tables (MS SQL 2012)
450 Reports

Other Information
==============
We use RDP to connect to the Server.
Windows Server 2012 R2 on Xeon CPU with 154GB RAM
MS Office 2016 (64-bit)

Any help is greatly appreciated.

Best,
Ozair

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] Access Data Query long running - left join

it's important that text fields you're joining on have a short Size. By default, text fields are 255. I change default to 50 but even that is too long for fields you'll use to join. When indexes are made, the full Size is used. You may not be able to change field Size of big tables. Best to do then is make a new table with proper sizes and append all the records.

What are the data types for fields you're joining on?

kind regards,
crystal

On 9/4/2023 4:41 AM, Kiran Kumar via groups.io wrote:

i am trying to resolve this in MS access only as we dont have access to use other DB

On Sunday, September 3, 2023 at 02:54:39 PM GMT-8, anzus101 <anzus101@gmail.com> wrote:

You best bet is to move your back end to postgresql, then create a view and then link the view as an external data source.

On Monday, September 4, 2023, Kiran Kumar via groups.io <kirankumarnv=yahoo.com@groups.io> wrote:

I am trying to fetch data from two tables of 0.7 M records Left Join match all 7 columns with 1.5 M records table and Group By all columns from left table.

Data Query is getting freezed and long running with no output. FYI i tried applying index

I am getting immediate output if i use aggregate functions with this join

Please advise at the earliest

Re: [MSAccessProfessionals] Access Data Query long running - left join

i am trying to resolve this in MS access only as we dont have access to use other DB

On Sunday, September 3, 2023 at 02:54:39 PM GMT-8, anzus101 <anzus101@gmail.com> wrote:

You best bet is to move your back end to postgresql, then create a view and then link the view as an external data source.

On Monday, September 4, 2023, Kiran Kumar via groups.io <kirankumarnv=yahoo.com@groups.io> wrote:

I am trying to fetch data from two tables of 0.7 M records Left Join match all 7 columns with 1.5 M records table and Group By all columns from left table.

Data Query is getting freezed and long running with no output. FYI i tried applying index

I am getting immediate output if i use aggregate functions with this join

Please advise at the earliest

Minggu, 03 September 2023

Re: [MSAccessProfessionals] Access Data Query long running - left join

I am trying to fetch data from two tables of 0.7 M records Left Join match all 7 columns with 1.5 M records table and Group By all columns from left table.

Data Query is getting freezed and long running with no output. FYI i tried applying index

I am getting immediate output if i use aggregate functions with this join

Please advise at the earliest

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

[MSAccessProfessionals] Access Data Query long running - left join

I am trying to fetch data from two tables of 0.7 M records Left Join match all 7 columns with 1.5 M records table and Group By all columns from left table.

Data Query is getting freezed and long running with no output. FYI i tried applying index

I am getting immediate output if i use aggregate functions with this join

Please advise at the earliest