Front End = Access; Back End = MySql problem

pozzo

Registered User.
Local time
Today, 00:52
Joined
Jan 23, 2004
Messages
26
Hello

I´m working in an application using Access as FE and MySql as BE.

When I add some registers to this linked table, OK.

Problem: When I try to add a lot of registers (700.000!) it does´nt work and the msg is that the computer does´nt have memory resources to do the job.

If I try the same with Access tables it works fine.

MySql configuration, perhaps?

Thanks a lot

Renato
 
What code are you using in Access to add the Registers? What version of Access and what OS are you using?
 
RuralGuy

Thanks for replying.

I´m using an update query.

My Access is 2003.

My OS is Win XP SP2.

Keep in touch

Renato
 
My code:

INSERT INTO tabelao1 ( cart_usu, nome_usu, data_realiz, tipo_mov, cod_proc_ins, desc_proc_ins, cod_prestador, nome_prestador, esp_prestador, quant, valor, mod, desc_mod, nro_guia, loc_atend, desc_loc_atend, conv, desc_conv, grau_depend, data_nasc, ano_comp, mes_comp, chave_unica )
SELECT [aacrt 2005].cart_usu, [aacrt 2005].nome_usu, [aacrt 2005].data_realiz, [aacrt 2005].tipo_mov, [aacrt 2005].cod_proc_ins, [aacrt 2005].desc_proc_ins, [aacrt 2005].cod_prestador, [aacrt 2005].nome_prestador, [aacrt 2005].esp_prestador, [aacrt 2005].quant, [aacrt 2005].valor, [aacrt 2005].[mod], [aacrt 2005].desc_mod, [aacrt 2005].nro_guia, [aacrt 2005].loc_atend, [aacrt 2005].desc_loc_atend, [aacrt 2005].conv, [aacrt 2005].desc_conv, [aacrt 2005].grau_depend, [aacrt 2005].data_nasc, [aacrt 2005].ano_comp, [aacrt 2005].mes_comp, [aacrt 2005].chave_unica
FROM [aacrt 2005]
WHERE ((([aacrt 2005].cart_usu)="0305921049901"));
 
Maybe someone with MySQL experience will see something. Is it over a network or is MySQL local to the PC? How about the Access only configuration? FE/BE Local/Remote?
 
It´s MySQL local to the PC.

FE/BE Local.
 
So your PC must run Access *and* MySQL. That could strain the OS. How much real memory are you using? How about free disk space? Let's hope a MySQL Guru will drop by and see and/or offer something that will help.
 
Glad you got it sorted. Thanks for posting back.
 
The problem is probably the way Access caches the append. Notice how Access always tells you how many rows will be affected by an action query and then asks if you want to proceed. To do that, Access requires a substantial amount of memory to hold the data in just in case you say no to the update prompt.
 

Users who are viewing this thread

Back
Top Bottom