IntroductionUnlike most of my posts to date, this has to do with some VBA code in an Access standard module.
Project DescriptionThis Access project has an Account table and every month we run a set of queries that links Excel worksheets, loads the data into the MonthlyBilling table, then updates the LastBillingYearMonth ("YYYYMM") field for the Account and Customer. A Customer may have more than one account.
The end-point is to update another Excel Workbook, using embedded queries, to show sales and billing trends for each customer, over-all trends, latest month sales for largest 10 customers, and a 12 month history of the largest customer.
Original Operations mode
- Receive the input files and save them to a specified directory
- Link using the Access UI. Linking was used instead of importing because the data was then loaded into a single table, each row stamped with the billing year and month.
- Resolve issues in the linking process, i.e. the providers decided on their own that they wanted to put an apostrophe in the Worksheet names, which caused an error when trying to link.
- Edit, with new month/year references, and run queries to add the input files to the MonthlyBilling table,
- Edit, with new month/year references, and run a query to add an Account if needed, i.e. the billing input showed an account number not previously billed and add a new Customer for the Account
- Manually review the new accounts (they aren't linked to Customer records at this time, linking each to either the new Customer or an existing Customer.
- Run a query to delete any Customers that had no Accounts
- Run queries to update LastBillYearMonth in Account, then Customer, tables.
- Open the "live" file from the previous month, (The "live" file has formulas and links/queries. There is also a "published" version with no formulas or links/queries.
- Refresh the queries.
- SaveAs with new date stamp, still marked as the "live" version,
- Copy-PasteValues for all worksheets.
- Disconnect data links.
- SaveAs, marked for publication.
- Close and distribute.
Improved Operations modeTo help with the front end of the process I wrote some code in a Excel "toolkit" that cleaned-up the worksheet names.
In the Access project file (the "live" file mentioned above), I translated the linking process and queries used above to DoCmd.TransferSpreadsheet and SQL statements, respectively. Using constants I would change once, I did the editing mentioned above, then ran the DoCmd/SQL.
Change to make
- Grab the max of LastYearMonthBilledResult1 = DMax("LastYearMonthBilled", "Account")
- Convert the yyyymm returned by DMax() into a real dateResult2 = Right(Result1 , 2) _ & "/1/" _ & LeftResult1 , 4)
- Add 1 monthResult3 = DateAdd("m",1,Result2)
- Extract the year and month Result4A = Year(Result3)Result4B = Month(Result3)
- Returns the max of LastYearMonthBilled
- Made stand-alone function because it is a piece of code already shown to have two uses and may have more.
- fn_NextMonthToProcess and fn_NextYearToProcess
- Both invoke fn_LastYearMonthBilled
- Are identical expect the former uses the Month() function and the latter, Year().
- Use variable LastYearMonthBilled so that function fn_LastYearMonthBilled is only invoked once.