VBA code runs in full Access 2007 but not in 2007 Runtime

[CAFRman]

I have a compiled Access 2000 application (converted to 2007) that seems to
have some code that runs perfectly fine in full access but will not run in
the 2007 runtime. Even when I execute the .accdr on a machine that has the
full access on it it runs fine but when i take the .accdr to a machine that
only has the 2007 runtime installed it will not run. I have been able to
isolate it to the following code.

sSql = "SELECT DISTINCT Mid([acct-char].[acct-nr]," & giFundStartPos
& "," & giFundLength & ") AS FundNbr, Characteristics.description as
FundName, Characteristics.position into tblFundName" & vbCr
sSql = sSql & "FROM Characteristics INNER JOIN [acct-char] ON
(Characteristics.abbrev = [acct-char].abbrev) AND (Characteristics.position =
[acct-char].position)" & vbCr
sSql = sSql & "WHERE Characteristics.position=2"
Set rstemp = dbMine.OpenRecordset(sSql, dbOpenSnapshot)

The execution actually seems to fail on the OpenRecordset. The error that
comes up is the following:

"The expression On Click you entered as the event property setting produced
the following error:
*The expression may not result in the name of a macr, the name of a
user-defined function, or [Event procedure]

Is this a bug or am i missing something?

 

[Albert D. Kallal]

I suspect that some other strange thing is going on here.

I also VERY surprised that your code example works the with vbcr in your
text?

You don't need, and as far as I can tell, vbCr should not be used here at
all.

Also, do you have option explicit set on?

When you go debug->compile....does your code compile?

I would use something like:

sSql = "SELECT DISTINCT Mid([acct-char].[acct-nr]," & _
giFundStartPos & "," & giFundLength & ") AS FundNbr," & _
"Characteristics.description as FundName, " & _
"Characteristics.position into tblFundName" & _
" FROM Characteristics INNER JOIN [acct-char] ON " & _
"(Characteristics.abbrev = [acct-char].abbrev) " & _
" AND (Characteristics.position = [acct-char].position)" & _
" WHERE Characteristics.position=2"

The above has no line breaks, and the use of & _ shows how to "continue" to
the next line....

 

[CAFRman]

I would agree that something strange is happening here but this code has been
working for 8+ years. For what it is worth the following OpenRecordset is not
working:

sSql = "SELECT * FROM tblSetupData " & vbCr
Set rstemp = dbMine.OpenRecordset(sSql, dbOpenSnapshot)

 

[david]

I'd guess a references problem. Is this the first/only code to run?
Looking up the definition of the manifest constant vbCr in the
VBA library won't work if there is any kind of references problem.

(david)

I would agree that something strange is happening here but this code has
been
working for 8+ years. For what it is worth the following OpenRecordset is
not
working:

sSql = "SELECT * FROM tblSetupData " & vbCr
Set rstemp = dbMine.OpenRecordset(sSql, dbOpenSnapshot)

I suspect that some other strange thing is going on here.

I also VERY surprised that your code example works the with vbcr in your
text?

You don't need, and as far as I can tell, vbCr should not be used here at
all.

Also, do you have option explicit set on?

When you go debug->compile....does your code compile?

I would use something like:

sSql = "SELECT DISTINCT Mid([acct-char].[acct-nr]," & _
giFundStartPos & "," & giFundLength & ") AS FundNbr," & _
"Characteristics.description as FundName, " & _
"Characteristics.position into tblFundName" & _
" FROM Characteristics INNER JOIN [acct-char] ON " & _
"(Characteristics.abbrev = [acct-char].abbrev) " & _
" AND (Characteristics.position = [acct-char].position)" & _
" WHERE Characteristics.position=2"

The above has no line breaks, and the use of & _ shows how to "continue"
to
the next line....

 

[CAFRman]

David,

I am starting to think it is a reference problem too but probably not what
you think. This application needs Excel. In the old version we distributed
Access 2000 runtime and of course referenced Excel 2000 as that is what it
was developed under. As the clients upgraded to Excel 2002, 2003 and now some
of them to Excel 2007 the runtime seemed to recognize the new Excel
applications. I just assumed that the 2007 runtime would recognize what ever
version of Excel was installed even if it wasn't 2007. I am beginning to
think it isn't that smart. Is there a way we can change references on-the-fly
to see whive version of Excel they have and set the reference correctly?

I'd guess a references problem. Is this the first/only code to run?
Looking up the definition of the manifest constant vbCr in the
VBA library won't work if there is any kind of references problem.

(david)

I would agree that something strange is happening here but this code has
been
working for 8+ years. For what it is worth the following OpenRecordset is
not
working:

sSql = "SELECT * FROM tblSetupData " & vbCr
Set rstemp = dbMine.OpenRecordset(sSql, dbOpenSnapshot)

I suspect that some other strange thing is going on here.

I also VERY surprised that your code example works the with vbcr in your
text?

You don't need, and as far as I can tell, vbCr should not be used here at
all.

Also, do you have option explicit set on?

When you go debug->compile....does your code compile?

I would use something like:

sSql = "SELECT DISTINCT Mid([acct-char].[acct-nr]," & _
giFundStartPos & "," & giFundLength & ") AS FundNbr," & _
"Characteristics.description as FundName, " & _
"Characteristics.position into tblFundName" & _
" FROM Characteristics INNER JOIN [acct-char] ON " & _
"(Characteristics.abbrev = [acct-char].abbrev) " & _
" AND (Characteristics.position = [acct-char].position)" & _
" WHERE Characteristics.position=2"

The above has no line breaks, and the use of & _ shows how to "continue"
to
the next line....

 

[Guest]

It is difficult to fixup references on the fly, and even more difficult
in an 'de. So difficult in fact that it is the universal recommendation
to never include references in a distributed application, and
always use late binding instead...

Fortunately, alltho they didn't have to, MS has always kept all
the values of the manifest constants the same in different versions
of Office, so all you have to do is go through and replace all
the Excel values with the correct numbers instead. Change all
your Excel declarations to 'as object', and fix up any use of
"New" to use CreateObject/GetObject instead, and you're
right to go.

(david)

David,

I am starting to think it is a reference problem too but probably not what
you think. This application needs Excel. In the old version we distributed
Access 2000 runtime and of course referenced Excel 2000 as that is what it
was developed under. As the clients upgraded to Excel 2002, 2003 and now some
of them to Excel 2007 the runtime seemed to recognize the new Excel
applications. I just assumed that the 2007 runtime would recognize what ever
version of Excel was installed even if it wasn't 2007. I am beginning to
think it isn't that smart. Is there a way we can change references on-the-fly
to see whive version of Excel they have and set the reference correctly?

I'd guess a references problem. Is this the first/only code to run?
Looking up the definition of the manifest constant vbCr in the
VBA library won't work if there is any kind of references problem.

(david)

I would agree that something strange is happening here but this code has
been
working for 8+ years. For what it is worth the following OpenRecordset is
not
working:

sSql = "SELECT * FROM tblSetupData " & vbCr
Set rstemp = dbMine.OpenRecordset(sSql, dbOpenSnapshot)



:

I suspect that some other strange thing is going on here.

I also VERY surprised that your code example works the with vbcr in your
text?

You don't need, and as far as I can tell, vbCr should not be used here at
all.

Also, do you have option explicit set on?

When you go debug->compile....does your code compile?

I would use something like:

sSql = "SELECT DISTINCT Mid([acct-char].[acct-nr]," & _
giFundStartPos & "," & giFundLength & ") AS FundNbr," & _
"Characteristics.description as FundName, " & _
"Characteristics.position into tblFundName" & _
" FROM Characteristics INNER JOIN [acct-char] ON " & _
"(Characteristics.abbrev = [acct-char].abbrev) " & _
" AND (Characteristics.position = [acct-char].position)" & _
" WHERE Characteristics.position=2"

The above has no line breaks, and the use of & _ shows how to "continue"
to
the next line....

 

[Tony Toews [MVP]]

I am starting to think it is a reference problem too but probably not what
you think. This application needs Excel. In the old version we distributed
Access 2000 runtime and of course referenced Excel 2000 as that is what it
was developed under. As the clients upgraded to Excel 2002, 2003 and now some
of them to Excel 2007 the runtime seemed to recognize the new Excel
applications. I just assumed that the 2007 runtime would recognize what ever
version of Excel was installed even if it wasn't 2007. I am beginning to
think it isn't that smart. Is there a way we can change references on-the-fly
to see whive version of Excel they have and set the reference correctly?

If you distributed your FE as an MDB then it should've been able to
cope with the different versions of Excel. Most of the time. However
possibly you're now distributing as an MDE?

To add to David's posting on Late Binding see the "Late Binding in
Microsoft Access" page at
http://www.granite.ab.ca/access/latebinding.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/