Problem in Creating record set as function

[MALICKA]

I am doing convertion from DAO TO ADO FOR AN INVENTORY PROJECT

I have problem in converting this function it gives compile error
I am sure i am going wrong some where but could't trace it

Your help/tips are very much appreciated

_____DAO ____
Public Function CreateRecordSet(sQuery As String) As Recordset
Set CreateRecordSet = db.OpenRecordset(sQuery, dbOpenDynaset)
End Function


____ADO________
Public Function CreateRecordSet(sQuery As String) As ADODB.Recordset
CreateRecordSet = CreateRecordSet.Open(sQuery, Conn, adOpendynamic, adLockOptimistic)
End Function


Thanks
-M

 

[SemperFiDownUnda]

The problem is that the different recordset objects (RDS,DAO,ADO, etc) are not compatible. They are just named the same. Similarly the same named methods do different things. ADO does not natively have a CreateRecordset method. Thus you must be calling a RDS, DAO or other data access method called CreateRecordset that returns their type of recordset and not an ADO recordset.

 

[MALICKA]

Tks for your Responce
Its user defined function just to create the recordset when the query string is passed

I know record set object are not compatable
But i just want to know how do i change to ado

 

[PraveenMenon]

Malicka,

Try this..

'=====================================================

Public Function retreiveRecords(sql As String) As Recordset

On Error GoTo execErr
Dim rst As Recordset
Set rst = New Adodb.Recordset
rst.Open sql, Cn, adOpenDynamic, adLockOptimistic, adCmdText
Set retreiveRecords = rst

Exit Function

execErr:

MsgBox "error....!!!!!!!!!!!" & vbNewLine & Err.Description

End Function
'==========================================================

All the best Praveen Menon

 

[SemperFiDownUnda]

CreateRecordSet.Open(sQuery, Conn, adOpendynamic, adLockOptimistic)

is that a class/method you wrote?

if you want to go from DAO to ADO you are going to have to touch almost all your data access code.

to create a recordset I'd do
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open sQuery, conn, adOpenDynamic, adLockOptimistic

ooops I see what you have done... do this

Public Function CreateRecordSet(sQuery As String) As ADODB.Recordset
  Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset
  rs.Open sQuery, conn, adOpenDynamic, adLockOptimistic
  set CreateRecordSet = rs
End Function

The error you are getting is because even though the function returns a recordset it doesn't actually instantiate the recordset, it only holds a pointer to one.

Sorry for me being confused before

 

[MALICKA]

HI Menon
i get type mismatch error when i equate the recordset to function
//Set retreiveRecords = rst

 

[PraveenMenon]

Please try this and let me know

'=====================================================
Public Function retreiveRecords(sql As String) As Adodb.Recordset

On Error GoTo execErr
Dim rst As Recordset
Set rst = New Adodb.Recordset
rst.Open sql, Cn, adOpenDynamic, adLockOptimistic, adCmdText
Set retreiveRecords = rst

Exit Function

execErr:

MsgBox "error....!!!!!!!!!!!" & vbNewLine & Err.Description

End Function
'=====================================================


All the Best
Praveen Menon

 

[MALICKA]

Hi Semper & Menon
Tks it worked perfectly
As semper said i have not changed the record set to adodb in calling function