Export all unique entries from MS Access to MS Excel and save as .xlsx files

  • Hi all,

    I've been handed a rather unwieldy Access database and an even more unwieldy process for carrying out a task in a new role. There is simply no scope whatsoever for either the database or the process to be changed further up the food chain from me. I basically get what I'm given. That much has been made pretty clear to me. Therefore the only option for me is to simply use what I've got but produce better results by changing the process.

    I need to find a means by which I can export all the records for every unique entry using a query in MS Access. The reason why I'm thinking it should come from a query is because the database table that contains the data I need also contains a ridiculous number of superfluous fields. I can do nothing about this. Therefore I'm hoping to write a query that produces an output, from which I can loop through all of the unique entries for the 'COMMCODE' field and export them to a particular folder as MS Excel files.

    I have searched the internet and tried out some code in MS Access to no avail. Most seem to want to look at recordsets but there are bound to be other ways to carry this out. Is there anywhere I can go to find out more about this? I have some code but it is, at best, incomplete and at worst cobbled together from 2 or 3 different internet solutions to similar queries.

    I have no particular experience in MS Access and feel more at home in MS Excel but I am open to writing and running the coed from whichever application gets the job done quickest and with the least fuss. I have had various run time errors that have crashed the code when I've tested it. I'm at a loss to explain what they mean but I am pretty sure that one of them is because I may not be the only user on the database when I'm running it which makes it difficult to test.

    Any help gratefully received as this is only phase 1 of a massive revision of the processes for which I am getting precious little help from the rest of the organisation.

  • Re: Export all unique entries from MS Access to MS Excel and save as .xlsx files

    So this is the code I'm working with. I have changed some of the fields and table names from the original code. Not all of them though as I couldn't figure out what one line was doing. Any help greatly appreciated:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!