[AccessD] Database Conversion

Sat Jan 6 17:57:38 CST 2024

So what are the sizes of the FE and BE after compacting before and after processing?

Instead of compact and repair before processing, In both the BE and FE, go into 
File-Options-Current Database and select "Compact On Close".  It will happen in the 
background automagically.

I'd also use a copy of the FE on the local machine and only have the BE on the network
Also, if you are using temporary tables, Keep them in the FE on the local machine and 
make sure to delete them when processing finishes.

On 6 Jan 2024 at 21:05, Randall R Anthony via AccessD wrote:

> Hello, follow-up,
> As usual, the KISS principle works.  Did a simple split into separate
> FE/BE, c/r'd, ran the process/crunched the numbers.  From a 1.9 GB
> after processing, before the split, I have a 400MB FE and 1.2GB BE, so
> plenty of room in the interim crunch while I explore other options. 
> Much obliged to everyone who chimed in, again, Happy New Year to all. 
> 
> -----Original Message-----
> From: AccessD
> <accessd-bounces+rranthon=sentara.com at databaseadvisors.com> On Behalf
> Of Jim Dettman via AccessD Sent: Saturday, January 6, 2024 1:21 PM To:
> 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com> Cc: Jim Dettman
> <jimdettman at verizon.net> Subject: Re: [AccessD] Database Conversion
> 
> Notice: This email originated outside the Sentara Health network.
> Beware of links and attachments. Report suspicious emails with the
> Report Phishing button.
> 
> << to 1.89GB after processing.>>
> 
>  Keep in mind too that you can have multiple BE's.
> 
> Jim.
> 
> -----Original Message-----
> From: AccessD On Behalf Of Randall Anthony
> Sent: Saturday, January 6, 2024 9:47 AM
> To: 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Database Conversion
> 
> Thanks for the replies everyone, looking things over now.
> Stuart, see below, (BTW, I'm a SQL DBA that actually coded in Access
> years ago)
> 
> Before doing that:
> It is on a network or single user?
> Network, but basically single user.
> 
> Is it currently a split BE/FE or is everything in the one file.
> If it is one file, try splitting them first.
> Totally forgot about that, will try that first (BTW, it's been 5-6
> years since actively working in Access)
> 
> If multi-user, is a a share sing file or FE or do all users have
> seprate local FEs. No, see above.
> 
> When was the last timei the data file was compacted?
> Do you regularly compact your data  (Is it configured to "compact on
> close"?) It's c/r'd before every run, down to about 800MB prior to
> running, to 1.89GB after processing.
> 
> As an alternative, you may consider multiple back ends:
> https://answers.microsoft.com/en-us/msoffice/forum/all/there-is-a-maxi
> mum-si ze-of-2gb-for-
> an-accdb-file/29b095ce-95b0-4291-b3e1-e4baff288ad3
> 
> 
> On 5 Jan 2024 at 20:33, Randall R Anthony via AccessD wrote:
> 
> > Hello Group,
> > Happy New Year!  I come before this august group with a question on
> > converting an Access DB to using SQL as the BE.  This is an ancient
> > DB (circa 2001, current v = 2013) that's bumping up against the 2GB
> > wall.
> >  It uses a lot of macros in the processing logic.  How/what's the
> >  best 
> > way to expedite converting to SQL tables?  I've seen a few posts
> > back in the day that used a SQL?  Access?  conversion tool with
> > mixed results.  Thank you for your time.
> > 
> > Regards,
> > R.
> > 
> > 
> > 
> > 
> > 
> > 
> > Disclaimer:
> > 
> > 
> > This electronic message and its contents and attachments contain
> > information from Sentara Health and is confidential or otherwise
> > protected from disclosure. The information is intended to be for the
> > addressee only.
> > 
> > If you are not the addressee, any disclosure, copy, distribution or
> > use of the contents of this message is prohibited. If you have
> > received this electronic message in error, please notify us
> > immediately and destroy the original message and all copies. --
> > AccessD mailing list AccessD at databaseadvisors.com
> > https://secure-web.cisco.com/12v1bt6cWaKD5nqHzAq_CJixU_0yu1MUDB3hSvj
> > z8PWKVlJ-peQeBwmH7jfFIk0TyLIy4Iq6jsLpTaV-w-SVDC8_8H8kWDdNv2WVR51Bp-o
> > yXcNZ5GDkhL3TJgU7M_vxYOx34Cke50ciUJsi6kBxsPApXw2RsrwM4wzK7BbfZdMOuDi
> > NPCmHaIUffyaBCOthhw_-XvRCdP7tnCGlnjfoXMsYAVYuqr_YDZxGomK0JemGhV-8yop
> > xpF9bFgVz6Go2_EgHJh68Xim-q5cjhRJZJuDYF8Nv-OVpGa7O11xWLlDKtZJIbdfFA_0
> > H7p5zrtPhS/https%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2
> > Faccessd Website:
> > http://secure-web.cisco.com/1eYg_sEm6yy710-m_hbQZJJaHILobokS2zyVsAbH
> > nF
> > xDRyOO9dWxkjDI4v2YSM0tnNWYey-0YME8xessBVGWZCJUPyj--ySAcb-hPEDd9moUrO
> > Fa
> > JwudFYvx-2Bzr9DmS6heoiPmeii_f5wtjqYsA8w7gYnaWwMKWGNxiaTbBwN68nzsKXj4
> > oi
> > kETXmH8r3mJMTR-n2l9mpXYHIRU8ZlAE_-vcJOsVFWmJG7_DurTBTO6rXV5oKyjkQ8TV
> > Fz
> > VkrATfQSqxjrv1VsSxAgV-6PcZJB9x_jG6e2x5KlujA5dRgFligVP0Xb6ftpPBJ4MjIT
> > e/ http%3A%2F%2Fwww.databaseadvisors.com
> > 
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://secure-web.cisco.com/12v1bt6cWaKD5nqHzAq_CJixU_0yu1MUDB3hSvjz8
> PWKVlJ-peQeBwmH7jfFIk0TyLIy4Iq6jsLpTaV-w-SVDC8_8H8kWDdNv2WVR51Bp-oyXcN
> Z5GDkhL3TJgU7M_vxYOx34Cke50ciUJsi6kBxsPApXw2RsrwM4wzK7BbfZdMOuDiNPCmHa
> IUffyaBCOthhw_-XvRCdP7tnCGlnjfoXMsYAVYuqr_YDZxGomK0JemGhV-8yopxpF9bFgV
> z6Go2_EgHJh68Xim-q5cjhRJZJuDYF8Nv-OVpGa7O11xWLlDKtZJIbdfFA_0H7p5zrtPhS
> /https%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd
> Website:
> http://secure-web.cisco.com/1eYg_sEm6yy710-m_hbQZJJaHILobokS2zyVsAbHnF
> xDRyOO9dWxkjDI4v2YSM0tnNWYey-0YME8xessBVGWZCJUPyj--ySAcb-hPEDd9moUrOFa
> JwudFYvx-2Bzr9DmS6heoiPmeii_f5wtjqYsA8w7gYnaWwMKWGNxiaTbBwN68nzsKXj4oi
> kETXmH8r3mJMTR-n2l9mpXYHIRU8ZlAE_-vcJOsVFWmJG7_DurTBTO6rXV5oKyjkQ8TVFz
> VkrATfQSqxjrv1VsSxAgV-6PcZJB9x_jG6e2x5KlujA5dRgFligVP0Xb6ftpPBJ4MjITe/
> http%3A%2F%2Fwww.databaseadvisors.com
> 
> 
> --
> This email has been checked for viruses by Avast antivirus software.
> http://secure-web.cisco.com/1tXgIbMIovNzE8lgfXK8AeZGmF11LJLxFSf0ZOJGqi
> BwiZPATyrbCxL0OwqXDgZQB63ZZ87WYHn3SMqJJq_Lr245N1ocLbvlpyH7f3Q_tjMnORSx
> nP0_JtiAzMCOAAK-rLi6rUef_IYLu4JF-0uJ5D7YUqTer3ocu4veGynk2yMtNwTOa2FhIs
> hFVq9jiHnxul4bFm1ioRx9iNhdh4N-IhJMIUM7Tlx9HiaXpyXZpbEFKI_O3P0M2MArkhN-
> Q4kPlpZ5sqs8kvJibJj19heS5-_du_HkZ0hsxpiyDPkoysNerAPjZ6NSrWED0KXRFEseh/
> http%3A%2F%2Fwww.avast.com -- AccessD mailing list
> AccessD at databaseadvisors.com
> https://secure-web.cisco.com/12v1bt6cWaKD5nqHzAq_CJixU_0yu1MUDB3hSvjz8
> PWKVlJ-peQeBwmH7jfFIk0TyLIy4Iq6jsLpTaV-w-SVDC8_8H8kWDdNv2WVR51Bp-oyXcN
> Z5GDkhL3TJgU7M_vxYOx34Cke50ciUJsi6kBxsPApXw2RsrwM4wzK7BbfZdMOuDiNPCmHa
> IUffyaBCOthhw_-XvRCdP7tnCGlnjfoXMsYAVYuqr_YDZxGomK0JemGhV-8yopxpF9bFgV
> z6Go2_EgHJh68Xim-q5cjhRJZJuDYF8Nv-OVpGa7O11xWLlDKtZJIbdfFA_0H7p5zrtPhS
> /https%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd
> Website:
> http://secure-web.cisco.com/1eYg_sEm6yy710-m_hbQZJJaHILobokS2zyVsAbHnF
> xDRyOO9dWxkjDI4v2YSM0tnNWYey-0YME8xessBVGWZCJUPyj--ySAcb-hPEDd9moUrOFa
> JwudFYvx-2Bzr9DmS6heoiPmeii_f5wtjqYsA8w7gYnaWwMKWGNxiaTbBwN68nzsKXj4oi
> kETXmH8r3mJMTR-n2l9mpXYHIRU8ZlAE_-vcJOsVFWmJG7_DurTBTO6rXV5oKyjkQ8TVFz
> VkrATfQSqxjrv1VsSxAgV-6PcZJB9x_jG6e2x5KlujA5dRgFligVP0Xb6ftpPBJ4MjITe/
> http%3A%2F%2Fwww.databaseadvisors.com
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://secure-web.cisco.com/12v1bt6cWaKD5nqHzAq_CJixU_0yu1MUDB3hSvjz8
> PWKVlJ-peQeBwmH7jfFIk0TyLIy4Iq6jsLpTaV-w-SVDC8_8H8kWDdNv2WVR51Bp-oyXcN
> Z5GDkhL3TJgU7M_vxYOx34Cke50ciUJsi6kBxsPApXw2RsrwM4wzK7BbfZdMOuDiNPCmHa
> IUffyaBCOthhw_-XvRCdP7tnCGlnjfoXMsYAVYuqr_YDZxGomK0JemGhV-8yopxpF9bFgV
> z6Go2_EgHJh68Xim-q5cjhRJZJuDYF8Nv-OVpGa7O11xWLlDKtZJIbdfFA_0H7p5zrtPhS
> /https%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd
> Website:
> http://secure-web.cisco.com/1eYg_sEm6yy710-m_hbQZJJaHILobokS2zyVsAbHnF
> xDRyOO9dWxkjDI4v2YSM0tnNWYey-0YME8xessBVGWZCJUPyj--ySAcb-hPEDd9moUrOFa
> JwudFYvx-2Bzr9DmS6heoiPmeii_f5wtjqYsA8w7gYnaWwMKWGNxiaTbBwN68nzsKXj4oi
> kETXmH8r3mJMTR-n2l9mpXYHIRU8ZlAE_-vcJOsVFWmJG7_DurTBTO6rXV5oKyjkQ8TVFz
> VkrATfQSqxjrv1VsSxAgV-6PcZJB9x_jG6e2x5KlujA5dRgFligVP0Xb6ftpPBJ4MjITe/
> http%3A%2F%2Fwww.databaseadvisors.com
> 
> Disclaimer:
> 
> 
> This electronic message and its contents and attachments contain
> information from Sentara Health and is confidential or otherwise
> protected from disclosure. The information is intended to be for the
> addressee only.
> 
> If you are not the addressee, any disclosure, copy, distribution or
> use of the contents of this message is prohibited. If you have
> received this electronic message in error, please notify us
> immediately and destroy the original message and all copies.
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 