?
Solved

DAO and recordset

Posted on 2005-03-29
9
Medium Priority
?
739 Views
Last Modified: 2013-11-26

Hi,

I have an error at line: Set mRecordSet = mConnection.OpenRecordset(SQLstatement, dbOpenDynaset). "Type mistmatch"

I am trying to create a function GetRecordSet. This function takes a SQL query, and returns a recordset based on that query.

I have to do it in VB6 and in DAO 3.6

------------------------------------------------------------------------------------------------------------------------------------------------
Private mWorkspace As Workspace
Private mConnection As Database
Private mRecordSet As Recordset


Public Function GetRecordSet(SQLstatement As String) As Recordset

    On Error GoTo RecordSetErrorHandler
   
    If mIDBType.DBStatus = False Then
        IDBType_OpenDatabase
    End If
   
    Set mRecordSet = mConnection.OpenRecordset(SQLstatement, dbOpenDynaset)
    Set GetRecordSet = mRecordSet
   
    Exit Function
   
RecordSetErrorHandler:
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If


End Function

------------------------------------------------------------------------------------------------------------------------------------------------




Thanks





0
Comment
Question by:TungVan
9 Comments
 
LVL 55

Expert Comment

by:Ryan Chong
ID: 13654009
try change:

Private mConnection As Database
Private mRecordSet As Recordset

to:

Private mConnection As DAO.Database
Private mRecordSet As DAO.Recordset

?
0
 

Author Comment

by:TungVan
ID: 13654517

When i set to
Private mConnection As DAO.Database
Private mRecordSet As DAO.Recordset

Public Function GetRecordSet(SQLstatement As String) As dao.Recordset

    On Error GoTo RecordSetErrorHandler
   
    If mIDBType.DBStatus = False Then
        IDBType_OpenDatabase
    End If
   
    Set mRecordSet = mConnection.OpenRecordset(SQLstatement, dbOpenDynaset)
    Set GetRecordSet = mRecordSet
   
    Exit Function
   
RecordSetErrorHandler:
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If


End Function

------------------------------------------------------------------------------------------------------------------------------------------------

I have an error at Exit Function. "Type mistmatch"

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13655525
Try passing the recordset in and out:

Private mConnection As DAO.Database

Public Function GetRecordSet(SQLstatement As String, mRecordSet As DAO.Recordset) As Boolean
    GetRecordSet = True
    On Error GoTo RecordSetErrorHandler
    If mIDBType.DBStatus = False Then
        IDBType_OpenDatabase
    End If
    Set mRecordSet = mConnection.OpenRecordset(SQLstatement, dbOpenDynaset)
    Exit Function
RecordSetErrorHandler:
    GetRecordSet = False
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Function
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 5

Expert Comment

by:IThema
ID: 13657485
...that is an option, but probably, the variable in which the return value for GetRecordSet is stored, is also declared as recordset, in stead of DAO.Recordset.

You can also avoid these problems by referencing only one database connectivity at a time.
ie, when you only need DAO, you should only reference DAO in your project references.
You may now also have ADO (Microsoft ActiveX Data Objects 2.x) referenced by your project. Since 'Recordset' may be both a DAO.Recordset or an ADO.Recordset, you may get 'type mismatch' errors, since a DAO.Recordset is not an ADO.Recordset.

Good luck,

Cheers
0
 
LVL 8

Accepted Solution

by:
craigwardman earned 200 total points
ID: 13657509
when your function returns, make sure the object receiving the return value is also a DAO.Recordset, not just Recordset as it was previously..

i.e.
Dim result as DAO.Recordset
result=GetRecordSet(SQL)

-------

also, I have always used:

mConnection.Execute(SQLstatement)

to get my results, but I normally use ADODB, so im not sure if this would affect it..
0
 
LVL 5

Expert Comment

by:IThema
ID: 13659614
A DAO.Database.Execute method does not return a Recordset. An ADO.Connection.Execute method does, but does not provide the flexibility for a recordset that you have when declaring ADO.Recordset objects and opening them using an SQL-command on an ADO.Connection object.

Cheers
0
 

Author Comment

by:TungVan
ID: 13661745


rehi,

craigwardman is right, i declared the object receiving the return value as recordset instead of dao.recordset...

Problem fixed

Thanks
0
 

Author Comment

by:TungVan
ID: 13661796


Just one last question..

Is it possible to use only "recordset" instead of "DAO.recordset" and "ADO.recordset".

This way, i can have 2 db classes: CDAO and CADO will use the same type of recordset


Thx
0
 
LVL 5

Expert Comment

by:IThema
ID: 13661823
That is not possible, because an ADO recordset is a different recordset than a DAO recordset... my previous post explains.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month9 days, 17 hours left to enroll

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question