?
Solved

DAO.Recordset and Collection

Posted on 2005-04-04
9
Medium Priority
?
343 Views
Last Modified: 2013-12-25
Hi,

I would like to create a function called

Public Function DAOBuildCallCodeRecordSet(CallCodeCollection As Collection) As DAO.Recordset


So i need to build 2 records in the recordset

CallCodeID   CallCode
100              Urgent
101              Not urgent

This is my code.
------------------------------------------------------------------------------------------------------------------------------------------------
Public Function DAOBuildCallCodeRecordSet(CallCodeCollection As Collection) As DAO.Recordset
   
    Dim element As Variant
   
    mDAORecordSet.Fields.Append "mCallCodeID", adInteger, , adFldKeyColumn
    mDAORecordSet.Fields.Append "mCallCode", adVarChar, 255, adFldUpdatable
    mDAORecordSet.OpenRecordset
   
    For Each element In CallCodeCollection
        mDAORecordSet.AddNew
            mDAORecordSet!mCallCodeID = element.CallCodeID
            mDAORecordSet!mCallCode = element.CallCode
        mDAORecordSet.Update
    Next
   
    Set DAOBuildCallCodeRecordSet = mDAORecordSet
   
End Function

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



Can anyone give me some clues how to do this?...Thanks a lot
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
  • 5
  • 4
9 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13712244
so what is the problem.  That code looks prety close to me.

AW
0
 

Author Comment

by:TungVan
ID: 13716862

I have error "Type mistmatched" at
mDAORecordSet.Fields.Append "mCallCodeID", adInteger, , adFldKeyColumn

if i removed this line, i have same error at:
mDAORecordSet.Fields.Append "mCallCode", adVarChar, 255, adFldUpdatable
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13723539
try this change, and let me know what you get:

Public Function DAOBuildCallCodeRecordSet(CallCodeCollection As Collection) As DAO.Recordset
   
    Dim element As Variant

    Dim mDAORecordSet as DAO.Recordset
    Set mDAORecordSet = New DAO.Recordset

    mDAORecordSet.Fields.Append "mCallCodeID", adInteger, , adFldKeyColumn
    mDAORecordSet.Fields.Append "mCallCode", adVarChar, 255, adFldUpdatable
   
    mDAORecordSet.OpenRecordset
   
    For Each element In CallCodeCollection
        mDAORecordSet.AddNew
            mDAORecordSet!mCallCodeID = element.CallCodeID
            mDAORecordSet!mCallCode = element.CallCode
        mDAORecordSet.Update
    Next
   
    Set DAOBuildCallCodeRecordSet = mDAORecordSet
   
End Function

AW
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TungVan
ID: 13730044


It's said invalid use of new keyword...

I tried many times before...for dao.recordset, it does not let me declare it with "new" keyword..
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13730803
ok.  I guess that is because you cannot create a stand-alone recordset on its own.  Why do you need a recordset in the first place?  Tell me what you are tryi9ng to accomplish with your program, and this code in particular.

AW
0
 

Author Comment

by:TungVan
ID: 13732253

I have a class call CCallCode that has 2 attributes: CallCodeID as integer, CallCode as string

I have 2 databases classes:

CDAO (which interact directly with the database) and CDB (that interact in the middle btw the CDAO and the system)

CDAO -- CDB -- the rest of system


Hend CDAO does not know anything about CCallCode since it belongs to the rest of the system. However CDB must know structure of CCallCode.

In CDB, there are 2 operation:
Public Function DAOExtractCallCodeRecordSet(RS As DAO.Recordset) As Collection
Public Function DAOBuildCallCodeRecordSet(CallCodeCollection As Collection) As DAO.Recordset


DAOExtractCallCodeRecordSet: Get the recordset based on sql specific statement, and then extract it to a collection object. Then the rest of the system can use and modify that colllection object.


DAOBuildCallCodeRecordSet: Get the collection modified by the system and put it into a recordset so that I can insert into the database...







0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13732874
this seems a bit clumsy to me.  Why not simply have teh data Access layer return a Collection directly, rather than trying to pass a recordset around, especially when yoiu are trying create that recordset as unconnected, and on the fly.  This is very suspect to me, and it does not strike me as being standard procedure.

Once you have created a stand alone recordset, how do you plan to connect that recordset to the back-end database?

You usually create a connected recordset, even if it has no actual records, that is connected to the table.  Then you can create the new records with the data from the colection, and save it to the connected table in one fell swoop.

Also, is there a real reason why you are using DAO (which is rather old technology and now somewhat out of date), rather than ADO, which a much better database access technology, and usually will perform much better than DAO.

You did not say what backend database you are using.

AW
0
 

Author Comment

by:TungVan
ID: 13739264

>is there a real reason why you are using DAO (which is rather old technology and now somewhat out of date), rather than ADO, which a much better database access technology, and usually will perform much better than DAO?
I coded in ADO 2 weeks ago, and my ADOExtractCallCodeRecordSet, and ADOBuildCallCodeRecordSet are working good...
But I got notice that my project is required to use DAO, which is why i created CDAO class..

>Once you have created a stand alone recordset, how do you plan to connect that recordset to the back-end database?
I intent to call InsertRecordset function within CDAO class...

Do you have a suggestion for standard procedure?

It's better to have

CDB - the rest of the system

and remove CDAO class?


Thanks
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 750 total points
ID: 13739786
the middle tier (what you call CDAO), is intended to serve to manage Business Rules (so it MUST know about the properties that are relavent to the application in question.  YOu seem to be trying to create a GENERIC Business Rules layer, that knows NOTHING, but must be informed by means of the collecdtion that you pass to it.  While the concept might be laudable, in practive, this is going to be very difficult to implement and then to apply to a specific application.

What you need is a middle tier tailored to the application you are working on, but which EMBODIES the basic design concepts that you seem to be aware of.

I might sugget that you take a look at

Visual Basic 6 Business Objects
Rockford Lhotka
WROX Press
ISBN: 1861002076

check it out at:

http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=dK840nFBO7&isbn=1861002076&itm=4

AW
ISBN
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 Month10 days, 23 hours left to enroll

770 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