Convert Common Access Field Defaults to SQL Server

I wrote a custom upsizing Access application that converts Microsoft Access backend data files to SQL Server.

One part of that application deals with converting common Microsoft Access field defaults into their SQL Server equivalents.  For example, the VBA Now() function gets converted to the SQL Server GETDATE() function.  The VBA date literal wrappers (#) are replaced with T-SQL date literal wrappers (i.e., single quotes: ').  Note that I replace Yes/True defaults with -1 and No/False defaults with 0 because I use SQL Server smallint fields to replace MS Access Yes/No fields.

Here is the Select Case statement I use:

Select Case Fld.DefaultValue
Case "Yes", "True"
    DefaultVal = -1
Case "No", "False"
    DefaultVal = 0
Case "=Now()", "Now()"
    DefaultVal = "GetDate()"
Case "=Date()", "Date()"
    DefaultVal = "DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))"
Case "=Time()", "Time()"
    DefaultVal = "CONVERT(TIME, GETDATE())"
Case "=DatePart(""yyyy"",Now())"
    DefaultVal = "YEAR(GETDATE())"
Case Else
    DefaultVal = Replace(Fld.DefaultValue, """", "'")
    DefaultVal = Trim(Replace(DefaultVal, "#", "'"))
    
    'remove leading equal sign (valid in Access but not SQL Server)
    If Left(DefaultVal, 1) = "=" Then DefaultVal = Trim(Mid(DefaultVal, 2))
End Select

This is not an exhaustive list, but it covers many of the most common default field value scenarios.  If you think I left something out, let me know in the comments below.

Referenced articles

Image by Gerd Altmann from Pixabay