[AccessD] Trouble with SQL statement on two difefrent machines

Connie,

  Is tblCAuthorityType and ODBC linked table?  If so, then ODBC drivers come
into play and you most likely have two different driver versions between the
machines.

  If not, you've covered most everything else.  Only other thing I can think
of is that you might want to try using /decompile on the DB, then try again
on the problem machines (make sure you do this on a backup copy).

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
connie.kamrowski at agric.nsw.gov.au
Sent: Saturday, March 26, 2005 10:27 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Trouble with SQL statement on two difefrent machines



Hi All,

I am havinbg an issue with a function and could use some fresh eyes and
knowledge. Both machines are running Access97 SR2. On my machine the
function works fine, on the users machine it falls over. We are accessing
the database from a central location.

Function as follows:

<code>
Private Function getstrNextAuthorityNumber() As String
'Automatically select the next availaible Authority Number for this type of
Authority
On Error GoTo Failure

Dim dbNextAuthority As Database
Dim rsNextAuthority As Recordset

Dim intNextAuthorityNumber As Integer

Dim strSelect As String


strSelect = "SELECT tblCAuthorityType.AuthorityPrefix,
Max(Right([AuthorityNumber],5)) AS [Number] " & _
                        "FROM tblCAuthorityType RIGHT JOIN tblDAuthority ON
tblCAuthorityType.AuthorityTypeID = tblDAuthority.AuthorityType " & _
                        "GROUP BY tblCAuthorityType.AuthorityPrefix " & _
                        "HAVING
(((tblCAuthorityType.AuthorityPrefix)=getgstrAuthorityPrefix()));"

On Error Resume Next

    Set dbNextAuthority = CurrentDb()
    Set rsNextAuthority = dbNextAuthority.OpenRecordset(strSelect,
dbOpenSnapshot, dbFailOnError)

        If rsNextAuthority.RecordCount < 1 Then 'No records - this will not
actually be used.
            intNextAuthorityNumber = 1 'Initial Authority Number for each
type
        Else
            intNextAuthorityNumber = rsNextAuthority!Number + 1 'Increment
last Authority Number by 1
        End If 'rsNextAuthorityNumber.RecordCount = 0

        getstrNextAuthorityNumber = getgstrAuthorityPrefix() & Right("0000"
& Trim(Str(intNextAuthorityNumber)), 5)

ExitRoutine:
        On Error Resume Next
            rsNextAuthority.Close
            Set rsNextAuthority = Nothing
            dbNextAuthority.Close
            Set dbNextAuthority = Nothing
        Exit Function

Failure:
        Call ErrorHandler(lngErrorNumber:=Err.Number,
strErrorDescription:=Err.Description, strErrorSource:=Err.Source)
        Resume ExitRoutine

End Function
<code>

This fails at the bold line, on my machine it takes the SQL statement in
strSelect and goes to the getgstrAuthorityPrefix() function as defined on
the end, returning a value. On the users machine it goes straight to the
next line and misses the call to getgstrAuthorityPrefix() altogether
returning a Bliock Obejct variable not set.

This is code written by my predecessor. All ideas appreciated.


Connie Kamrowski

Analyst/Programmer
Information Technology
NSW Department of Primary Industries
Orange



This message is intended for the addressee named and may contain
confidential information. If you are not the intended recipient or received
it in error, please delete the message and notify sender. Views expressed
are those of the individual sender and are not necessarily the views of
their organisation.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com