I've managed to successfully setup my first Access database.
I have imported data from Microsoft Excel into an Access Database and Table within that (EmptyHomesTable).
The data relates to empty properties and every month at work we receive a list of empty homes in the month. Every month, this new data will be imported into a TemporaryImportTable.
I am then running an update query to compare the data in the EmptyHomesTable with TemporaryImportTable and 'close' those which are no longer empty (i.e. update their status in the EmptyHomesTable if the account reference number doesnít match).
I am then running an append query to compare the data in the EmptyHomesTable with the TemporaryImportTable and add any new empty properties (i.e. add those which arenít in the EmptyHomesTable by looking at the account reference numbers and adding them if they donít match).
This gives us a working database of empty properties but doesn't delete those which are no longer empty (rather they are marked as closed).
What I'm trying to do is to run a Macro to automate all of this on a monthly basis.
Macro is as follows:
1. Delete Query to delete the data in the TemporaryImportTable but keep the table structure;
2. TransferSpreadsheet to import the latest Microsoft Excel file into TemporaryImportTable;
3. Update Query to close properties which are no longer empty in EmptyHomesTable;
4. Append Query to import those new empty properties in EmptyHomesTable;
The macro almost runs fine but I have a couple of questions to help finish it:
a).Iíve run the macro to update the February list to the January list which works fine. Running the macro to update the January and February list (combined) is almost fine but Iím 2 entries out. I canít manually check as weíre talking about 1,500 entries. Is there another way?
b). Is there any way for the TransferSpreadsheet query to ask at each time of running the macro for the location of the Excel spreadsheet or do I need to go into the macro every time and change the file location?
c). One of the fields in the table is empty date (i.e. the date the property became empty). Is there a quick way to filter the entries before a certain date (i.e. only show those empty before 30 September 2007 for example)?
Go to your macro, right click it and save as > module.
Fantastic, now you can see the code you're toying with right there and then AND you get to utilise this wizardy called error handling
a) I don't really understand; have you tried querying for the results?
b) Yes there is, but not through a macro. There's something I posted in the code bank a while back to do with the FSO (File System Object) that allows such functionality - ask if you have any questions regarding the code there!
c) Again, queries are your friend! Except, if the date is empty, how do you know it was before 30th Sept?
Thanks for your help and apologies for not explaining myself very well!!
a). Every month I import new entries of empty homes, close those which are no longer empty and do not import those which are duplicated. That basically gives me an up-to-date list of empty properties. This is what is on the Excel sheet (which I import from) so the number of 'live' entries on the Excel sheet should match the number of 'live' entries in Access. However, after importing the March list, I had 1,790 in Excel and 1,788 in Access. It appears that it has closed two entries which should really be 'live'.
b). Thanks for this, I'll have a look in the code bank;
c). One of the fields I import from Excel is the date the property became empty. What I want to do is to filter to find the number which have been empty for longer than six months (from a date I specify) - is there a way to do this?