Microsoft Access – Save programming time and cost with technology for writing less programming code

You may be writing more Microsoft Access programming code, then you need to. This means wasting coding time, maintaining more code and potentially correcting errors. 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 encryption for tasks such as group email messages, control form size, scheduling tasks, date selection, format, etc.

I have reviewed tens of thousands of lines of programmers' code, not just in Access 2007, and found that many lines of code are written in areas where much less code was needed to do the same task. Here is a method that will save a lot of time.

Did you know that spreadsheets in Microsoft Access 2007 can be used to control Access 2007 and write code for you, if they contain metadata.

Metadata is data about data. You have already stored and used metadata since you started using Access 2007. When you choose a color for the form text box label, you are instructing Access 2007 to remember this color and run the program icon that creates that color each time that label appears.

Well here is an example of how to save yourself from writing many lines of code with metadata that you store in Access 2007 spreadsheet.

Here is an example of using metadata. You have some reports to be printed every Tuesday and some need to be printed every Friday. You can write some Access 2007 codes to print Tuesday reports by writing:

If the day of the week (date) = 3, then this is Tuesday

DoCmd.OpenReport "A Tuesday Report"

DoCmd.OpenReport Tuesday B Report

DoCmd.OpenReport "Tuesday C Report"

DoCmd.OpenReport "Tuesday D Report"

DoCmd.OpenReport "E Tuesday Report"

End if

You will then write more code for Friday's reports:

If the day of the week (date) = 6, then this is Friday

DoCmd.OpenReport "A Tuesday Report"

DoCmd.OpenReport Tuesday B Report

DoCmd.OpenReport "Tuesday C Report"

DoCmd.OpenReport "Tuesday D Report"

DoCmd.OpenReport "E Tuesday Report"

End if

Now, let's write these 14 lines of code with one line of 7 lines of code.

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset ("The table name contains metadata")

Is even rs.EOF

if! (DayOfWeek) = weekdays (date) then

DoCmd.OpenReport! (Name report)

End if

rs.MoveNext

Knot

You do not need to change or add the seven lines of code even if the 10 reports are increased to 50 in Access 2007 or in any other version of Access.

Only the Access 2007 spreadsheet will need to store the report name and day of the week, only two fields of data. This means easy maintenance and no code change in the future.

This technique can be used to run a set of queries in a specific sequence. Use raw data when linking to external data sources by including paths, file or table names, and specifications. You can automate the import or export of 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.

&

    Leave Your Comment

    Your email address will not be published.*