Constants and Variables

[Thales750]

Hello All,
I have a Loader written by one of our old alumni, KenHigg.


The loader was originally written to Load a new Front-end into a local drive. Enter a new world where my Users are logging into a Remote Desktop with a shared C: Drive.
So the loader will be on the C: Drive, but the files it copies will be on the User\Drive.

I can verify the existence, and make directories, in the User Drive with the following code. This code runs from the On Load Event of the Display Form I added.

Private Sub Form_Load()
Dim strExistinPath As String
On Error Resume Next   
        strExistinPath = "C:\Users\" & getComputerUser & "\TrecoraClient"   
     If Dir(strExistinPath, vbDirectory) = "" Then    'MkDir strDirectoryPath
        MkDir strExistinPath
    End If
DoCmd.RunMacro "autoexec1"

DoCmd.Close
End Sub

Once the form closes, it runs the Autoexec1 Macro that was previously named Autoexec, so it would open at launch. The Display Form loads now and then the macro Runs. As you can see. This Macro calls a Function

In the Module Constants are given a value.

Both of the following Codes Works:

'Const cstrClientFEPath = "C:\TrecoraClient\"

'Const cstrClientFEPath = "C:\Users\jsanders\TrecoraClient\"

These do not:

'Const cstrClientFEPath = "C:\Users\" & Environ("username") & "\TrecoraClient\"
Const cstrClientFEPath = "C:\Users\" & getComputerUser & "\TrecoraClient"

The get User Function is defined as:

Public Function getComputerUser()
    pubComputerUser = Environ("username")
    getComputerUser = pubComputerUser
End Function

Any Ideas?

 

[theDBguy]

Sorry. Any ideas about what? I may have missed the question or description of the problem.

 

[Thales750]

Sorry. Any ideas about what?

Why the Constants don't work with Variable in the Path.

 

[Pat Hartman]

I don't know. What value is the function returning? It may not be retrieving the correct value.

I use a batch file rather than a loading app. This is the one I use for a Citrix installation. I think RD might be similar.

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb

 

[theDBguy]

Why the Constants don't work with Variable in the Path.

Do you have Option Explicit in your modules?

 

[theDBguy]

Both of the following Codes Works:

'Const cstrClientFEPath = "C:\TrecoraClient\"

'Const cstrClientFEPath = "C:\Users\jsanders\TrecoraClient\"

These do not:

'Const cstrClientFEPath = "C:\Users\" & Environ("username") & "\TrecoraClient\"
Const cstrClientFEPath = "C:\Users\" & getComputerUser & "\TrecoraClient"

Any Ideas?

Oh, I think I get it now...

When you declare a constant, you have to use a literal value. After all, that's what constant mean, right? You can't use a variable to declare a constant, as that would defeat the purpose.

 

[isladogs]

Constants are just that. They require a constant expression so a variable won't work
For that reason both of these will cause a compile error

Const dteDate = Date
Const StrUserName = Environ("UserName")

However, it is easy enough to work around this. For example
a) Declare a public variable and assign its value in a function
b) use a TempVar

 

[The_Doc_Man]

There is an issue with string constants. See this thread:

Solved - fileCopy error

do not know if this has been answered before but i found this one https://www.vbforums.com/showthread.php?421518-path-not-found-in-filecopy but it not quantify my issue. i am running test code to copy text files from one directory to other. i keep getting path not found error. but source...

www.access-programmers.co.uk

 

[arnelgp]

so a variable won't work

it will, but not with Functions.

Private Sub t()
Const j As Integer = 100
Const m = j
Debug.Print m
End Sub

 

[The_Doc_Man]

There are various cases where constants won't work correctly. Functions may well be one, particularly if the function's argument is ByRef. Never tried the ByVal case. I know that expressions that try to build a constant in an expression from another symbolically named constant will often fail - but will work for literal constants.

I cannot prove this because of course we don't have visibility of the generated code for VBA, but I suspect that the constants are stored in a read-only area, so anything that requires read-write access would fail. Then again, since we can't see inside the code, that's just a guess.

 

[isladogs]

A constant based on another constant will obviously work ... precisely because it isn't a variable
For example

Const j = 100
Const m = 2 * j

Sub test()
   Debug.Print j
   Debug.Print m
End Sub

 

[arnelgp]

precisely because it isn't a variable

it is called Constant Variable?, ie. Pi

 

[Isaac]

Back to the original problem: Just don't use a Constant. Use a variable.

 

[The_Doc_Man]

A constant based on another constant will obviously work ... precisely because it isn't a variable
For example

Const j = 100
Const m = 2 * j

Sub test()
   Debug.Print j
   Debug.Print m
End Sub

Yeah, but now try to build a string constant that way from another string constant.

 

[isladogs]

Yeah, but now try to build a string constant that way from another string constant.

Not sure I see your point. Do you mean something like this?

It also works if you use + instead of & :

Const y = x + "World Forums"

So whilst both that and the previous example I gave in post #11 do work, I wouldn't ever use this approach in a real database

 

[Thales750]

I've never used a constant in 25 years of Access Development. Why did Ken use them in this Loader?

Here is a link to Ken's Loader, Iv'e used it for 12 years now, hundreds of user stay updated to the latest version.
Ken's Example Files.

 

[The_Doc_Man]

Not sure I see your point. Do you mean something like this?

View attachment 92876
It also works if you use + instead of & :

Const y = x + "World Forums"

So whilst both that and the previous example I gave in post #11 do work, I wouldn't ever use this approach in a real database

Some combination of string constant usage has caused errors, as I reported in my referenced post, which in turn reported other errors. If this has been somehow fixed in a newer version of Access, great.

 

[MajP]

I've never used a constant in 25 years of Access Development. Why did Ken use them in this Loader?

Same reason anyone uses a constant. Makes code more readable and easier to update the code. He clearly specifies if you change the constants to your paths then it works. Imagine a constant not used and instead that path is in the code many times. It would be difficult to customize the code. Change in one place and changes everywhere.

 

[isladogs]

Some combination of string constant usage has caused errors, as I reported in my referenced post, which in turn reported other errors. If this has been somehow fixed in a newer version of Access, great.

I hadn't noticed your earlier post, nor had I read the linked thread previously.

Anyway, I have now checked the code in post #1 of that thread in both Access 365 & Access 2010.
Nobody had commented on it, but the last line of the code snippet is wrong in both post #1 and your modified version in post #10.
It should of course be End Sub not Exit Sub.

Once I had corrected the last line, as I expected that code worked perfectly in both versions of Access

 

[Thales750]

Same reason anyone uses a constant. Makes code more readable and easier to update the code. He clearly specifies if you change the constants to your paths then it works. Imagine a constant not used and instead that path is in the code many times. It would be difficult to customize the code. Change in one place and changes everywhere.

I've known that it works for 12 years, thank you. It does not work with variables, as several people have previously stated.

Variable make code easier to read, why use a constant instead of a variable?

When exactly is a constant better than a variable?