This is a migration project.
The solution should be written in Visual Basic.
The process is very straigthforward and simple, all has been thought previously.

The host should be a variable, so we could change it at will.

The data is in an Access Database and should be posted to a Zencart (MySQL) Database.

The software should autostart with the system.

Should activate every 24 hours.

Specific Migration
------------------
Any field not mentioned should be ignored

Access Database
---------------

Table
-----
JOYACLEN

For each different record of JOYACLEN (Client) then
Check if the id of the customer (JOYACLEN.NUME) already exists in the address book table of Zencart (address_book.customers_id):
If exists, then update it, else create a new one

The data for an "address_book" (Zencart) Record:
JOYACLEN.DIRE -> address_book.entry_street_address
JOYACLEN.DIR2 -> address_book.entry_suburb
JOYACLEN.COPO -> address_book.entry_postcode
JOYACLEN.POBL -> address_book.entry_suburb
JOYACLEN.PROV -> address_book.entry_city
JOYACLEN.PAIS -> address_book.country


Then insert or update a customer record in Zencart
JOYACLEN.NUME -> customers.customers_id
JOYACLEN.SEXO -> customers.customers_gender
JOYACLEN.NOMB -> customers.customers_lastname
JOYACLEN.DNIF -> customers.customers_taxid
JOYACLEN.TELF -> customers.customers_telephone
JOYACLEN.TEL2 -> customers.customers_fax
JOYACLEN.EMAI -> customers.customers_email_address
JOYACLEN.MAIL -> customers.customers_newsletter
JOYACLEN.TIPO -> customers.customers_group_pricing
JOYACLEN.NACT -> customers.customers_authorization BUT if JOYACLEN.NACT = 0 customers.customers_authorization = 0 if JOYACLEN.NACT = -1 customers.customers_authorization = 1

customers_info.customers_info_id is the same of customers.customers_id
customers.customers_default_address_id is the id of the address_book record found or created above
JOYACLEN.FALT -> customers_info.customers_info_date_account_created




Table
JOYASTOC

For each different record of JOYASTOC (Client), then
Read JOYASTOC.FAMI and JOYASTOC.SECC
If the value of either is 98 or 99, then skip that record

Check if the category (JOYASTOC.SECC) already exists in the categories table of Zencart (categories.categories_id) when parent_id = 0 (when 0, it is a category != 0 is a subcategory of that id)
If exists, then update it, else create a new one

categories.categories.sort_order = 0
categories.categories_status = 1
categories.parent_id = 0

Also, a record on categories_description is needed
categories_description.categories_id is the id of the category above
categories_description.language_id = 7
JOYASECC.DESC -> categories_description.categories_name (JOYASECC.CODI relates to JOYASTOC.SECC)

Check if the subcategory (JOYASTOC.FAMI) already exists in the categories table of Zencart (categories.categories_id) when parent_id <> 0 (when != 0 is a subcategory of the parent_id)
If exists, then update it, else create a new one

categories.categories.sort_order = 0
categories.categories_status = 1
categories.parent_id = (the category parent_id)

Also, a record on categories_description is needed
categories_description.categories_id is the id of the subcategory above
categories_description.language_id = 7
JOYASECC.DESC -> categories_description.categories_name (JOYASECC.CODI relates to JOYASTOC.SECC)

Next create or update the products (Zencart) Record:
JOYASTOC.CODI -> products.products_id
JOYASTOC.PESO -> products.products_weight
With JOYASTOC.FOTO do







JOYASTOC.PVEE -> products.products_price
JOYASTOC.DESC -> products.descripcion_oculta (This is a custom field created by us)
JOYASTOC.POVE -> products.proveedor_oculto (Another custom field created by us)
JOYASTOC.REFE -> products.referencia_proveedor (Another custom field created by us)
JOYASTOC.UNVE -> products.unidad_peso (Another custom field created by us)
JOYASTOC.PEME -> products.peso_medio (Another custom field created by us)
JOYASTOC.WFLAG -> products.products_status BUT when JOYASTOC.WFLAG = 0 then products.products_status = 1 and if JOYASTOC.WFLAG = -1 then products.products_status = 0
JOYASTOC.PCOS -> products.precio_cliente

Next create or update the manufacturers (Zencart) Record: (obviously, if doesn't exists, create also an manufacturers_id value)
JOYASTOC.MARC -> manufacturers.manufacturers_name
manufacturers.manufacturers_date_added is today() (only if the record is created)


Next create a products_options (Zencart) Record:
products_options.products_options_id -> it is a different id, automatically incremented
products_options.language_id = 7
JOYASTOC.MEDI -> products_options.products_options_length

Next create a products_attributes (Zencart) Record: (product_attributes links products and product_options)
products_attributes.products_attributes_id (Automatically incremented)
JOYASTOC.CODI -> products_attributes.products_id
products_attributes.options_values_id is the one generated while inserting the product_options record


Next create or update the products_description (Zencart) Record:
JOYASTOC.CODI -> products_description.products_id
products_description.products_id
JOYASTOC.WDES -> products_description.products_name
JOYASTOC.WTXT -> products_description.products_description


Next create a products_options (Zencart) Record:
products_options.products_options_id -> it is a different id per language
products_options.language_id = 7


For each different record of JOYATIEN (Client), then
read JOYATIEN.NUME
search this ID in the Zencart table tiendas (custom table created by us)
update the following fields (the only way to create new records in this table is by adding those manually, the program only update existing records):
JOYATIEN.NOMB -> tiendas.nombre
JOYATIEN.DNIF -> tiendas.dni
JOYATIEN.DIRE -> tiendas.direccion
JOYATIEN.DIR2 -> tiendas.direccion2
JOYATIEN.COPO -> tiendas.codigo_postal
JOYATIEN.POBL -> tiendas.distrito
JOYATIEN.PROV -> tiendas.provincia
JOYATIEN.PAIS -> tiendas.pais
JOYATIEN.TELF -> tiendas.telefono
JOYATIEN.EMAI -> tiendas.email


For each different record of JOYAINVE (Client), then
read JOYAINVE.STOC (corresponds to products_id)
Read JOYAINVE.TIEN corresponds to (id_tiendas)
search those ID in the Zencart table inventario (custom table created by us)
if doesn't exists, then create a new one, else update these fieds
JOYAINVE.UNID -> inventario.disponibles
JOYAINVE.APAR -> inventario.reservados