Excel querying Access using MS Query

[TryingMyBest]

Hi folks!

I've got my application working wonderfully and now have set up some reports in Excel. People want to be able to manipulate the data and I figured this was easiest...the users don't know how to create their own report within Access but are Excel literate.

My workbook has two queries (setup using Microsoft Query) back to the database...the data is returned to the workbook and then sorted within the workbook using Excel formulas.

This works fine all data is present and correct and the results are accurate.

HERE'S THE PROBLEM

My database keeps corrupting. The LDB file isn't being deleted after the last person exits the database. I downloaded the LDBUTILS from Microsoft and found that when a person accesses the database via the Excel workbooks the session is left in a suspect state and this is causing the corruption.

I know it's not a server permissions error because I created the folder and am the owner of the file. I have full control of the files and folder on that part of the server yet my own PC frequently leaves the database in a suspect state.

Does anybody know what I can do to stop this? Is there a piece of code I can add so that when the workbook is closed the database session is also ended cleanly?

Thank you in advance

Jo

 

[Pat Hartman]

I have never automated this process from Excel but similar problems within Access VBA can be avoided by specifically closing recordsets when they are no longer needed.

 

[TryingMyBest]

I have never automated this process from Excel but similar problems within Access VBA can be avoided by specifically closing recordsets when they are no longer needed.

Thanks for your time Pat.

I am aware that I need to close the recordset but since I don't use code to open the recordset I am a bit stuck as to how to reference it in order to close it using code. Does anybody know how to do this please?