Setting a Data objects recordset  or recordsource in code

Posted on 2005-04-08
Last Modified: 2012-05-05
I am trying to set a data objects recordsource in code. Basically I am trying to execute a query, return a recordset from the query, and set that to the data object here is my code:

Dim strQuery As String, rsResult As Recordset

On Error GoTo activate_error
strQuery = "SELECT Client.CaseID, Contact.ContactID, Contact.NameF, Contact.NameL, Contact.Address, Contact.City, Contact.State, Contact.Zip, Contact.Phone, Contact.Fax, Contact.Email, Contact.ISP_Member FROM (Client LEFT JOIN ConClient ON Client.CaseID = ConClient.CaseID) LEFT JOIN Contact ON ConClient.ContactID = Contact.ContactID WHERE Client.CaseID = 112578"
'Set rsResult = Project1.Conn.Execute(strQuery)
Set rsResult = dataContact.Database.OpenRecordset(strQuery)

This gives me a type mismatch error. I need to view what information is in the rsResult recordset. I want to do this with the data control so I can link the recordset to text boxes and view the data. Someone please help!!
Question by:wilsoada
    LVL 12

    Expert Comment

    What is "dataContact"
    Where are you creating and setting the connection object?

    LVL 10

    Accepted Solution

    Hi, wilsoada.

    Bob's question is a good one.  I don't use DAO; I work with ADO.  In ADO I would need to set up the following code to read from a database:

         Dim MyConn As ADODB.Connection
         Dim MyRecSet As ADODB.Recordset
         Set MyConn = New ADODB.Connection
         Set MyRecSet = New ADODB.Recordset
         MyRecSet.LockType = adLockOptimistic
         MyRecSet.CursorLocation = adUseClient
         MyRecSet.CursorType = adOpenDynamic  
         MyConn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=C\:db.mdb"
         Set MyRecSet = MyConn.Execute("SELECT * FROM Notes")

    My example only reads from one table ("Notes") in my "db.mdb" database.  I'm not asking you to switch from DAO to ADO, but with your current coding, don't you need to do Dim a Connection object (e.g. in my code I DIM and SET "MyConn")?

    Author Comment

    I don't think you guys understood the question. The dataContact is a data object in VB6. The ADO connection and everything works fine and executes the SQL statement creates the recordset fine. I am trying to tie the recordset into the data object so I can use it view the data in the recordset. If there is another way to do this please let me know.
    LVL 10

    Expert Comment

    Can you share the code/steps you took to create/set up the "data object" (which I'm guessing is "dataContact")?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    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…
    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…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now