Access Runtime Error when using Str function

[Randy@Fluke]

I have created a database project (Access 2003), created an installation
package with Access extension software and just distributed the first one on
a PC. Installation went fine and the database opens. However, anytime it runs
a routine that uses the "Str" function, an error message is displayed
"function isn't available" followed by the offending expression. For this
instance, the offending "Str" is in VBA code. I've also seen this same
problem when the "Str" function is used in query expression. I know it's the
"Str" because if I remove that function from the code, it runs fine. Of
course I can't do what I need to do without it, but the test proved "Str"
function was the offending function.

Why is this function not available in the Runtime version and works fine
when the code is run under the full version of Access on my development
machine. I tried the runtime switch on my development machine but the
shortcut properties always said the "/Runtime" after the "...msaccess.exe"
path was not valid. Anyone have any suggestions to a solution?

 

[Rick Brandt]

I have created a database project (Access 2003), created an
installation package with Access extension software and just
distributed the first one on a PC. Installation went fine and the
database opens. However, anytime it runs a routine that uses the
"Str" function, an error message is displayed "function isn't
available" followed by the offending expression. For this instance,
the offending "Str" is in VBA code. I've also seen this same problem
when the "Str" function is used in query expression. I know it's the
"Str" because if I remove that function from the code, it runs fine.
Of course I can't do what I need to do without it, but the test
proved "Str" function was the offending function.

You are seeing the very common "Missing Reference" issue. Your file
contains a reference to an external library file that doesn't exist or is
not properly registered on the target PC. This causes many built in
functions to fail and the missing library need not have anything to do with
the functions that fail. See article at link below...

http://support.microsoft.com/?id=160870

Never mind that the article only mentions Access 97. The problem applies to
all versions.

Why is this function not available in the Runtime version and works
fine when the code is run under the full version of Access on my
development machine. I tried the runtime switch on my development
machine but the shortcut properties always said the "/Runtime" after
the "...msaccess.exe" path was not valid. Anyone have any suggestions
to a solution?

To use /Runtime (or any other command line argument) you need a shortcut to
MSACCESS.EXE (the program), not a shortcut to your MDB. The target of the
shortcut would look like...\

"Full path to MSAccess.exe" /Runtime "full path to your MDB file"

 

[Randy@Fluke]

Thank you Rick for taking the time to look at and respond to my problem.

First on the broken link issue: The article was great on information,
however, it doesn't tell me how I can fix the problem in my situation. The PC
that I am installing the project on doesn't have the full Access program and
therefore thte database can't be opened to edit forms or look at VBA code
where the references are set. You would think that the installation files
created by the Access Extensions program would check for all the required
references and ensure they are installed if they don't exist on the target
PC. That same database on the development PC works fine.

On the runtime issue: I was putting the /Runtime inside the quotes with the
MSACCESS.EXE path string.

I would appreciate any suggestions or references for fixing reference links
on PCs without the full Access software.

Thanks again.

Randy

 

[Rick Brandt]

Thank you Rick for taking the time to look at and respond to my
problem.

First on the broken link issue: The article was great on information,
however, it doesn't tell me how I can fix the problem in my
situation. The PC that I am installing the project on doesn't have
the full Access program and therefore thte database can't be opened
to edit forms or look at VBA code where the references are set.

Couple things you can try. First, your file might very well have extra
references that are causing this problem that you don't even need. Open a
new blank file, press Ctl-G to open the VBA code editor window and then go
to Tools - References. The list of "checked" references will be the three
or four default ones that every Access file of the version you are using
will have. Now look at the references in your file. If you have ANY
additional ones then the extras are more than likely the problem and should
be eliminated.

Uncheck each of the extra ones one at a time and then try to compile the
project. If the project will still compile without errors with the
reference removed, then you don't need it. Once any un-needed references
are removed you can try your file again on the runtime PC.

If you intentionally have additional references to Word, Excel, Outlook,
etc., then you should change your code that needs them to late binding so
they can be removed. You have no license to distribute those libraries
anyway so no runtime project should ever depend on them. With late binding
the particular functions that need those referenced libraries will obviously
not work on PCs where they don't exist, but at least the basic functionality
of your app is not broken by having a missing reference

You can also add this code to your app and run it from some obscure button
or admin area of your file.

Sub EnumerateReferences()
Dim ref As Reference
Dim strOut As String

For Each ref In Access.References
strOut = strOut & vbCrLf & ref.Name & IIf(ref.IsBroken, "
Missing", " OK") & vbCrLf & ref.FullPath & vbCrLf
Next ref

MsgBox strOut
End Sub

That will display a message box showing all references and will indicate
which are broken. That can be used in the runtime to identify the problem.

You
would think that the installation files created by the Access
Extensions program would check for all the required references and
ensure they are installed if they don't exist on the target PC. That
same database on the development PC works fine.

They couldn't do that because they have no idea whether you have any legal
right to distribute those libraries. You have MANY libraries on your system
as the result of installing software and some of them could be referenced
and used in an Access app on your PC. The fact that you purchased and
installed that software almost never means that you also are legally allowed
to distribute those reference libraries though.

 

[Douglas J. Steele]

Randy@Fluke wrote:
You can also add this code to your app and run it from some obscure button
or admin area of your file.

Sub EnumerateReferences()
Dim ref As Reference
Dim strOut As String

For Each ref In Access.References
strOut = strOut & vbCrLf & ref.Name & IIf(ref.IsBroken, " Missing",
" OK") & vbCrLf & ref.FullPath & vbCrLf
Next ref

MsgBox strOut
End Sub

Unfortunately, Access raises an error if you try to use either the Name or
FullPath properties for a broken reference.

 

[Rick Brandt]

Unfortunately, Access raises an error if you try to use either the
Name or FullPath properties for a broken reference.

Really? Then where the heck did I get that code?

I could have sworn it was from one of the various MVP sites. I don't know if I
have ever used it when it didn't report full success (I seldom use additional
references), but have just been assuming that it would report a bad reference if
one were there.

I think I added that to one of my apps that runs under the runtime on our Citrix
servers (just in case). Don't think I've ever had a need for it to actually
work yet though.

 

[Douglas J. Steele]

Really? Then where the heck did I get that code?

I could have sworn it was from one of the various MVP sites. I don't know
if I have ever used it when it didn't report full success (I seldom use
additional references), but have just been assuming that it would report a
bad reference if one were there.

I think I added that to one of my apps that runs under the runtime on our
Citrix servers (just in case). Don't think I've ever had a need for it
to actually work yet though.

Well, it _will_ report a bad reference, but not in a very useful way! <g>

You know what your references are supposed to be. Just knowing that the 2nd
or the 4th reference is broken should be enough.

 

[Rick Brandt]

Well, it _will_ report a bad reference, but not in a very useful way!
<g>
You know what your references are supposed to be. Just knowing that
the 2nd or the 4th reference is broken should be enough.

I wasn't sure how to easily simulate a broken reference so I made an
external reference to an MDE and then renamed the file I was referencing.

It created the broken reference symptoms perfectly. Several built in
functions failed and the reference was marked "MISSING" in the references
dialog.

I ran that code and it worked as advertised. My good references were
reported as good and my bad reference was reported as Missing and there were
no errors.

Perhaps an MDE reference is not a good test of the concept.

 

[Randy@Fluke]

OK Rick. I did as you suggested and was able to delete a reference I didn't
need. However, I put your code in my application and reloaded on the target
PC. Your program indicates that all the reference likes are OK but I still
can't run the procedure with the Str function in it.

Not sure what to do next.

Randy

 

[Rick Brandt]

OK Rick. I did as you suggested and was able to delete a reference I
didn't need. However, I put your code in my application and reloaded
on the target PC. Your program indicates that all the reference likes
are OK but I still can't run the procedure with the Str function in
it.

Not sure what to do next.

What references are you using?

 

[Douglas J. Steele]

I wasn't sure how to easily simulate a broken reference so I made an
external reference to an MDE and then renamed the file I was referencing.

It created the broken reference symptoms perfectly. Several built in
functions failed and the reference was marked "MISSING" in the references
dialog.

I ran that code and it worked as advertised. My good references were
reported as good and my bad reference was reported as Missing and there
were no errors.

Perhaps an MDE reference is not a good test of the concept.

I suspect it's not a good test, since there's no GUID property.

 

[Randy@Fluke]

I think I need to state the problem differently. I use VBA code to create an
SQL string and execute that string with an Execute command. A simple query.
That SQL string has the Str function as part of an expression, where it
converts a number field to a string field. The error appears when that SQL is
executed. So I don't think the problem is really a VBA problem but a query
problem.

To answer your question, the references are:

Visual Basis for Applications
Microsoft Access 11.0 Object Library
Microsoft Office XP Web Components
Microsoft DAO 3.5 Object Library

Randy

 

[Rick Brandt]

I think I need to state the problem differently. I use VBA code to
create an SQL string and execute that string with an Execute command.
A simple query. That SQL string has the Str function as part of an
expression, where it converts a number field to a string field. The
error appears when that SQL is executed. So I don't think the problem
is really a VBA problem but a query problem.

As stated previously, ANY broke VBA references causes many of the built in
functions to fail.

To answer your question, the references are:

Visual Basis for Applications
Microsoft Access 11.0 Object Library
Microsoft Office XP Web Components
Microsoft DAO 3.5 Object Library

Most likely it is the Web Components library. Switch your code using that to
late binding. That will allow you to remove the reference. You're sure the
Enumerate References code indicates that the WEB library is okay?

 

[Brendan Reynolds]

In addition to the good advice you have received from others, you might want
to double-check the DAO reference. It should be 3.6, not 3.5.

Although I don't think it is the cause of your current problem there is a
peculiarity of the Str function that can be a problem if you are not aware
of it. The Str function places a space in front of positive numbers. This
can cause tests such as 'If Str(SomeNumber) = "0" Then' to produce
unexpected results, because if SomeNumber = 0 then Str(SomeNumber) is not
equal to "0", it is equal to " 0". For this reason, CStr is often a better
choice than Str - it does the same thing, but without the leading space.

 

[Randy@Fluke]

I was aware of CStr, but didn't know it would remove the leading space. I had
always used Trim with Str to strip off the space. In my application, I had
removed Trim from the SQL to see if that was the problem child. The Str by
itself still caused an error.

However, implementing your suggestion to switch to CStr allowed my
application to work on the Runtime system. I still don't know why Str won't
work in a query on the Runtime only PC, but I can move on with my application
and don't see any reason to use Str again.

Thanks for jumping in with your suggestion. It was a great help as were all
the other suggestions.

Randy

 

[Randy@Fluke]

Thanks again Rick.

I had already tried a dumbed down version of the program without the WEB
Components reference and had the same problem. I am interested in how you set
late reference binding however.

I am now been able to run my program on the PC with only the Runtime.
Brendan's suggestion of using CStr instead of Str made the whole process
work. It doesn't solve the orginal problem, but for me at this point I don't
need to solve that problem.

Again, thanks for the help. I learned a few things from you.

Randy

 

[Brendan Reynolds]

This is further evidence that the problem is a missing or mis-matched
reference, Randy. We see this frequently in the newsgroups where one
function causes an error but a similar function does not, for example Date
and Now, or Left and Mid. I would advise continuing to attempt to identify
the missing or mis-matched reference. It is likely to cause further problems
in the future if you do not.

Can you confirm that DAO reference? Does it really say 3.5, or was that just
a typo?

 

[Randy@Fluke]

It is 3.5. There is a 3.6 in the list of references. I could try it with that
reference and see what happens.

Randy

 

[Brendan Reynolds]

It should be DAO 3.6 for Access 2000 and later. DAO 3.50 was the version
originally shipped with Access 97, later service releases of Access
97/Office 97 introduced DAO 3.51.