ACCESS FEED: September 2023

Sabtu, 09 September 2023

Re: [MSAccessProfessionals] VBE Project Explorer and code windows are all over the place.

Thanks Arnelito,
I had tried every conceivable comvbination of Tools->Options->Docking but whatever I did wouldn't work.
I could get the PE and Properties panels to fit between the menu, left and bottom edges but the Code window would continue to hide underneath them when expanded.
Then I found the by shrinking the VBE the PE and Properties panels were actually floating on the background!

I eventually used the nuclear option that I had read somewhere, (wish I'd recorded the url so I could give them a shout out), but that had I lacked the courage previously to try and violá, it worked!
Using Regedit navigate to Computer\HKEY_CURRENT_USER\Software\Microsoft\VBA and delete the key (and subkeys) for 6.0 or 7.1 whichever is appropriate. Then restart just in case.
On opening the VBE, the PE and Properties menu were anchored between the menu, left and bottom margins and did not float on shrinking the VBE. The Immediate window could be pinned to the bottom right of the screen and the code windows stayed between these boundaries.

At last, a useable coding setup has been returned to me.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] VBE Project Explorer and code windows are all over the place.

on VBA, goto Tools on menu->Options.

Choose Docking tab and Tick all Dockable windows.

On Sat, Sep 9, 2023 at 4:12 AM d_i_s_r <regularmail@disrees.com> wrote:

I don't know how but the windows have suddenly come adrift and I can't find a way of reverting to the standard.
It is almost making completly unuseable.
I have tried move the Project Explorer and Object windows back where they belong on the left but even then the code window slides under them when maximised.
Before this happened the PE and Obj panes were static and the code window would be fixed to them.
There are some suggestions in the net but none of themappear to work for me.
I'm running Access 365 on Win 11
Can anybody help please before I lose what little hair I have left on my head!

Arnelito G. Puzon

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Jumat, 08 September 2023

[MSAccessProfessionals] VBE Project Explorer and code windows are all over the place.

I don't know how but the windows have suddenly come adrift and I can't find a way of reverting to the standard.
It is almost making completly unuseable.
I have tried move the Project Explorer and Object windows back where they belong on the left but even then the code window slides under them when maximised.
Before this happened the PE and Obj panes were static and the code window would be fixed to them.
There are some suggestions in the net but none of themappear to work for me.
I'm running Access 365 on Win 11
Can anybody help please before I lose what little hair I have left on my head!

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Kamis, 07 September 2023

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

@Richard
Yes the application is too old that's why its too big. We'll soon remove the MDW.

@Jane
Thanks.

The only challenge AFTER we split the single file into multiple smaller files - switching from one to another.
I know I can add an ACCDE as a Reference then call its objects from the current file.
Good: No user intervention. Objects are opened within the same database container.
Bad: The ACCDE file being called, gets corrupted more frequently.

Best,
Ozair

On Wed, Sep 6, 2023 at 06:28 AM, Richard Rost wrote:

MDW? Wow. Those are old. Microsoft phased those out with Access 2007. User and group level security has been gone since then.

--
Live Long and Prosper

Richard Rost
Microsoft MVP 2023
President/CEO
Access Learning Zone

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Rabu, 06 September 2023

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

MDW? Wow. Those are old. Microsoft phased those out with Access 2007. User and group level security has been gone since then.

--
Live Long and Prosper

Richard Rost
Microsoft MVP 2023
President/CEO
Access Learning Zone

_._,_._,_

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 Ozait
It's possible you're hitting the limits for the number of controls on forms (754 over the lifetime of the form) or the max number of objects (32768) or max number of modules (1000 inc forms & report that have 'has module' set to true).
See the below link for other max limits in Access.
https://support.microsoft.com/en-us/office/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c
Might be time to split the database?
Best regards
Jane

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Selasa, 05 September 2023

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

@Crystal,
Thanks, its INT data type (MS SQL)

@Mark,
Thanks, I will more closely look into it.

@All,
Did I tell you? - the error ONLY appears when I am creating a new object or adding controls to an existing or copy pasting some of my code from within the application.
Summary : It never happened to end users but has become a nightmare to programmers.

Best,
Ozair

On Tue, Sep 5, 2023 at 07:14 AM, Mark Burns wrote:

<!doctype html>
@ozair,

You've gotten some good advice so far from Crystal and others, and it seems you're already doing most of those things.
Unfortunately, it may not be enough. I have seen both ~2GB databases that still worked (up to finally hitting the limit), and much smaller ones that hit those "out of memory" errors long before 2GB. The OOM errors are not about database size, but about the heap memory limit, and that is HALF the 2GB database limit. Access(/VBA) can only manage 1GB of heap space in use, and there are problems with this.

This is what you're running into most likely.

In these cases where the OOM errors recur frequently - some application re-engineering may be needed, or you simply need to be absolutely certain you're not leaking any COM Object references. So, step 1 is to review your code to be certain that you "clean up your toys" the moment you're done using them. If you are assigning COM object reference in any PUBLIC/Global variables, focus your re-engineering on getting rid of those cases. For example, of you are using Excel via automation, it is tempting to put the Excel.Application reference into a public/global and keep it open for re-use. Don't. Bad Things(tm) (like OOM errors) tend to happen when you do this kind of thing.

On 09/05/2023 9:45 AM EDT ozair <ozairkhalid@hotmail.com> wrote:

On Tue, Sep 5, 2023 at 06:15 AM, Richard Rost wrote:

I agree with Crystal on checking your object variables, but also, don't try to do too much with a single database. I've had students before who tried to run their entire business off of one single database, when in reality, it could have been split into multiple databases. Remember, the accounting people don't necessarily need the same information as the warehouse and shipping people, or the CEO. You can create multiple front-ends that access the same back-end data, and each one can be much more lightweight and compact. So keep that in mind; one single database doesn't have to run the whole operation.

And remember, you can make buttons on your main menu that can open the different databases. So, in case the shipping people do need to access the customer records, they can. But if it's not something they use on a daily basis, then don't try to pack all those features into a database that's not going to use them.

LLAP
RR
@Richard,
I agree with you, the file should be split into smaller files.
We are using MS Access Workgroup Security MDW file - maybe I am unaware or its not possible to avoid the logon dialog.
That would have user intervention - that's undesirable.

@Jane,
After compact, the file size is around 200mb~250mb. As per MSFT's documentation - its still far below the limit.
Yes, its already split into FE (MS Access 2016 - 64-bit) with BE (MS SQL 2012).

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?

@ozair,

You've gotten some good advice so far from Crystal and others, and it seems you're already doing most of those things.
Unfortunately, it may not be enough. I have seen both ~2GB databases that still worked (up to finally hitting the limit), and much smaller ones that hit those "out of memory" errors long before 2GB. The OOM errors are not about database size, but about the heap memory limit, and that is HALF the 2GB database limit. Access(/VBA) can only manage 1GB of heap space in use, and there are problems with this.

This is what you're running into most likely.

In these cases where the OOM errors recur frequently - some application re-engineering may be needed, or you simply need to be absolutely certain you're not leaking any COM Object references. So, step 1 is to review your code to be certain that you "clean up your toys" the moment you're done using them. If you are assigning COM object reference in any PUBLIC/Global variables, focus your re-engineering on getting rid of those cases. For example, of you are using Excel via automation, it is tempting to put the Excel.Application reference into a public/global and keep it open for re-use. Don't. Bad Things(tm) (like OOM errors) tend to happen when you do this kind of thing.

On 09/05/2023 9:45 AM EDT ozair <ozairkhalid@hotmail.com> wrote:

On Tue, Sep 5, 2023 at 06:15 AM, Richard Rost wrote:

I agree with Crystal on checking your object variables, but also, don't try to do too much with a single database. I've had students before who tried to run their entire business off of one single database, when in reality, it could have been split into multiple databases. Remember, the accounting people don't necessarily need the same information as the warehouse and shipping people, or the CEO. You can create multiple front-ends that access the same back-end data, and each one can be much more lightweight and compact. So keep that in mind; one single database doesn't have to run the whole operation.

And remember, you can make buttons on your main menu that can open the different databases. So, in case the shipping people do need to access the customer records, they can. But if it's not something they use on a daily basis, then don't try to pack all those features into a database that's not going to use them.

LLAP
RR
@Richard,
I agree with you, the file should be split into smaller files.
We are using MS Access Workgroup Security MDW file - maybe I am unaware or its not possible to avoid the logon dialog.
That would have user intervention - that's undesirable.

@Jane,
After compact, the file size is around 200mb~250mb. As per MSFT's documentation - its still far below the limit.
Yes, its already split into FE (MS Access 2016 - 64-bit) with BE (MS SQL 2012).

Best,
Ozair

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

hi Ozair,

What kind of Number? Long Integer? If it's Double, they aren't precise for joins

kind regards,
crystal

On 9/5/2023 2:26 AM, ozair wrote:

Crystal,

I am sure I did not intentionally removed previous messages.
Throughout the application, the JOINS are always on Number data type.

Best,
Ozair

PS : Forgive me if it again removes the previous messages to the thread.
On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

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

On Tue, Sep 5, 2023 at 06:15 AM, Richard Rost wrote:

I agree with Crystal on checking your object variables, but also, don't try to do too much with a single database. I've had students before who tried to run their entire business off of one single database, when in reality, it could have been split into multiple databases. Remember, the accounting people don't necessarily need the same information as the warehouse and shipping people, or the CEO. You can create multiple front-ends that access the same back-end data, and each one can be much more lightweight and compact. So keep that in mind; one single database doesn't have to run the whole operation.

And remember, you can make buttons on your main menu that can open the different databases. So, in case the shipping people do need to access the customer records, they can. But if it's not something they use on a daily basis, then don't try to pack all those features into a database that's not going to use them.

LLAP
RR

@Richard,
I agree with you, the file should be split into smaller files.
We are using MS Access Workgroup Security MDW file - maybe I am unaware or its not possible to avoid the logon dialog.
That would have user intervention - that's undesirable.

@Jane,
After compact, the file size is around 200mb~250mb. As per MSFT's documentation - its still far below the limit.
Yes, its already split into FE (MS Access 2016 - 64-bit) with BE (MS SQL 2012).

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?

Ho Ozair
How big is the Access file after you've compacted it and how big does it bloat to when you've been using it? I think generally if it goes above 2Gb, it'll stop working & you get some strange error messages.
Also is the database split front end/backend? ie All the data is actually stored elsewhere & the front end only has links to the data tables, queries, forms, reports & code.
Best regards
Jane

_._,_._,_

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?

I agree with Crystal on checking your object variables, but also, don't try to do too much with a single database. I've had students before who tried to run their entire business off of one single database, when in reality, it could have been split into multiple databases. Remember, the accounting people don't necessarily need the same information as the warehouse and shipping people, or the CEO. You can create multiple front-ends that access the same back-end data, and each one can be much more lightweight and compact. So keep that in mind; one single database doesn't have to run the whole operation.

And remember, you can make buttons on your main menu that can open the different databases. So, in case the shipping people do need to access the customer records, they can. But if it's not something they use on a daily basis, then don't try to pack all those features into a database that's not going to use them.

LLAP
RR

_._,_._,_

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?

Crystal,

I am sure I did not intentionally removed previous messages.
Throughout the application, the JOINS are always on Number data type.

Best,
Ozair

PS : Forgive me if it again removes the previous messages to the thread.
On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

On Tue, Sep 5, 2023 at 12:13 AM, crystal (strive4peace) wrote:

hi Ozair,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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,

you're welcome.

when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

~~~
other email clients may have a different method such as an Advanced Setting to "Insert the original email text to a reply" (thanks, Giorgio)

I asked:

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

and if text, what is Size?

thanks

kind regards,
crystal

---

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

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

---

On 9/5/2023 1:59 AM, ozair wrote:

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

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