datageek

A discussion of what's new in Access 12

2006-02-13T14:45:00.000-05:00

A discussion of what's new in Access 12

If you haven't had a chance to read up on what's new and exciting in the next version of Office, here is a great blog covering the new features in Access 12. I have been using Office 12 Beta 1 for over a month now and can say this will be the most significant Office upgrade since the release of Office 97. Most importantly for developers, Access is getting a lot of attention this time around. Check out the Access 12 blog and start learning the new features now ... if you wait, there will be too much to learn.

Do you use Email efficiently?

2005-12-30T11:08:00.000-05:00

How many of you have over 1000 items in your inbox? Or have over 20 unread messages you have no intention of ever reading? There is nothing more annoying in the world of business than people who don't use email efficiently. It's not a toy! Used correctly, email can be one of the most efficient forms of communication AND organization. It can also be the most time consuming and frustrating tools available. This article does a GREAT job of outlining 50 ways to use email more efficiently.

Free Microsoft Virtual Labs

2005-11-15T09:02:00.000-05:00

SQL Server 2005 and Visual Studio 2005 have launched! To help developers get "ramped up" for the new features, Microsoft is offering a variety of free training opportunities. Most notibly, I think, are the virtual labs, where you can try out the software in a hosted environment - for free! I've done a few of the labs and think there is a ton of value in this type of training.

SQL Server 2005 Virtual Labs

Access your files from anywhere

2005-10-17T12:04:00.000-04:00

Ever been at work and needed a file from your home computer? Avvenu is a new service that allows you to have access to all your files from any computer with internet access. The only pre-requisite is that your home computer will need to be left on and logged onto the internet whenever you want to have access to your files. Best of all ... it's FREE!!

You might want to also check out another service I posted on a while back ... Logmein - you can access your computer from anywhere ... kind of like PC Anywhere used to work - and it's FREE as well.

Access Day

2005-10-17T08:58:00.000-04:00

If you have ever attended a user group meeting, you know how useful and informative they can be. The Boston area has a user group for Access that meets the third Wednesday of every month. Each meeting provides demonstations, tips & tricks, and troubleshooting for all levels of Access users. I have been contemplating starting something like this at VTEC, but would need to know that the attendance would be consistent to make it worth putting forth the effort in gathering speakers, putting together presentations, and scheduling and organizing the meetings. If this is something that interests you, please drop me an email or post here. Please give me some ideas of topics you'd like to see. Check out Access Day in Boston for some ideas (and useful links/examples).

Access Add-ins

2005-08-04T11:09:00.000-04:00

FMS Inc. has been providing high quality Access, SQL Server, and VB add-ins since 1992 and has recently updated all their tools to support Access 2003. I have personally used all their tools and highly recommend them. While these add-ins certainly aren't cheap, they will save you hours of development and provide much more functionality than the built-in controls.

Check out the tools here.

Recover corrupt Access databases

2005-08-04T10:48:00.000-04:00

Tony from aadconsulting.com has created a new freeware utility that helps recover corrupt Access databases. Access Database Mechanic uses the following three methods: compact & repair, decompile, and completely rebuild an .mdb. If you've ever had the sick feeling of trying to open a corrupt database, this tool will definitely be something worth hanging onto.

Treo 650

2005-06-21T13:30:00.000-04:00

My job entails me being away from the office on a regular basis. I've been using an iPaq as my pda for several years. As technology has advanced over the years, I've been constantly looking for a SINGLE device that I could carry to replace my pda and my cell phone. Most of the reviews I have read on these devices are that they do ONE function (cell or pda) very well and provide the basic functionality of the other. Verizon recently release the Treo 650, so I thought I would give it a shot to see if it would be a useful replacement.
At first, I must admit, I was ready to throw this device out the window. However, after some growing pains and learning the quirks (MANY!!) of the Palm OS, I've decided to stick with it. I don't know that I would call it a SMARTphone but it has become an essential tool. While I much prefer the Pocket PC operating system, the Pocket PCs were MUCH too pricey for me to consider. It certainly isn't perfect, but having always-on access to the internet, email, calendar and contacts (which wirelesssly sync to Outlook - thank to free software from Verizon) has made the 650 invaluable.

Choose Function

2005-06-21T13:19:00.000-04:00

It's been a while since I've come across a new VBA function that I haven't used before AND find useful. The Choose function has similar functionality to the VLookup function in Excel. If you need to lookup a value but can't justify storing the values in an array, table, or lookup list, then the Choose function might be a good alternative.
Syntax

Choose(index, choice-1[, choice-2, ... [, choice-n]])

I recently used this in a database I was working with a database which tracks driver information for a trucking company. The driver classification was one of the fields in the database (not designed by me). I wanted to hide the classifications in code, so that some relatively savey Access user couldn't alter the 'lookup' field. So, I started to build my own lookup when I found the Choose function. I used it as the control source of one the driver classification text box on a form:

=Choose([class],"Experienced","Veteran","Suspended")

If the [class] had a value of 1, then the text box displayed "Experienced", 2 displayed "Veteran", and 3 displayed "Suspended."

I used the Choose function so that I could hide the three values in code and because I didn't want to add another table to the database to store just three values (which SHOULD never change). One other side note was that I knew that the [class] field was only being displayed on one form, otherwise I would have made my own Public function to display the results of the Choose function.

Putting some ZIP back into Windows

2005-04-18T18:34:00.000-04:00

There are several services that start automatically with Windows XP. Many of these services are never used and should be disabled to gain performance. Windows IT Pro has a great article that outlines the most common services that can be disabled.

Unneeded Services in Windows XP

Stumbleupon: Find interesting websites

2005-03-09T10:39:00.000-05:00

Mozilla Update :: Extensions -- More Info: stumbleupon

I'm always looking for cool new websites that I can waste my time reading. The problem is, how do you find new sites? Unless one of your friends tells you about it or you read an article from another site about it.

Using one of Firefox's newest extensions I have found a very simple way of broadening my surfing world. Stumbleupon asks you a few questions about the types of sites you like to read, then randomly takes you to sites that match your categories. A rating system has been implemented to make more popular sites appear first. You can even mark sites you like and Stumbleupon will remember your favorites. Check it out!

Basing one combo box on another

2005-02-28T00:38:00.000-05:00

This technique is very useful in applications where you want to allow your users to choose multiple data from combo boxes - when you want one combo box to display records based on what the user selected in the first combo box.

Microsoft has put together a very small sample database of how this can be accomplished.

WinZip - Outlook Companion

2005-02-25T11:12:00.000-05:00

Winzip has long been the standard tool for compressing files and folders. With Outlook Companion, Winzip is offering tighter integration with Outlook.

From Winzip's website: Zipping attachments saves transmission time, conserves space in Outlook mailboxes, and saves disk space for both the sender and recipient of the e-mail. The Companion can be configured to automatically zip your attachments, ask whether or not you want them zipped, or let you zip and attach files manually with just a few mouse clicks. Toolbar and menu items allow you to control the Companion's functionality on a message-by-message basis. In addition, sensitive attachments can be easily protected with the same built-in advanced AES encryption found in WinZip 9.0

Benefits

Easily zip attachments when sending Outlook e-mail messages
Conserve space in your Outlook mailbox
Save disk space
Save transmission time
Protect sensitive attachments with encryption
Easy-to-use; just install it and it is ready to work for you

WinZip - Companion Product Information

Problem with record selection and left joins

2005-02-25T10:49:00.000-05:00

Many report writers begin to explore table joins in Crystal Reports when they aren't getting the results they expect. While changing a join from an inner to an outer is sometimes the easy solution, it may not always yield the results you expect. Imagine you want all your customers in a report, whether they had transactions or not. Where they do have transactions, you are only interested in a selection of transactions

To illustrate with a simple example, let's look at a customer table and a transaction table of orders.

This one-to-many table relationship is common to many reports. This is a problem we have seen on many occasions. There are several solutions you might like to consider if your report has this problem. The default Inner Join will give us only those customers who have orders. To get ALL customers and their orders if they have any, you would use a Left Outer join. Under normal circumstances, the Order table fields will be NULL for those customers without any orders. And that is the foundation of the problem we are about to describe.

Usually, you don't want all the records in the transaction table. At a client site recently, we only wanted the transactions from a selected date range. But we still wanted to see all the customers on the reports. Because we are doing a selection on the date field in the orders table, we restricted our customer selection to only those customers who had orders in the selected dates. You might suggest that those customers without any orders will have NULL in the fields in the order table, so select the NULL values as well. Sure, that will pick up customers who have NEVER had any orders. But customers who have orders, but not in the date selection are still missing from the report. This is because the LEFT join is done in the database prior to record selection. We need a solution that shows all customers, and their orders in the date selection (if they have any). There are several solutions to this. Thanks to Tim Wilson for his help investigating these.

Brute Force Solution

Use a formula on all records After examining and trying some of the other methods, this was the solution we implemented. We expected a performance hit, but the report was fast and gave us the results we wanted.

As faster and faster processors are delivered it is time we used some of that extra power for more productive purposes than 3D helicopter games. We joined the two tables with a left join, but with no selection from the orders table.

Yes, all the transactions were loaded into the report and had to be processed. We calculated our “This Month” total using a summary of an If .. Then .. Else formula.

A Running Total Field would also work fine here. You might try and avoid a report that loads millions of records into one report. But Crystal can handle the volume without any problems. This solution performed well and worked with our CR 8.5 user.

Stored Procedure Solution

We looked at the previous problem from the database side. If Crystal wasn't part of the solution, how would we do this using SQL? The raw data tables won't do what we require, so we need an SQL stored procedure.

* We looked into using SQL to create the left join we needed, and it just didn't happen. If any of you SQL experts out there have a pure SQL solution for this, then let us know.
* If only summary data are required, a sub SELECT would work. But if you need the transactions as well, it is not enough.
* A UNION join also looked like it might provide a solution. With a stored procedure we could select the required transactions from the orders table. Then we could use Crystal Reports 9 or 10 to left join the stored procedure to the master table. But this solution is not available for CR8.5 or earlier and has a big performance hit.

Business View Solution

Business Views are a new feature in Crystal Reports 10. We created a SQL command with the date selection parameters inside a Business Foundation. Left join that to the customer table, and create the required Business Elements and Business Views.

This worked fine in our Crystal Reports 10 test report.

SELECT INTO Solution

One stored procedure solution we thought would work was to use a stored procedure with several steps. The first step did a SELECT INTO to create a temporary table with the only the records we needed, and then it left joined the temporary table to the master table. This would work with any version of Crystal Reports.

The negative side of this solution is that the report can only be run by one user at a time. A second user would reset the temporary table while the first was still using it.

Subreport Solution

Back into Crystal Reports with a main report from the master table, and a subreport for each transaction record to get the data we required. This did work correctly.

But the performance and selection of the transactions for each master record made this the slowest performing solution of any we implemented. Also, you could not do any grouping or primary record selection on fields in the transaction table.

Conclusion

Use the solution that best suits your needs. Each of the above has specific technology and performance limits. But we now have several ways to get that report with the data we want on it.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.

Retrieving random records with a query

2005-02-24T09:31:00.000-05:00

I've had several students in the past ask about getting random results from a query, so they could review/analyze random data from there tables. For instance, one student was working with a customer service database - tracking calls and solutions provided to customers. Once a week, one person was in charge of calling random customers to get feedback on how the customer service was handled and if the customer was satisfied with the service. The employee would open the database and manually look for random customers to call. This solution provides an easy way to build a query - which could then be formatted with a report - to automatically build a list of any number of customers randomly.

Type the following expression into an empty column in the field row of the query design view:

RandomValue: Rnd([field])

where [field] is any field in the table that contains numeric values - the field can be an AutoNumber, Number, or Date/Time field. Apply a sort order to the RandomValue field, ascending or descending. Use the Top Values dropdown list on the Query Design toolbar to specify how many records to return. If you want a number or percentage that isn't listed, simply enter the desired amount in the Top Values text box. When you run the query, you'll find that Access returns a random selection from the underlying record source.

Outlook reminders sent to your mobile phone

2005-02-23T11:50:00.000-05:00

I'm always forgetting stuff. Meetings, appointments, tasks, etc. I forget them all. I don't want to look like batman with a utility belt so instead of carrying a cell phone and pda, I wish Outlook could send a text message to my phone as a reminder if I'm away from my desk. This great little utility - although it's not free - makes a great add-on for Outlook.

OutlookSMS

Add bullets to Access reports

2005-02-17T16:43:00.000-05:00

Bullets are useful to draw the reader's attention to a particular item, displaying lists, or even separating address parts in the footer of a report (similar to most company letter head). You can add a bullet to a report in Access.

Open your report in design view.

Turn on the Number Lock.

Insert a label and type the text you want in the Caption property. When you want to insert a bullet:

Hold the alt key down and--

For a round bullet, type:

0149

For a hyphen bullet, type:

0150

Release the alt key.

Crystal Reports XI Released

2005-02-16T14:04:00.000-05:00

Business Objects has just released the next version of Crystal Reports 11 and Crystal Enterprise 11. Unlike the upgrade to version 10, the upgrade to version 11 has some significant improvements for report designers. Here are the ones I feel are most significant (follow this link to see all new features):

Dynamic Parameters:
Crystal parameters can finally generate the list of default values at runtime. The 'pick list' is no longer static.

Crystal Reports allows you to generate the 'pick list' directly from your data. You can specify a field in the report and Crystal will query the database at runtime for current values. You can even create a command (SQL statement) to generate the list.

Cascading Parameters:
You can now have one parameter prompt determine the list in another prompt. For instance, when trying to select an employee you could first select a department. Then Crystal would show you a list that only included employees from that department.

I think Business Objects was finally feeling the pressure from Microsoft's SQL Server Reporting Services. I expect this new rivalry to really benefit developers as the business intelligence tools gain more and more features.

Microsoft Access Limitations

2005-02-16T13:42:00.000-05:00

Many times in class students will ask when they should upgrade to a server product like SQL Server for their databases. Access does have limitations; this list can be useful when deciding whether to use Access or some other database.

Access table limitations:

Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048. The actual number may be less because of tables open internally by Microsoft Access.
Table size 1 gigabyte
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface;
1 gigabyte when entering data programmatically.
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields) 2,000
Number of characters in a field property setting 255

General Access limitations:

Microsoft Access database (.mdb) file size 2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255

Technology News: Security: Microsoft Beta Seeks and Destroys Spyware

2005-01-07T11:37:00.000-05:00

Technology News: Security: Microsoft Beta Seeks and Destroys Spyware

Looks like we can expect even more software from Microsoft in the near future to be dealing with malicious software. I expect this to be a huge step forward for Microsoft, simply because of the integration with the operating system.

Microsoft SpyWare Beta

2005-01-06T17:21:00.000-05:00

Microsoft has entered the SpyWare removal business! If you have been looking for a good source for reliable spyware removal, I would suggest checking this out. I just tried it and was very impressed with the speed, but I haven't really had a chance to compare what it found/didn't find to other leading removal programs.

Microsoft Spyware Beta

LogMeIn - Remote Access Software

2004-12-08T23:02:00.000-05:00

LogMeIn - Remote Access Software and Remote Control Software

I LOVE free stuff. As an MCT, I've had a free 1 year trial to GoToMyPC - a really cool and easy to use remote access software. I've had it installed on my home computer and can easily access it from ANY java enabled web browser. Unfortunately, my year is almost up. So, I've been looking for reasonable alternatives (GoToMyPC is 20 bucks a month!!!). I think I've found it ...

LogMeIn took me all of 5 minutes to install and get configured ... and it seems to work very well - especially for free. If you have the need to access your home computer from work or vice versa you should definitely check this out!

Threat profiliing Microsoft SQL Server

2004-12-07T11:00:00.000-05:00

Threat profiliing Microsoft SQL Server

This document is an excellent overview of the security vulnerabilities with SQL Server. This is a must read for any dba out there that is trying to decide which authentication mode to use.

Personally, I rarely recommend using SQL Server Authentication. I've seen developer's tell their dba they MUST use SQL Server Authentication for programming - only because all the examples you see in books and online use "sa" with no password. I am shocked at the number of systems I have seen setup this way. Don't fall into this trap. Take the extra time to learn how to use Windows Authentication via code. This article should open your eyes to the real threats.

Pro SQL Server Reporting Services

2004-11-30T14:51:00.000-05:00

Just got this new book on Reporting Services ... VERY good book. If you are just starting out or need help with some good examples, I highly recommend this book.

Stored procedure to find all instances of a field

2004-11-30T14:36:00.000-05:00

As a report writer, database administrator, developer or user, it may be useful to know which table a field exists in. Many third party vendors don't supply a data schema, making it difficult to query and build reports. This stored procedure is a simple method of searching through all the table in the current database for a field that you specify.

While you certainly cannot assume that because two tables have the same field that they are related, this will certainly be at least one tool you can use to help find those relationships.

CREATE PROCEDURE dbo.sp_FieldInfo
(
@Column_Name nvarchar(384) = NULL
)
AS
SELECT Object_Name(id) as 'Table Name',
rtrim(Name) as 'Field Name'
FROM syscolumns
WHERE name like @Column_Name
GO

Then to call this proc:
sp_fieldinfo employeeid

Email attachment limits

2004-11-24T15:21:00.000-05:00

What a pain it is when you want to email someone a large file only to find out Hotmail, Gmail, Yahoo, or any of the other numerous email providers don't support large attachments - most only allow up to 10mb. 10MB?? With today's focus on media (pictures, movies, songs, etc.) - how can I email a bunch of pictures in one big zip file?

Yousendit has started a service which will allow you to email large attachments to somebody - up to 1 GIG!! THAT'S more like it! Using the yousendit website, you upload your file, enter the email addresses you want it sent to, a link is sent to them via email, they click on the link to download the file. Your file is stored for seven days, then deleted. VERY COOL!

Office Developer Center: VBA Language References

2004-11-16T23:54:00.000-05:00

Office Developer Center: VBA Language References

Microsoft has recently published the language references for all of the Office 2003 applications. You can view them online or download them.

If you are a VBA programmer, you should download these useful references.

Blogging on Visio

2004-11-16T23:45:00.000-05:00

Blogging on Visio

Just found another great blog with very useful tips and techniques using Microsoft Visio. For some strange reason, Visio seems to have very little documentation and there aren't many Visio books available. If you see other Visio sites or have any books to recommend, please post in the comments - thanks!

Microsoft Photo Story 3 for Windows: make show-n-tell cool again

2004-11-04T14:21:00.000-05:00

Microsoft Photo Story 3 for Windows: make show-n-tell cool again

If you are into photo editing or you share your photos with friends and family - you gotta check this out! Microsoft Photo Story 3 - it's free!!!

Bring life into your favorite memories with Photo Story 3 for Windows by adding motion, effects, music, and more to your digital photos. Easily retouch your images with a single click and add slick-looking titles, or add dramatic pans and zooms that give your creations a professional finish, create a soundtrack - even record narration. Then enjoy the show. Start sharing your stories today!

Retrieve current username from windows using VBA

2004-11-04T13:06:00.000-05:00

Ever needed to retrieve username of the person currently logged into Windows? Using VBA in any of the Office products, the following code will return the current username. Great for implementing your own security or auditing.

' Makes sure all variables are dimensioned in each subroutine.
Option Explicit

' Access the GetUserNameA function in advapi32.dll and
' call the function GetUserName.
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

' Main routine to Dimension variables, retrieve user name
' and display answer.
Sub Get_User_Name()

' Dimension variables
Dim lpBuff As String * 25
Dim ret As Long, UserName As String

' Get the user name minus any trailing spaces found in the name.
ret = GetUserName(lpBuff, 25)
UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)

' Display the User Name
MsgBox UserName
End Sub

Library of Free Data Models

2004-11-03T14:07:00.000-05:00

Library of Free Data Models

Reading the news groups today, I came across this little gem of a website. If you are just starting out with database design or need help coming up with a data model, this website gives some EXCELLENT examples of well designed databases.

Remind yourself to send attachments with Outlook

2004-10-26T14:36:00.000-04:00

Ever forget to send an attachment to someone? The following code will search your outgoing message text for the word "attach." You can add as many words as you want. The code needs to go in the ThisOutlookSession module in Outlook VBA; make sure macro security is not set to High.

Private Sub Application_ItemSend(ByVal Item as Object, Cancel as Boolean)
If Instr(1, Item.Body, "attach", vbTextCompare) > 0 Then
If Item.Attachments.Count = 0 Then
MsgBox "You forgot the attachment."
Cancel = True
End If
End If
End Sub

For more examples of VBA using Outlook, check out Sue Mosher's site OutlookCode.

Microsoft Access Performance Considerations

2004-10-26T10:17:00.000-04:00

While Access is certainly NOT an enterprise solution for databases, it can certainly provide small to medium size businesses with a very valid solution for their database needs. It's very common to read discussions about how Access cannot handle a multi-user environment or is too slow to use across the network. Most of the time, I find there are major design flaws with the databases these people are using. For most developer, there a few simple configurations and code tricks that can be used to attain reasonable performance from Access.

First, it is VERY possible to create a successful client/server application using linked tables for up to 20 concurrent users. But there are lots of details you must fix to be successful. To get good performance, it's all about keeping the "wire" cool -- that is, making sure you minimize the amount of data you ask JET to fetch and the number of threads you have open to the file server.

One of the most insidious "wire" eaters in Access 2000 and later is the silly Subdatasheet feature. If you leave SubdatasheetName set to [Auto], Access opens an extra thread AND fetches the rows for any recordset built on the table. You'll see a dramatic improvement in performance if you go to all the tables in the data file on the server and set this property for each table to [None].

You must also design your application to minimize the data fetched to the client machine. It is NOT true that JET on the client always does a full table scan. If you apply a filter on an indexed field, JET first fetches the index blocks and then sends specific requests to the file server for the data pages it needs to satisfy the request. So, it's important to both define appropriate indexes on your tables AND to NEVER request "all the tea in china" by opening a form or report unfiltered.

You should design custom "query by form" forms into your application that prompt the user to focus on the records needed for the task at hand, and the QBF code should refuse to proceed unless the user specified at least one filter value. You should also avoid any combo boxes that return more than about 200 rows. Although Access does try to optimize the combo box row source by not fetching all the rows until the user types in the box or drops it down, fetching 1000+ rows in a combo box is a performance killer. Most successful applications provide a way for the user to filter the combo first - for example, by selecting a product category that then filters the product name list. One alternative is to write code in the Change event to lookup the closest value for the user as the user types the entry they want. Single probes via DLookup are much more efficient than fetching the 1000+ row recordset.

In applications that might release the last active thread by returning to an unbound switchboard, performance can be improved by keeping one recordset open in code as long as the application is running. When you close the last bound form or report, Access releases the thread to the server - and if someone else has the data database open, it can take seconds to reopen the thread. If you keep a thread open, other processes in the application will share the open thread.

For reports, consider fetching the "unnormalized" report data to a local table and bind the report to that. Depending on how your report is designed, the report engine can end up fetching all the data from the server four or five times before it displays the result. Also, if you open a large report in Print Preview and the user pages back and forth in the result, the report engine will fetch the data several times again.

Be advised that "upsizing" to SQL Server is not a panacea. If your appication is badly designed for client/server, it won't run any better on SQL Server, and might actually run worse. You should upsize only when you need to manage more than 200 meg of data, you need true security, or you need to support more than 20 simultaneous users.

As for lookups - don't use them, at least not in table definitions. Check out my previous discussion for more information.

The Access Web - The Evils of Lookup Fields in Tables

2004-10-26T10:13:00.000-04:00

The Access Web - The Evils of Lookup Fields in Tables

Lookup fields are a hot topic for debate, that's for sure. If you frequent Access newsgroups or discussion forums, you've probably seen at least one heated thread about whether developers should use lookup fields in their tables. Personally, I tend to agree lookup fields should NOT be used. The misunderstanding most people have regarding this stance is that they believe the functionality of lookup fields is lost. Not true.

Unless you have a *rock solid* understanding of relational theory and the detailed structure of your database, it is a very bad idea. It gets you very little - it makes your table datasheet view more human readable, at the cost of concealing the actual contents of the table, and makes sorting and searching the table more difficult; it also makes it a tiny bit easier to put combo boxes on forms instead of textboxes. But I advise people never to use this misfeature; just put the combo boxes on Forms, which is what you should be using to look at your data anyway. Table datasheets are for debugging; and as such they should display what they actually contain rather than concealing it.

You can very easily store your tables with the actual data that they contain visible, and use a Query or a combo box or other control on a Form or Report to retrieve data from the linked ("lookup", if that's what you want to call it) table. What I object to is the perverse idea that you should define a lookup *FIELD* in your main table, and use it in your table datasheet. This accomplishes absolutely nothing other than to conceal the actual contents of your table, cause confusion about the relational structure of your data, and mislead users into trying to do data entry and searching in table datasheets instead of properly doing so on Forms.

It's perfectly easy to put a Combo Box - a "lookup", if you want to call it that - on a Form. The table lookup wizard makes this the default, but at the cost of concealing the actual contents of your table; encouraging you to use table datasheets for data entry, almost never a good idea; creating a new relationship between the main table and the lookup table even if such a relationship already exists; and adding redundant indexes to your table. If you leave the tables alone, and use a Form to do your data entry, you get the best of both worlds. Use table datasheets for debugging and design ONLY, and use the Form and the rich kit of tools that the Form provides for your data entry.

For more detailed information, follow this articles link to the MVPs Access site.

Filter a report using a form's filter

2004-10-23T13:07:00.000-04:00

There is a relatively simple way of filtering a report by using the filter property of a form. This allows the user to filter any field on the form and then print the displayed records in a report. Now, instead of creating complex queries or multiple controls on a criteria form, you can let the users decide how to filter the report.

This form is based on the customers table from the Northwind sample database. The country field has been filtered to show only those customers located in Germany.

Now, place the following code behind the Preview Report button's On Click event:
DoCmd.OpenReport "rptCustomers", acPreview, , IIf(Me.FilterOn = True, Me.Filter, "")

Now, once the user clicks on the the Preview Report button, the report uses the filter property of the form, if a filter has been set.

This report is now filtered using the same filter used on the form.

Lookout email search for Microsoft Outlook

2004-10-19T14:35:00.000-04:00

Lookout email search for Microsoft Outlook - Lookout Software

Ok, one more on the software side of things - search Outlook for all those emails, contacts, and calendar items you thought you had lost!

You can use Lookout to search your:

Email messages
Contacts, calendar, notes, tasks, etc.
Data from exchange, POP, IMAP, PST files, Public Folders
Files on your computer or other computers
... your wife's purse (okay, not true)

This software was recently acquired by Microsoft and is tightly integrated with Outlook. Definitely worth checking out if you live and breath Outlook.

Google Desktop Beta

2004-10-19T13:52:00.000-04:00

How many times have you thought it would be great to be able to search your desktop as easily as Google searching the web?

Find your email, files, web history and chats instantly
View web pages you've seen, even when you're not online
Search as easily as you do on Google

Google Desktop Beta

Ad Ware Removal and Detection

2004-10-19T13:26:00.000-04:00

Lately, spam has taken a back seat to Ad Ware. What is Ad Ware and Spyware? Spyware and Adware is software made by publishers that allow them to snoop on your browsing activity, invade your privacy, and flood you with those horrible popups. If you are like most users on the internet, chances are you are probably infected with these applications. Unfortunately, most novice users don't know how to remove these pesky softwares. I've compiled a list of the software I recommend that can help you rid your computer of Ad Ware and Spyware.

While these programs are free, Ad-Aware has a professional version which does real-time monitoring - sort of like most firewall software. The free version is sufficient, but the professional version has some great tools that make it worth the upgrade price.

Ad-Aware
Spybot
HijackThis

These three are consistently the best reviewed in their categories, Spybot being the most prevalent. HijackThis uses a slightly different approach than Ad-Aware and Spybot. HijackThis scans your hard drives and registry for commonly used attack methods - not specific URLs and registry entrys like the other two. I would recommend loading all three. Several articles I have read indicate that Spybot and Ad-Aware will detect some entries the other doesn't.

More information and reviews can be obtained by reading:

CNET Spyware Reviews

Spybot Overview

What is XML?

2004-10-18T13:54:00.000-04:00

Microsoft Office Assistance: XML for the uninitiated

XML for the uninitiated

A relatively common question in class these days - what is XML? Microsoft has provided support for it in most of their products, including, Office and SQL Server. XML really has revolutionized data distribution across the web. This article gives a very good overview in non techy terms.

Free Virus Protection

2004-10-16T13:06:00.000-04:00

One of the most common "excuses" I hear about not using virus protection is that it costs too much. After you buy the software, there are usually subscriptions you must pay for to keep your software up to date - preventing new viruses from attacking your computer. I have found this AVG Anti-virus to be one of the best virus protection programs available - and it's free for personal use.

What is RSS?

2004-10-16T13:05:00.000-04:00

RSS is quickly becoming a new standard for reading news sites, blogs and other syndicated web sites. It's like a customized news reader. Click here to read an article on CNET that gives a good overview of RSS, how to use it, and what software you'll need to get started. Don't forget ... most blogs have a RSS feed. Add the link below to your RSS reader to receive the feed for this site.

http://dbageek.blogspot.com/atom.xml

I have found Pluck to be the best RSS reader. Best of all, it's FREE!

Recommended Books

2004-10-16T10:45:00.000-04:00

Here is a list of books I recommend for database development.
Access

SQL Server and Programming

Crystal Reports

XML

XML In Easy Steps - Hard to find (only at Barnes & Noble stores - not online)

Access Articles

2004-10-14T23:18:00.000-04:00

Below are a list of useful links for Access classes by level, as well as, general Access articles.

Access 97 Expert Solutions - a little dated, but still very useful information; this is an online book.

Access Level 3:

Guide to Access database corruption

General Microsoft Access tips, tricks, and articles.

Access Performance Considerations

More Performance Tips

Should lookup fields be used?

Filter report using form's filter

Override shift key in Access

Disable prompts when sending Outlook

Green-bar reporting in Access

A more complete datediff function

Calculating first and last day of the month

Commonly used naming conventions

Retrieve current username from windows

Deleting all data from Access database

Microsoft Access technical limitations

Add bullets to Access reports

Retrieving random records with a query

Basing one combo box off another

Migrating Access Databases to SQL Server

Recover corrupt Access databases

Access Add-ins

Override the Shift key in Access

2004-10-05T10:25:00.000-04:00

Using the startup properties in Access, the database window can be hidden when a database is open. This is usually turned on for most production databases. When the developer needs to make changes, the shift key is used when opening the database to override all startup options. This is a handy feature, but also allows other not so savy users to do the same. The AllowByPassKey property can be used to "password" protect this feature by enabling or disabling the shift key. However, because of the potential of locking yourself out of a database, Microsoft has not created this property by default.

The AllowByPassKey property is explained in detail in the VBA help file that comes with Access. However, the sample code provided in the help files does not use the fourth DDL argument when making a call to CreateProperty. This means that anyone who can open the database can programmatically reset the AllowBypassKey value.

Therefore, in order to restrict the change capabilities to only the Admins, set the fourth argument to True when calling CreateProperty. And don't lock yourself out!

Below is the code that can be used to create this property and some comments on how to use it. Check out the help file for more detail.

'*********** Code Start ***********
'This code was originally written by Michael Kaplan.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Michael Kaplan
'
Function ChangePropertyDdl(stPropName As String, _
PropType As DAO.DataTypeEnum, vPropVal As Variant) _
As Boolean
' Uses the DDL argument to create a property
' that only Admins can change.
'
' Current CreateProperty listing in Access help
' is flawed in that anyone who can open the db
' can reset properties, such as AllowBypassKey
'
On Error GoTo ChangePropertyDdl_Err

Dim db As DAO.Database
Dim prp As DAO.Property

Const conPropNotFoundError = 3270

Set db = CurrentDb
' Assuming the current property was created without
' using the DDL argument. Delete it so we can
' recreate it properly
db.Properties.Delete stPropName
Set prp = db.CreateProperty(stPropName, _
PropType, vPropVal, True)
db.Properties.Append prp

' If we made it this far, it worked!
ChangePropertyDdl = True

ChangePropertyDdl_Exit:
Set prp = Nothing
Set db = Nothing
Exit Function

ChangePropertyDdl_Err:
If Err.Number = conPropNotFoundError Then
' We can ignore when the prop does not exist
Resume Next
End If
Resume ChangePropertyDdl_Exit
End Function

Function ChangeProperty(strPropName As String, _
varPropType As Variant, varPropValue As Variant) As Integer
' The current listing in Access help file which will
' let anyone who can open the db delete/reset any
' property created by using this function, since
' the call to CreateProperty doesn't use the DDL
' argument
'
Dim dbs As Database, prp As Property
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function
' *********** Code End ***********

What's best for DBAs? GUI or T-SQL commands?

2004-10-04T21:57:00.000-04:00

What's best for DBAs? GUI or T-SQL commands?

Do SQL Server DBAs really need to know the T-SQL commands for doing day to day administrative tasks? This article is one of the best explanations I've found that discusses the benefits of learning T-SQL to accomplish administrative tasks. More and more DBAs are becoming "lazy" and use the GUI that Microsoft provides, SQL Server Enterprise Manager.

Disable prompts when sending (Outlook) email via Access

2004-09-29T01:05:00.000-04:00

If you have ever tried to automate the process of sending an email from within Access using VBA code, you may have seen this error message:

"A program is trying to automatically send e-mail on
your behalf. Do you want to Allow this?"

It makes you wait & then you HAVE to hit the Yes button for it to send the Email.

A SOLUTION that should work ...

Dim objOutlook As New Outlook.Application
Dim objMail As MailItem

Set objOutlook = New Outlook.Application
Set objMail = objOutlook.CreateItem(olMailItem)

EmailAddr = "xxx@AOL.com"
CopyEmailAddr = "yyy@AOL.com"
Subj = "Test subjject"
Body = "Body"
PathName = "C:\test.txt"

With objMail
.To = EmailAddr
.cc = CopyEmailAddr
.Subject = Subj
.Body = Body
.NoAging = True
.Attachments.Add PathName
.Display
End With

'****** Send the email without prompts *******
SendKeys "%{s}", True
'****************************************************

Set objMail = Nothing
Set objOutlook = Nothing

Use Microsoft Jet's ShowPlan to write more efficient queries

2004-09-27T23:51:00.000-04:00

Use Microsoft Jet's ShowPlan to write more efficient queries

While reading some posts on the Access newsgroup, I came across this article about optimizing your Access queries. One of the most powerful features of SQL Server is being able to view the execution plan of a query. With this relatively simple registry hack, you can accomplish a very similar feature in Access, by forcing Access to export the execution plan to a text file.

Green bar reporting in Access

2004-09-27T23:07:00.000-04:00

Many accounting style reports need help to be more readable. Ever used a ruler on a landscape report to make sure you read data from the correct row? Remember the old green bar reports from mainframes that made this easier? Well, you can accomplish the same type of layout on an Access report.

Make sure the BackStyle of each control is Transparent.

Code the Detail Format event:

If Me.Section(0).BackColor = vbWhite Then
Me.Section(0).BackColor = 12632256 ' gray
Else
Me.Section(0).BackColor = vbWhite
End If

Access your bookmarks online

2004-09-27T09:46:00.000-04:00

MyBookmarks.com - Access your bookmarks online anytime, anywhere

I've always hated having to manage bookmarks at home, at work, on my laptop ... it gets very frustrating not having all my bookmarks on all my machines.

Now, with mybookmarks, I have a cool little icon in my system tray that I can use to quickly add any site to my bookmarks - on any machine. For example, if I'm at work and I find a cool site I want to bookmark, I add it to mybookmarks then when I get home or use my laptop, I can syncronize that new bookmark to that machine. Now I don't have to email myself links all the time.

Microsoft Announces 2006 Target Date for Windows "Longhorn"

2004-09-27T09:44:00.000-04:00

Microsoft Announces 2006 Target Date for Broad Availability Of Windows "Longhorn" Client Operating System

Microsoft has had to re-prioritize many many times in the past several months, deciding which features will be included in the upcoming release of Windows. Longhorn (the code name for the next version of Windows) was supposed to be one of the most aggressive releases Microsoft has released; and it still might be, just not quite as feature laden as initially planned.

One of the biggest changes will be the new file system, WinFS. The current Windows file system, NTFS, is quickly becoming outdated by other operating systems (Linux, Novel, etc.). Another key feature will be the integrated mobile device support. As a huge proponent of Pocket PCs, I think this is a great move by Microsoft. One of the biggest complaints users of Pocket PCs have is the syncronization software called Activesync. I'm hoping that having the mobile support integrated into the operating system will fix many of the bugs that have plagued Activesync for years.

Only two years to go ...

A more complete datediff function

2004-09-26T23:20:00.000-04:00

One of the more common questions I receive in my Access classes is how to display the difference between two dates. VBA has always had the datediff function; however, this function only allows you to display the difference with one date part (year, month, or day – not all three together). So, instead of seeing the difference as 1 year 2 months and 3 days, you can choose one date part.

I highly recommend reading newsgroups for valuable information about programming (use msnews.microsoft.com if you don’t have a news server available). Microsoft MVPs (most valuable professionals) constantly post and answer questions in these groups. Douglas Steele is one of the MVPs which posts quite often and has some very useful code samples on his website.

One of the functions, posted here, lets you calculate a "precise" difference between two date/time values.

You specify how you want the difference between two date/times to be calculated by providing which of ymdhns (for years, months, days, hours, minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days

?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds

?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day

'***************** Code Start **************
Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
Optional ShowZero As Boolean = False) As Variant
'Author: © Copyright 2001 Pacific Database Pty Limited
' Graham R Seach MCP MVP gseach@pacificdb.com.au
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
' This code is freeware. Enjoy...
' (*) Amendments suggested by Douglas J. Steele MVP
'
'Description: This function calculates the number of years,
' months, days, hours, minutes and seconds between
' two dates, as elapsed time.
'
'Inputs: Interval: Intervals to be displayed (a string)
' Date1: The lower date (see below)
' Date2: The higher date (see below)
' ShowZero: Boolean to select showing zero elements
'
'Outputs: On error: Null
' On no error: Variant containing the number of years,
' months, days, hours, minutes & seconds between
' the two dates, depending on the display interval
' selected.
' If Date1 is greater than Date2, the result will
' be a negative value.
' The function compensates for the lack of any intervals
' not listed. For example, if Interval lists "m", but
' not "y", the function adds the value of the year
' component to the month component.
' If ShowZero is True, and an output element is zero, it
' is displayed. However, if ShowZero is False or
' omitted, no zero-value elements are displayed.
' For example, with ShowZero = False, Interval = "ym",
' elements = 0 & 1 respectively, the output string
' will be "1 month" - not "0 years 1 month".

On Error GoTo Err_Diff2Dates

Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim varTemp As Variant

Const INTERVALS As String = "dmyhns"

'Check that Interval contains only valid characters
Interval = LCase$(Interval)
For intCounter = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
Exit Function
End If
Next intCounter

'Check that valid dates have been entered
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If

Diff2Dates = Null
varTemp = Null

'What intervals are supplied
booCalcYears = (InStr(1, Interval, "y") > 0)
booCalcMonths = (InStr(1, Interval, "m") > 0)
booCalcDays = (InStr(1, Interval, "d") > 0)
booCalcHours = (InStr(1, Interval, "h") > 0)
booCalcMinutes = (InStr(1, Interval, "n") > 0)
booCalcSeconds = (InStr(1, Interval, "s") > 0)

'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
Date1 = DateAdd("yyyy", lngDiffYears, Date1)
End If

If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
Date1 = DateAdd("m", lngDiffMonths, Date1)
End If

If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("d", lngDiffDays, Date1)
End If

If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lngDiffHours, Date1)
End If

If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lngDiffMinutes, Date1)
End If

If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
Date1 = DateAdd("s", lngDiffSeconds, Date1)
End If

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
End If

If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
End If
End If

If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If

If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
End If
End If

If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
End If
End If

If booSwapped Then
varTemp = "-" & varTemp
End If

Diff2Dates = Trim$(varTemp)

End_Diff2Dates:
Exit Function

Err_Diff2Dates:
Resume End_Diff2Dates

End Function
'************** Code End *****************

Check out Doug's homepage for more great samples.

Lebans Holdings Website

2004-09-23T08:44:00.000-04:00

I'm always looking for more resources online for examples and plugins for Access. Lebans has been one of the most invaluable resources I have found. Well known on the discussion forums for having useful addins/funtions like MouseWheel, MonthCalendar and Justification, the website is definitely worthy of your time to explore to see what they have available.

Lebans Holdings

Crystal Reports: Field Clipping

2004-09-21T15:31:00.000-04:00

It seems that every new version of Crystal Reports changes how data is displayed when the field simply isn't wide enough. Until Crystal 7, the data was just clipped by the size of the field on the report. So, you could spend a pretty significant amount of time trying to figure out why the numbers on your report were wrong, only to find out that the field just needed to be made wider.

Crystal 7 introduced a new option that you could finally set to show pound signs when a number was too wide, instead of just dropping the extra digits. Unfortunately, Crystal also set this option, called "Field Clipping", off by default. Even the newer versions of Crystal, 9 and 10, still have this option set to off.

To turn off field clipping and display pound signs when a number is too wide:

File > Options
Fields Tab
Number Button (you should also do the Currency Button)
Number Tab
Customize Button
Take out the check mark in the lower left for "Field Clipping".

Only new reports will be affected. Any existing reports will still have field clipping until you change the numeric and currency fields individually. To change an individual field right-click on the field and select "Format Field". Then click the "Customize" button and take out the checkmark for "Field Clipping".

Should Microsoft be worried about Google's success?

2004-09-21T13:22:00.000-04:00

As Goolge expands into more and more realms of the internet (gmail, froogle, google local, etc.), the author of this article seems to think Google has big plans for your desktop. A very interesting read ...

Bad Hair Day

2004-09-20T20:32:00.000-04:00

dataDog got a haircut today. Here's a picture of him after the carnage was over.

Got Gmail?

2004-09-20T15:42:00.000-04:00

Heard of Gmail yet? It's Google's free email service which allows you to store 1 gig - yes that is 1,000,000 bytes - of email!! No more running out of space because your friend just emailed you 10 mb worth of vacation pictures you don't care to see.

The only problem is, as I posted in a previous blog, Gmail is still in beta (testing) and you can only get an account if you get "invited." I still have a few invites left that I can send out, so if you want a gmail account, email me!.

Updated Amazon Browser Buddy Toolbar

2004-09-19T01:46:00.000-04:00

A9

Ever wanted an easier way to find books (or any other item) at Amazon?

Amazon has finally released a new version of there award winnig search engine - complete with a browser buddy toolbar. Definitely worth checking out.

More Blogs!!

2004-09-17T08:02:00.000-04:00

The honorable Celeste Sartor, colleague and very good friend of mine, has also recently started a blog. Definitely worth checking out! Drop her some comments, and tell her I sent you :)

Calculating the first and last day of the month

2004-09-17T07:02:00.000-04:00

Ever needed to calculate the first and last day of the month? Here's an expression that can be used in Access:

DateSerial(Year(Now), Month(Now) + 1, 0) - Last day of month
DateSerial(Year(Now), Month(Now), 1) - First day of month

Commonly used naming conventions

2004-09-16T13:09:00.000-04:00

When building Access databases, it is highly recommended that you use a naming convention to standardize how you name your objects. This will make development easier for yourself and help eliminate confusion when other developers make modifications to your database. This article outlines some commonly used naming conventions.

The best way to choose good names for software objects is to employ a naming convention or standard system. Aside from better communication among programmers, the many benefits of using a naming convention include:

Saves time thinking up names
Reduces the need for comments
Helps you spot errors, by allowing visual consistency checks, similar to dimension checks on mathematical equations
Triggers early detection of design or implementation issues
Helps in debugging code
Provides an identity for the project's code and participants
Allows automated tool support for writing/checking code

General: Commonly used naming conventions

Deleting all data from an Access database

2004-09-15T23:23:00.000-04:00

One of the questions I have had asked several times in class is how to empty a database of all existing data. You don't want to lose your tables structure, just the data. For example, you've created a new database and during the development process you have entered quite a bit of test data that needs to be removed before you release it to users. If done manually, this job can quickly become tedious. If your database has many tables, the following code will clear all the data in a hurry.

Dim ctr As Container, doc As Document, db As Database
Set db = CurrentDB()
Set ctr = db.Containiners!Tables
For Each doc in ctr.Documents
If Left$(doc.Name, 4) <> "MSys" Then 'Table is not a system table
db.Execute "DELETE [" & doc.Name & "].*" & _
"From [" & doc.Name & "];"
End If
Next doc

Recommended Sites

2004-09-15T17:57:00.000-04:00

I have changed how I am storing my recommended sites. The goal is to share websites that I have found invaluable or simply great references. Trying to maintain a list on my blog would quickly get out of hand and be very difficult to organize. So, I have enlisted the services of Furl.

Furl is a new (still in beta) website that you can save your bookmarks to and then share them with friends. You can even subscribe to my bookmarks using their RSS feed (very cool!). If you want to know more about how to use RSS, search google or go to Furl's website for more information.

So, now when you click on Recommend Sites, you will be taken to my bookmarks page on Furl. There, you will be able to filter and view by category, popularity, rating and more. Hopefully you will all find that very useful!

Firefox browser to hit milestone

2004-09-15T17:54:00.000-04:00

Firefox browser to hit 1.0 milestone | CNET News.com

My favorite browser is growing up! If you haven't tried Firefox yet, now's the time. The usability features (tabbed browsing, integrated search bar, extensions, skins, etc.) that Firefox introduces are what make it my browser of choice.

I have found Firefox to be more stable, faster, and easier to use than Internet Explorer. Try it out and let me know what you think!

Gmail tools and addins

2004-09-15T17:35:00.000-04:00

Plugins and tools to make Gmail easier to use are starting to pop up on the web, click read more to see some of the most popular. Don't know what Gmail is? Click read more. Props to Rob for the original post.

* Official Gmail Notifier from Google (beta) - Downloadable Windows application that alerts you when you have new Gmail messages.
* Gmail Search Extension - Search your Gmail account from the Firefox search bar
* Gmail Tray - A search box for the Windows taskbar
* Gmail Notifier - Firefox extension, installs an icon on your toolbar that displays the amount of new messages in your inbox (previous post)
* Gmail-to - Associate all mailto: links with Gmail as opposed to Outlook
* Gmail-to Bookmarklet - Compose messages from your Gmail account from any page (previous post)
* Gmailit - Bookmarklet to send the current page you're viewing to someone through Gmail
* GMail Loader (GML) - Cross platform application forwards your existing email to Gmail
* Pop Goes the GMail - Gmail > POP3 converter
* gExodus - Graphical user interface tool allows you to import your emails into your Gmail account.
* GMail, RSS Feeds - Retrieve notifications of new mail through RSS
* Gmail Gems - Tips and Tricks to use Gmail more efficiently
* Unread Gmail - Create a label called "Unread", Gmail will automatically add all unread items to it (Via Gmail Wiki)
* GSuite - Lets you access and use Gmail from a mini-browser in the system tray
* Gmail icon generator - Create an image of your Gmail address to minimize spam exposure (Via here)

Gmail is Google's new email service. It's been a pretty hot topic on many technology news sites lately. Unfortunately, it's still in beta, which means it isn't open to the public just yet. Existing users are given "invitations" so they can give friends a gmail account. I guess that's Google's way of doing a controlled rollout of Gmail. Anyway, if you really want one and don't have one yet, shoot me an email. I may just have an invitation I can send you.

Blog separation

2004-09-15T15:54:00.000-04:00

I've decided to go with two blogs. This blog will mainly be for technology related information while paqguy remains my personal blog.

2261 & 2262 Supporting Windows XP

2004-01-20T01:00:00.000-05:00

Useful links and articles for 2261 and 2262.

Windows XP Resource Kit

Useful knowledge base articles

2004-01-02T13:02:00.000-05:00

Use Excel's Median function in Access

2072 - SQL Administration

2004-01-01T00:00:00.004-05:00

Below are some useful links to supplement the course 2072 - SQL Server 2000 Administration:

SQL Server Administration Tips

SQL Server Performance

SQL Junkies

SQL Server Central

SQL Team

Migrating Access Databases to SQL Server

SQL Server Security

How to prevent the transaction log from growing unexpectedly

Track SQL Server file growth

SQL Server DTS

TSQL or GUI for SQL DBAs?

SQL Server Articles

2004-01-01T00:00:00.003-05:00

General Microsoft SQL Server articles, tips and links:

Stored Procedure to find all instances of a field

What's best for DBA's? GUI or T-SQL commands?

Which tool should I use: Enterprise Manager or Query Analyzer?

What is XML?

How to configure Reporting Services log file reports

2073 Links

2004-01-01T00:00:00.002-05:00

Below are some useful links to supplement the course 2073 - Programming a Microsoft SQL Server 2000 Database:

Naming Conventions

OLE DB versus ODBC data access methods

Transact SQL

T-SQL Scripts

Tracking File Growth

Cursor Performance

Query Execution Plan Analysis

SQL Server Parallel Execution Plans

10 Things You Shouldn't Do With SQL Server (Data Access Methods)

Temp tables discussed

2071 - TSQL

2004-01-01T00:00:00.001-05:00

Below are some useful links to supplement the course 2071 - Querying SQL Server 2000 with Transact-SQL:

SQL Server Development Tips

Transact SQL

SQL Server Performance

Crystal Reports Articles

2004-01-01T00:00:00.000-05:00

General Crystal Reports tips, tricks, and other articles

Field Clipping
Table join problems