• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

Interesting ADO question

I have a custom query form that I am in the process of revamping.  Currently, the results displayed on the form are the results of a query that pulls from three different tables.  After the user enters parameters and presses the command button the query is created and appended to the queries collection.  I then populate the listboxes on the form based on assigning the rowsources property a Select Distinct from the query I just created.  The three tables Im pulling data from are now in Access, but will soon be part of a separate db on SQL server.

What I want to do, is recreate the same thing in ADO without the reliance on linked tables or stored queries.  I will create this in Access and then when the tables migrate to sql server, the only thing that I will need to change is the connection string.I have a fairly good working knowledge of using ADO, but there are some things Im not so sure about. For instance when I start this procedure and begin to populate listboxes, is is possible to assign an ADO recordset to the rowsource property of a listbox.  Also, since I have many different select satements I need to get and assign the values to the textboxes and listboxes on the form, should I use the same rst variable and just continue to open and close it with a new select statement every time, or does this degrade performance.  Or should I just open up a larger recordset and somehow run select statements against this recordset (if thats even possible).  Just to give you some semblance of the size of the recordset, currently in Access the query thats created pulls from three tables, contains about 15 colums, and anywhere between 2 and 60 records depending on the user parameters.

Performance is definitely an issue, I want it to be fast.  Although I know that when its in SQL server, using a stored procedure is the way to go, Im going to try to go with something that will work in Access too for now.

Basically, Im looking for some general guidance on strategies to use ADO effectively in this manner?
0
BillPowell
Asked:
BillPowell
  • 3
  • 3
1 Solution
 
stevbeCommented:
" is is possible to assign an ADO recordset to the rowsource property of a listbox"

Yes ... listbox and combobox now have a Recordset property (I think 2002 was the first version to support this)

"Using stored procedure"

You can set up parameters in Access and then build an ADO Command Object and Paramter objects and then assign a recordset from the Command.Execute and in fact when you do this you need to tell the Commmand object that it is going to be a stored proc (Access does an internal conversion) so this will scale perfectly when you upsize to SQL server.

    cmd.CommandType = adCmdStoredProc

One other thought would be to change your app to use SQL Server Compatible mode to help find the little things that will need to change in SQL statements ... instead of Like use ALike instead of * use % and instead of using ? use _

Steve
0
 
BillPowellAuthor Commented:
Thanks for the info Steve.  Glad you decided to sign in.

If you dont mind, I could use some more explanation on the stored procedure part of that.  Are you saying that when you specify acCmdStoredProc, if access detects that you are using jet, it will save it as a regular query.   I have to tell you upfront, I still use Access 2000.  Most of my organization is using 2003, but since there are still users of my app who dont have it, I have to stick with 2000 for the moment, so the new recordset property may not be something I can do right away.  Do you have any recommendations on how to proceed given my situation.
0
 
stevbeCommented:
I will show you a long winded example :-)

My central error handler uses a saved Access query and updates error information using an ado command object and parameters. Are you familiar with making parameter queries in Access? Right click on any grey space in the top section of a query and select Parameters, now type in a name and the appropriate data type. My exmaple is an append but the same concept works for selects. When this is all said and done and you upgrade to SQL Server then the only thing you should have to do is to make stored procs that have parameters and are named the same as the old queries and you will not have to change a single line of code.

so here is the SQL for a saved Access query ...

PARAMETERS ErrLogApp Text ( 255 ),
                     ErrLogMod Text ( 255 ),
                     ErrLogProc Text ( 255 ),
                    ErrLogNo Long,
                    ErrLogDesc Text ( 255 ),
                    ErrLogUser Text ( 255 );
INSERT INTO ztblErrLog ( ErrLogApp, ErrLogMod, ErrLogProc, ErrLogNo, ErrLogDesc, ErrLogUser )
SELECT [ErrLogApp] AS F1, [ErrLogMod] AS F2, [ErrLogProc] AS F3, [ErrLogNo] AS F4, [ErrLogDesc] AS F5, [ErrLogUser] AS F6;

now for the function that takes error values and uses this query ... it's kind of long but has all the details ...
the only difference is that you would also declare an ADO recordset object and use ...

Set rst = cmd.Execute

'------------------------------------------------------------------------------
'
'   Module:     basError
'
'   Notes:      Centralized error handler.
'
'-------------------------------------------------------------------------------
'   Environment
    Option Compare Database
    Option Explicit
    Option Base 0
'-------------------------------------------------------------------------------
'
'   Method: LogError
'
'   Notes:  Generic error handler called by all methods in the application.
'
'-------------------------------------------------------------------------------
Public Sub LogError(ByRef ErrLogMod As String, _
                    ByRef ErrLogProc As String, _
                    ByRef ErrLogNo As Long, _
                    ByRef ErrLogDesc As String, _
                    ByRef ErrLogDisp As Boolean)
'-------------------------------------------------------------------------------
'   variables
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
'-------------------------------------------------------------------------------
'   enable custom error handling
'   because this is an error handler we need to clear the error state first
    Err.Clear
    On Error GoTo ERR_LogError
'-------------------------------------------------------------------------------
'   make sure input arguments are clean
    ErrLogMod = Trim$(ErrLogMod)
    ErrLogProc = Trim$(ErrLogProc)
    ErrLogDesc = Trim$(Left$(ErrLogDesc, 255))
'-------------------------------------------------------------------------------
   
    'get the stored proc (query) to append to error log
    Set cmd = New ADODB.Command
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "zappErrLog"
    cmd.ActiveConnection = CurrentProject.Connection
   
    'set the app name
    Set prm = cmd.CreateParameter(Name:="ErrLogApp", _
                                  Type:=adChar, _
                                  Direction:=adParamInput, _
                                  Size:=Len(GetAppName), _
                                  Value:=GetAppName)
    cmd.Parameters.Append prm
   
    'set the module name
    Set prm = cmd.CreateParameter(Name:="ErrLogMod", _
                                  Type:=adChar, _
                                  Direction:=adParamInput, _
                                  Size:=Len(ErrLogMod), _
                                  Value:=ErrLogMod)
    cmd.Parameters.Append prm
   
    'set the procedure name
    Set prm = cmd.CreateParameter(Name:="ErrLogProc", _
                                  Type:=adChar, _
                                  Direction:=adParamInput, _
                                  Size:=Len(ErrLogProc), _
                                  Value:=ErrLogProc)
    cmd.Parameters.Append prm
   
    'set the error number
    Set prm = cmd.CreateParameter(Name:="ErrLogNo", _
                                  Type:=adInteger, _
                                  Direction:=adParamInput, _
                                  Value:=ErrLogNo)
    cmd.Parameters.Append prm
   
    'set the description
    Set prm = cmd.CreateParameter(Name:="ErrLogDesc", _
                                  Type:=adChar, _
                                  Direction:=adParamInput, _
                                  Size:=Len(ErrLogDesc), _
                                  Value:=ErrLogDesc)
    cmd.Parameters.Append prm
   
    'set the user
    Set prm = cmd.CreateParameter(Name:="ErrLogUser", _
                                  Type:=adChar, _
                                  Direction:=adParamInput, _
                                  Size:=Len(GetUserName), _
                                  Value:=GetUserName)
    cmd.Parameters.Append prm
       
    'add the error details to the log
    cmd.Execute , , adExecuteNoRecords

    'raise to calling procedure if not displayed to user
    If ErrLogDisp = True Then
        MsgBox Prompt:=ErrLogMod & "." & ErrLogProc & vbCrLf & ErrLogDesc, _
               Buttons:=vbOKOnly + vbCritical, _
               Title:="Unexpected Error"
    End If
   
'-------------------------------------------------------------------------------
EXIT_LogError:
    On Error Resume Next
    'cleanup
    Set prm = Nothing
    Set cmd = Nothing
    Exit Sub
'-------------------------------------------------------------------------------
ERR_LogError:
    MsgBox Prompt:=Err.Number & ": " & Err.Description, _
           Buttons:=vbOKOnly + vbCritical, _
           Title:="Unexpected Error"
    Resume EXIT_LogError
'-------------------------------------------------------------------------------
End Sub
'-------------------------------------------------------------------------------
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
stevbeCommented:
as for recordset object reuse ... I know that if you bind a form to a recordset in memory and then close and set=nothing the recordset object that Access actually keeps a version of that object in memory so the form still works ... looks very odd when you read the code but that is how it works. I am not sure if the same holds true for listbox recordsets but I would create seperate recordset objects (at the module level) and re-initialize them as necessary. I do not closde and set nothing after I bind soething to them because it is easier to understand when reading the code and I *bet* that even though it works that is only because Access is creating a seperate object so you are creating 2 times as much work. As for re-filtering a recordset object ... I have read numerous times that for both DAO and ADO you are better off opening a new recordset rather than filtering one that you already have.

Steve
0
 
BillPowellAuthor Commented:
Thanks Steve.  Definitely food for thought!
0
 
BillPowellAuthor Commented:
On a different note, heres another one to look at if you have some time:

http://www.experts-exchange.com/Databases/MS_Access/Q_21212266.html
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now