HMGforum.com

Hello everyone,
Is anyone using MDB instead of DBF tables in a production environment?
If YES, what are the advantages and what is the behavior, especially when the table has hundreds of thousands of records?
I am facing some problems with DBFs that have about 700 / 800k records (table and index corruption) and I don't want to migrate directly to MySQL.


Best regards,

Salamandra, Brazil

If you think

.mdb, a file-extension used in certain versions of Microsoft Access databases

If you have 500k + records then you definitely need a lot of computation, I didn't use mdb but I heard that users had problems with computation (rounding to some decimal)

Try it, if it counts badly then don't waste time

Best will be SQL!

Serge

Hi Serge,

serge_girard wrote: ↑Tue Jul 07, 2020 9:45 am Best will be SQL!

Serge

Yes I agree that any flavor of SQL (MS, My, Maria, etc.) would be the perfect solution, but I don't have time to rewrite the entire system. Modifying it to use MDB tables instead of DBF, working MDB well and being reliable, would be a quicker solution to the problem.
Another problem is that my client cannot change the (old) file server at this time.
I will need to do a lot of research and think about it in order to make the best decision.

Best regards,

Salamandra, Brazil

Vote for SQL, but which version?
Access is coming with MS Ofiice
Very interesting is MS SQL Express
but MySQL is simpler and easier in terms of installation and maintenance

Hola Muchachos
Usen MySQL, descarguen el Xampp, y alli podran ver las tablas MySQL como se ve en el DBU, es mas facil y practico el uso de MySQL, no es dificil,al usarlo te va a cambiar la forma de pensar en la programacion, La base de un sistema es el buen diseño de las Bases de Datos, a partir de alli, diseñas todo el esqueleto de la programacion, no te llevara mucho tiempo aprenderlo, es facil, mas si tenes idea de base de datos. Te lo aseguro Amigo. Un Codazo (x este Covid19, no te mando un abrazo, jajajaja).-

I agree with Jorge!

I had a similar problem a few years ago. After 25 years a customers invoice and invoice items tables became very large. With xp and 4 gigs of ram
The computers basically where not fast enough. At first I started using temporary indexes in the terminals and it seemed to work, but not perfect.
I would set a set table index of what I wanted, then seek a starting point of what they wanted. Then created the temp index with a while statement
to where I wanted to end. The only thing is you still have to set index to temp plus old indexes for changes so you are updating big indexes.
I still use temporary indexes for most of my reports. instead of select from (sql) I index on ? for or while to temp.
Then we wondered why he needed so much history and I added a procedure where he could pick how much history he
really needed. He would copy his whole program to a backupjuly2020 folder, which he could use to look at old records when ever he wanted.
Then we would remove 15 or 18 years of invoices from the working program. I now suggest for new customers and old to get fast computers with lots
of memory. All my customers are business people so it is important.
Do you have a problem with your tables in fast computers or only old slow ones. Something to think about.
I still have not figured out sql so maybe I am behind the times.

salamandra wrote: ↑Mon Jul 06, 2020 9:43 pm I am facing some problems with DBFs that have about 700 / 800k records (table and index corruption).

En los años que utilice Clipper y ahora HMG, rara vez tuve problemas como estos que mencionas.
Pero es adivinar que puede estar sucediendo:
No utilizar DBCOMMIT() después de grabar, modificar los campos con Indices y tener cerrados el indice correspondiente.
... etc. etc.
Casi siempre he utilizado DBEVAL() y muy poco alguno que otro indice.
*------------------------------------------------------------------------------------------------------------------------------
In the years that I used Clipper and now HMG, I rarely had any issues like these you mention.
But it is to guess what may be happening:
Do not use DBCOMMIT () after saving, modify the fields with Indexes and have the corresponding index closed.
... etc. etc.
I have almost always used DBEVAL () and very little of any other index.


https://www.hmgforum.com/viewtopic.php? ... val#p45852

http://www.hmgforum.com/viewtopic.php?f ... val#p39392

http://www.hmgforum.com/viewtopic.php?f ... ezl#p36875


P.D.
Esta es una DBF con 20,000 registros SIN INDICES en donde hago una busqueda x 3 campos solo con DBEVAL()
+-------------------------------------------------------------------------------------------------------------------------------------------------------
This is a DBF with 20,000 records WITHOUT INDICES where I do a search x 3 fields only with DBEVAL ()

Hola @andyglezl,

andyglezl wrote: ↑Wed Jul 08, 2020 7:30 pm

salamandra wrote: ↑Mon Jul 06, 2020 9:43 pm I am facing some problems with DBFs that have about 700 / 800k records (table and index corruption).

En los años que utilice Clipper y ahora HMG, rara vez tuve problemas como estos que mencionas.
Pero es adivinar que puede estar sucediendo:
No utilizar DBCOMMIT() después de grabar, modificar los campos con Indices y tener cerrados el indice correspondiente.
... etc. etc.
Casi siempre he utilizado DBEVAL() y muy poco alguno que otro indice.
*------------------------------------------------------------------------------------------------------------------------------
In the years that I used Clipper and now HMG, I rarely had any issues like these you mention.
But it is to guess what may be happening:
Do not use DBCOMMIT () after saving, modify the fields with Indexes and have the corresponding index closed.
... etc. etc.
I have almost always used DBEVAL () and very little of any other index.


https://www.hmgforum.com/viewtopic.php? ... val#p45852

http://www.hmgforum.com/viewtopic.php?f ... val#p39392

http://www.hmgforum.com/viewtopic.php?f ... ezl#p36875


P.D.
Esta es una DBF con 20,000 registros SIN INDICES en donde hago una busqueda x 3 campos solo con DBEVAL()
+-------------------------------------------------------------------------------------------------------------------------------------------------------
This is a DBF with 20,000 records WITHOUT INDICES where I do a search x 3 fields only with DBEVAL ()

DBEVAL.gif

No, mis problemas realmente no son causados por índices cerrados o falta de instrucciones de COMMIT del programa.
Talvez el tipo de aplicaciones que tu desarrollas permita que trabajes sin (o con escasos) índices, lo que no es mi caso.
En el caso que mencioné, mi sistema de ERP atiende mas de 70 workstations (entre la administración y la producción) y
genera desde centenas hasta, dependiendo del sector, millares de nuevos registros x dia. Por ejemplo, una nueva factura
genera cerca de 140/150 nuevos registros de diversos tipos en diversas tablas DBF, entre finanzas, stock, bajas de producción, etc.
Sumale a eso, la generacion de pedidos de compra y de venta, ordenes de produccion, apuntamientos de inicio y termino de
operaciones de fábrica, etc. Al final del dia habras generado millares de nuevos registros.
No es un asunto tan simple como parece... tambien hay la cuestion de la seguridad de las tablas DBF que es exactamente CERO.
Cualquier usuário las copia, las abre y hasta las puede alterar utilizando VFP, DBase, Excel, Calc, etc.
Otro problema, bastante comun, son los usuários impacientes que reinician la PC con una sesion de sistema abierta simplemente
porque creen que la PC esta trabada cuando la verdad es que esta ejecutando alguna operacion que demora. Eso no solo causa
corrupcion de las tablas y de los índices, mas tambien perdidas de datos....
En fin, realmente es complicado...
Preciso resolver, muy en breve veré como lo hago, si con MSSQL o con MySQL.
Muchas gracias por tu colaboracion y tu opinion !!


** ENGLISH **
No, my problems are not really caused by closed indexes or lack of COMMIT instructions from the program.
Perhaps the type of applications that you develop allow you to work without (or with few) indexes, which is not my case.
In the case I mentioned, my ERP system serves more than 70 workstations (between administration and production) and
generates from hundreds to, depending on the sector, thousands of new records per day. For example, a new invoice
generates about 140/160 new records of various types in various DBF tables, including finance, stock, production downtime, etc.
Add to that, the generation of purchase and sale orders, production orders, start and end points of factory operations, etc.
By the end of the day you will have generated thousands of new records.
It is not as simple a matter as it seems ... there is also the question of DBF table security which is exactly ZERO.
Any user copies, opens and can even alter using VFP, DBase, Excel, Calc, etc.
Another rather common problem is impatient users restarting the PC with system sessions in a open state simply because they think
the computer it is locked when the truth is that it is executing some operation that takes time. This not only causes corruption, but
also data loss ... Anyway, it really is complicated ...
I need to solve, very soon I will see how I do it, whether with MSSQL or MySQL.

Best regards,



Salamandra, Brazil