Recent content by iqkh76

I am building a data consolidation tool in access. There are multiple Excel files from different vendors, each vendor has different format and for some years data the format keeps changing for the same vendor. I worked on a single vendor and created a unique field list for that particular...

Thanks Ranman, When I tried using Application.CompactRepair sSrc, sTarget, I am getting error "Microsoft Access can't compact and repair the current database"

I am uploading multiple Excel files through transfer sheet into Access table. After each upload of files, multiple queries are triggered to get the final data from the input file. Each file contains records on an average of 100,000 to 1,50,000. So since I upload all the files by looping files...

Can I split this data into multiple tables and run the query? So can I split this in 4 tables of 25,000?

The above data is sample data. Name matching is like 50% of the data does not get updated with exact match. I processed 200,000 records and of which 100,000 do not get updated with icode. Hence moved the records to another table and trying to use update query on that. 100,000 records is...

iMaster has list of codes for mName and I want to update the nearest first match to tmpMaster. Since first Pal has code ia123 and first Kal is id8987. I am already running a query to get the exact match, hence only want to update the one which does not matched exactly. iMaster mName iCode Pal...

I will try removing the index from the icode field. For the partial match this is something similar to vlookup with approx match which will pick up first record which matches something of lookup value, this is the requirement to update the field.

Thanks for your response. I made the changes of removing the * before the field name but still it is slow. Since I want a approximate match hence using Like. Also tried using % still no change. Please advise if I can do anything else to make this faster.

I am running the below query on around 100,000 records but it is taking ages, do not know why. UPDATE [tmpMaster] INNER JOIN iMaster ON [tmpMaster].[Name1] like "*" & iMaster.mName & "*" SET [tmpMaster].[iCode] = [iMaster].[iCode];