Tempvars and access 2003

Question

I have a database that is used in a mixed 2003, 2007 environment. I have some minor functionality that uses 2007's new TempVars feature. If it is a 2003 user, it isn't a problem for them to not have those features.

How do I write my code so that it will compile and run on Access 2003. I have tried on error resume next but this doesn't work for compile time errors.

Semitone

Answer 1

If your application will be used with Access 2003, seems to me you should exclude features 2003 doesn't support.

However, if you must have Tempvars, see whether a conditional compiler constant approach would make it work for you.

Option Compare Database
Option Explicit
#Const Aversion = "2007" 'conditional compiler constant '

Public Sub HelloWorld()
    Dim strWho As String
    strWho = "World"

    #If Aversion = "2007" Then
        '* your 2007 feature code here *'
        strWho = UCase(strWho)
    #End If
    'Aversion 2003 -> Hello World '
    'Aversion 2007 -> Hello WORLD '
    Debug.Print "Hello " & strWho
End Sub

Check Access' Help for more information about #Const and #If.

I haven't tested this, but I think it could work. You might need two copies of your database: YourDb2003.mdb; and YourDb2007.mdb. In YourDb2003.mdb use "2003" as the compiler constant, and "2007" in YourDb2007.mdb.

Craver

That would work, but I was hoping for something that would be automatically done at runtime. It isn't possible to set a Const to Application.Version or anything else that would be useful to me. Any suggestions? – Semitone

Sorry, no. The only way I know of to include code which won't compile is to tell the compiler to ignore it with #If ... – Craver

Why not make your constant equal to "11.0" and then test "#If Application.Version > Aversion Then" or "#If Aversion <= Application.Version Then"? – Weiner

#If Application.Version > Aversion Then causes "Compile error: Invalid use of object". The #If directive is limited in what it will accept. – Craver

Is it that it can only be compared to a literal? I guess that makes sense, as otherwise, the line can't be resolved at compile time (only at runtime). There has to be a way to get around this, though. – Weiner

I've done some research and it seems to me that there is no way to do this without changing the constant and compiling for the target platform. It might be easier to do this with the Project Properties instead of in the VBA code. – Weiner

O'Reilly's VB & VBA In A Nutshell says, contrary to MS' documentation, some functions can be used to assign a value to a compiler constant. It specifically mentioned Int(). That may apply to the #If expression, too, but I haven't checked. So we're not restricted exclusively to literals; OTOH Application.Version did not work. If it were me, think I would discard the code which is incompatible with Access 2003. The OP mentioned "minor functionality". And if "it isn't a problem for [A2003 users] to not have those features", then the A2007 users should also be OK without them. – Craver

@Craver I think I may have to just discard the functionality. LIke I said, it isn't extremely critical, it is just a convenience feature. Thanks for your help everyone! – Semitone

Answer 2

Here is a nice summary about using VBA built-in compiler constants.
#If VBA7 Then could help you to differentiate Office 2010 only code. Unfortunately it does not apply to Office 2007.

Revelatory