?
Solved

DAO and recordset

Posted on 2005-03-29
9
Medium Priority
?
730 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 53

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
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.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

752 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