Front End = Access; Back End = MySql problem

[pozzo]

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

 

[RuralGuy]

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

 

[pozzo]

RuralGuy

Thanks for replying.

I´m using an update query.

My Access is 2003.

My OS is Win XP SP2.

Keep in touch

Renato

 

[pozzo]

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"));

 

[RuralGuy]

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?

 

[pozzo]

It´s MySQL local to the PC.

FE/BE Local.

 

[RuralGuy]

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.

 

[pozzo]

RG

I decided for another approach.

I´m using Navicat to add data to MySQL instead of using Access to do that.

It works fine.

Here is Navicat link:

http://www.navicat.com/detail.html

 

[RuralGuy]

Glad you got it sorted. Thanks for posting back.