You are probably writing more Microsoft Access programming code then you need to. This means wasted coding time, more code maintenance and possible debugging. This article applies to all versions of Microsoft Access, not just Access 2007, but many new features of Access 2007 allow you to do zero coding to do tasks such as bulk emails, form control resizing, scheduling tasks, date picking, formatting, etc.
I have reviewed tens of thousands of lines of programmers’ code, not just in Access 2007, and have found that many lines of code are being written in areas where much less code was needed to do the same job. Here’s the technique that will save much time.
Did you know that your data tables in Microsoft Access 2007 can be used to control your Access 2007 program and write code for you, if they contain metadata.
Metadata is data about data. You have actually been storing and using metadata since you started using Access 2007. When you choose a color for a form textbox label, you are instructing Access 2007 to remember this color and run program code that creates that color each time that label is shown.
Ok, here is an example of how you could save yourself from writing many lines of code by using metadata that you have stored in an Access 2007 data table.
Here’s an example of using metadata. You have some reports to be printed every Tuesday and some that need to be printed every Friday. You could write some Access 2007 code to print the Tuesday reports by writing:
If Weekday(Date) = 3 then ‘It’s Tuesday
DoCmd.OpenReport “Tuesday Report A”
DoCmd.OpenReport “Tuesday Report B”
DoCmd.OpenReport “Tuesday Report C”
DoCmd.OpenReport “Tuesday Report D”
DoCmd.OpenReport “Tuesday Report E”
End if
You would then write some more code for Friday reports:
If Weekday(Date) = 6 then ‘It’s Friday
DoCmd.OpenReport “Tuesday Report A”
DoCmd.OpenReport “Tuesday Report B”
DoCmd.OpenReport “Tuesday Report C”
DoCmd.OpenReport “Tuesday Report D”
DoCmd.OpenReport “Tuesday Report E”
End if
Now let’s write these 14 lines of code with one line 7 lines of code.
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(“Table Name Containing Metadata”)
Do Until rs.EOF
If ![DayOfWeek] = Weekday(Date) Then
DoCmd.OpenReport ![ReportName]
End If
rs.MoveNext
Loop
These 7 lines of code would not need to be changed or added to even if the 10 reports increase to 50 reports in Access 2007 or any other version of Access.
The Access 2007 data table would only need to store the report name and the day of week, just two fields of data. This means easy maintenance and no future code changes.
This technique can be used for running a set of queries with a specified sequence. Use metadata when linking to external data sources by including paths, file or table names, and specifications. You can automate importing or exporting data by storing import and export specifications, formats, and storage paths.
Want more examples of this time saving approach? Let me know. Feedback is always appreciated.
AUTOPOST by BEDEWY VISIT GAHZLY
