Below is my Stored Procedure and my VB code used to generate the
SQL-Passthrough query from within Access. I am hoping this will help people
see what is happening:
CREATE PROCEDURE usp_gsmac_outstanding
AS
/*This stored procedure is a first step in recreating the outstanding
exceptions query.
*This stored procedure is to take the place of the Outstanding Exceptions
query used
*by Access for the detail reports.
*Note: for the ExceptionType field if a '~' is passed only those records
that have been
*populated will be returned, if a '!' or NULL only records that have been
populated will be passed
*if a '%' is passed all records will be returned.
*Created 11/06/1998 by DSS*/
SELECT tbl_gsmac.LOC,
tbl_gsmac.FUND,
datediff(dd, ReconDate, getdate()) AS Ageddays,
case
when datediff(dd, ReconDate, getdate()) < 30 then 1
else 0
end AS Day030,
tbl_gsmac.SPECIAL_INSTRUCTIONS as SwiftComment,
tbl_gsmac.SAFEKEEPING,
tbl_gsmac.gsmac,
tbl_gsmac.SUB,
tbl_gsmac.VARIANCE,
Abs(Variance) AS Varsort,
tbl_regions.Country,
tbl_regions.Bank,
tbl_regions.area,
tbl_regions.Region,
case
when SafCmmnt is null then convert(text, '')
else SafCmmnt
end AS ISOComments1,
case
when SafCmmntBy is null then ''
else ' By: ' + ltrim(rtrim(SafCmmntBy)) + ' on: ' + convert(varchar(11),
SafCmmntDte)
end as ISOComments2
FROM
RDB.dbo.tbl_gsmac tbl_gsmac
left outer join
rdb..ExceptionType ExceptionType
on
tbl_gsmac.EXCEPTIONTYPE = ExceptionType.ExceptionType
inner join
rdb..tbl_regions tbl_regions
on
tbl_gsmac.loc = tbl_regions.loc
where
and
and
and
and
or
or
and
or
or
or
or
and
RESOLUTIONDATE Is Null
and
tbl_gsmac.Safekeeping = 1
and
and
and
(TBL_regions.Safekeep_imp=1
OR
TBL_regions.Manual_Safekeep=1)
output:
GO
Access code:
Function BuildSQL(ByVal ExcType As String)
'****************************************************************
'*This function will build the SQL Passthrough query to be used *
'*by the openexceptions report(s). *
'*Created 12/16/98 by Dan Smith. *
'****************************************************************
On Error GoTo buildsqlerr
Dim sql As String
Dim myspace As Workspace
Dim mydb As Database
Dim rec_Outstanding As Recordset
Dim qry_Outstanding As QueryDef
Dim prpNew As Property
Set myspace = DBEngine.Workspaces(0)
Set mydb = myspace.Databases(0)
Select Case ExcType
Case "Safekeeping"
sql = "exec rdb_proc..usp_gsmac_outstanding "
Case "Registration"
sql = "exec rdb_proc..usp_gsmac_outstanding_Reg "
End Select
If Me.Loc = "*" Then
Else
End If
If Me.Fund = "*" Then
Else
End If
If Me.Settle = "*" Then
Else
End If
If Me.Reg = "*" Then
Else
End If
If Me.sleeve = "*" Then
Else
End If
If Me.asset = "*" Then
Else
End If
If Me.Clearance = "*" Then
Else
End If
Select Case STATUS
Case 1
'All
Case 2
'Uncoded
Case 3
'Coded
End Select
mydb.QueryDefs.Delete "q_Outstanding"
'*Build query
Set qry_Outstanding = mydb.CreateQueryDef("q_Outstanding")
qry_Outstanding.Connect = "ODBC;DSN=" & sDSNName
qry_Outstanding.ODBCTimeout = 0
'Set prpNew = qry_Outstanding.CreateProperty("LogMessages", _
' dbBoolean, True)
'qry_Outstanding.Properties.Append prpNew
qry_Outstanding.sql = sql
'qry_Outstanding.Execute dbSQLPassThrough
mydb.Close
myspace.Close
Exit Function
buildsqlerr:
If Err.number = 3011 Then
'*Object doesn't exist continue on
Resume Next
ElseIf Err.number = 3265 Then
Resume Next
Else
Dim errrs_value As Variant
errrs_value = errrs("buildsql", "", GSMACDir, "tbl_stderror", Err,
Error$)
DoCmd.SetWarnings True
DoCmd.Hourglass False
End If
Exit Function
Resume 0
End Function
> I am in the process of rolling out an upgrade to 200 + users of one of
> my applications and started receiving the above error message. The
> upgrade is mostly on the client side where I went from Access 2.0 to
> Access 97. On the server side I upgraded from Service Pack 1 to Service
> Pack 5a of SQL 6.5. The above error message occurs when the users try
> to run various reports, all of which use SQL Passthrough to a Stored
> Procedure. Before the upgrade these reports ran off of attached tables.
> There were no changes related to user ids or permissions in this
> upgrade which is why the Not a valid account name or password message is
> so perplexing. To make it even more complicated it doesn't happen on
> all of the computers all of the time, but we can occasionally recreate
> it.
> Thanks in advance for any and all help you can provide.
> Regards
> Dan Smith
by