Member Avatar for Andrew de Beer

Hi All

I use MS Access to import information from Excel, check for duplicates and post it to an Access data table. The amount of data is making this process extremely slow. I am attempting to move the data tables to MySQL and then running a Stored Procedure to do this task as I believe it should be a lot faster. I am finding it difficult to write the code for the SP. In MS Access the query looks like this:

*INSERT INTO tbl_Costs ( Type, Reference, Description, BusArea, CostCentre, Tx, DocumentNo, Account, Period, CompanyCode, Username, SalesDoc, Item, DocDate, Amount, PostingDate, BatchNo, BatchDate, Uniek, [Select], Duplicated, Cancelled, InActive, JobCardNo, Uniek )
SELECT tbl_ImportCosts.Type, tbl_ImportCosts.Reference, tbl_ImportCosts.Text, tbl_ImportCosts.BusA, tbl_ImportCosts.[Cost Ctr], tbl_ImportCosts.Tx, tbl_ImportCosts.DocumentNo, tbl_ImportCosts.Account, tbl_ImportCosts.Period, tbl_ImportCosts.CoCd, tbl_ImportCosts.[User name], tbl_ImportCosts.[Sales Doc], tbl_ImportCosts.Item, tbl_ImportCosts.[Doc Date], tbl_ImportCosts.[Amt in loc cur], tbl_ImportCosts.[Pstng Date], tbl_ImportCosts.BatchNo, tbl_ImportCosts.BatchDate, tbl_ImportCosts.Uniek, tbl_ImportCosts.[Select], tbl_ImportCosts.Duplicated, tbl_ImportCosts.Cancelled, tbl_ImportCosts.InActive, tbl_ImportCosts.JobCardNo, tbl_Costs.Uniek
FROM tbl_ImportCosts LEFT JOIN tbl_Costs ON tbl_ImportCosts.[Uniek] = tbl_Costs.[Uniek]
WHERE (((tbl_Costs.Uniek) Is Null));
*
Please assist.

Best Regards
Andrew

  • 2 Contributors
  • 1 Reply
  • 183 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by DaveAmour

Dani AI

Generated

This thread shows the right approach: move the heavy work out of Access and do a set-based import into the SQL server (MySQL in this case) rather than row-by-row processing in VBA. A few focused checks and adjustments will solve most problems and give the speed boost sought by .

  • Make sure the destination key is enforced. Add a UNIQUE index on the Uniek column in tbl_Costs. That lets MySQL reject duplicates at insert-time and makes existence checks fast. With a unique index you can use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE as a simple, high-performance dedupe strategy instead of per-row lookups.
  • Use a staging table + bulk load. Export the Excel data to CSV (or let the app write into a staging table), LOAD DATA INFILE into that staging table, then perform a single set-based insert from staging into tbl_Costs. Avoid cursors and loops in a stored procedure — do the work in one or a few set operations.
  • Verify schema and names before importing. Confirm the INSERT column list exactly matches the SELECT list (the Access SQL in the original post appears to repeat Uniek — ensure that is fixed). Rename or quote columns that contain spaces or reserved words (for example Select, User name, Doc Date) so the migration does not break. Convert Access data types to sensible MySQL types (use DECIMAL for amounts, DATE/DATETIME for dates) and normalize date strings if needed.

Performance and safety tips: run the flow inside explicit transactions for consistency, but do bulk inserts in chunks to avoid long locks; use EXPLAIN to check join/index usage; test on a copy of the database. To ’s point, the same ideas apply on SQL Server — the key is set-based bulk operations plus proper indexing, not which RDBMS is used.

Member Avatar for DaveAmour

So have you move everything to SQL server - ie no more Access and Excel at all?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.