Dynamic RecordSet Issue

[SqlHunter]

Here When I write this function in a form and try to run the program by passing the sql statement and it gives me back the correct record set and works fine...Now I want to use this function as a generic one..So I tried putting this one in class module and module then it gave me an error at the rs.source and it exited the function.please show me how to make it generic so that I can use this as a dynamic one....

Dim Connect2db As New dbConnection
Public Function fnSQL_RS(strSQL As String) As adodb.Recordset
''On Error GoTo ErrorHandler
Dim rs As adodb.Recordset
'Create and set the record set information
MsgBox strSQL
Set rs = CreateObject("ADODB.Recordset")
Set rs = New adodb.Recordset
rs.ActiveConnection = Connect2db.conn
rs.CursorLocation = adUseClient
rs.Source = strSQL
MsgBox rs.Source
rs.Open strSQL, , adOpenDynamic, adLockOptimistic
MsgBox "hello"
Set fnSQL_RS = rs
Exit Function
ErrorHandler:
Set rs = Nothing
End Function

 

[SqlHunter]

Is there any other way where I can make this as a coomon function so that can be called from any forms...

 

[JerryKlmns]

If I were in your shoes....

Public Function fnSQL_RS(strSQL As String) As adodb.Recordset
On Error GoTo Err_Function

  Dim rs As adodb.Recordset
  Set rs = New adodb.Recordset
  With rs
     .ActiveConnection = Connect2db 'This is an open public connection 
     .CursorLocation = adUseServer 'This cursor allows adOpenDynamic cursor type
     .CursorType = adOpenDynamic
     .LockType = adLockOptimistic
     .Source = strSQL
     .Open
      Set fnSQL_RS = rs
  End With

Exit_Function:
  If Not rs Is Nothing then
     If rs.State= adStateOpen Then rs.Close
     Set rs = Nothing
  End If
  Exit Function

Err_Function:
    Msgbox "Error :" & Err.Number & vbCrLf & _
           "Description :"& Err.Description, vbOkOnly
    Resume Exit_Function
End Function

 

[SqlHunter]

But can I put this code under the class module or module so that it gives me the corressponding record and it can be called from any form in the project

 

[SqlHunter]

Here I tried adding this code to the modules and then called from one for like dbrecorset.fnSQL_RS(strSQL) where
dbrecorset is the module name and strSQL is sql statement it was giving an error at rs.open and was going to Err_Function:

Is there anything regarding the CursorLocation,CursorType ,
and LockType

 

[BobRodes]

This should work:

'in DbUtils class
Public Function fnSQL_RS(cn as ADODB.Connection, strSQL As String) As adodb.Recordset
  Dim rs As adodb.Recordset
  Set rs = New adodb.Recordset
  rs.CursorLocation = adUseClient
  rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
  Set fnSQL_RS = rs
End Function

'in Client
dim dbx as DbUtils
dim cn as adodb.connection
dim rs as adodb.recordset

set cn = new adodb.connection
set dbx = new DbUtils

with cn
   .connectionstring = "blah blah blah"
   .open
end with

set dbx = new DbUtils
set rs = dbx.fnSQL_RS("select * from blah", cn)

I wouldn't have the class use an external reference to a connection object for encapsulation reasons. In my own DbUtils class, I have a local connection object (rather than passing a reference to one as here), which generally suits my purposes best.

Also, I wouldn't use a dynamic recordset unless I absolutely had to. If you do have to, jerry quite rightly mentions that you have to have a server side recordset.

HTH

Bob