If you are writing a program that is going to work both with MS Access and MS SQL Server, or if you guess that your program will have to work either on MS Access or MS SQL Server in future, then you should think about handling dates.

MS Access and MS SQL Server handles date values in different ways.

See the following example:

We need to insert a row, that contains a date field, into a table.

Let's see how we write the Insert SQL statement in MS Access:

sqlStr = "INSERT INTO users (Name, DateOfBirth) VALUES('" & _
                name & "', #" & dateOfBirth & "#)"

DBConnection.Execute sqlStr

Where "DBConnection" is the active database connection.

If we need to write the same Insert SQL statement for MS SQL Server:

sqlStr = "INSERT INTO users (Name, DateOfBirth) VALUES('" & _
                name & "', '" & dateOfBirth & "')"

DBConnection.Execute sqlStr

The difference: Access uses # as date delimiter where as SQL Server uses ' (single quote) for the same purpose.

If you want your code should work with any of these databases without much change in the code:

sqlStr = "INSERT INTO users (Name, DateOfBirth) VALUES('" & _
                name & "', " & getDateDelimiter() & dateOfBirth & _
                getDateDelimiter() & ")"

DBConnection.Execute sqlStr

Where getDateDelimiter() is a function that can be called by any of your ASP pages / programs. The following is the code for getDateDelimiter() function.

Public Function getDateDelimiter()

   ' If you are using MS Access as your database, use # as
   ' date delimiter, if your database is MS SQL Server, 
   ' then change it to ' (single quote)

    getDateDelimiter = "#"

End Function

And when you are changing the database, just change one character in the getDateDelimiter() function!