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
  • 171 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by DaveAmour
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.