creating a ADODB Recordset and adding records... (1 Viewer)

[zozew]

Hi, All

Im trying to dynamically create a ADODB recordset and then add records and then set it as the record source for a form..

What i dont understand is the open statement for the recordset. As i have nothing to open...? until i have added records to it...so what am i missing...

thx

Zozew

 

[MarkK]

A recordset is most commonly used as an in-memory representation of data from a table. If you don't have a table or some other structured data source then using a recordset will be cumbersome. Why not create a table and put your data there? Then open the recordset normally. I expect this will be simplest.
Cheers,
Mark

 

[zozew]

Hi, Thx for the quick reply...

Well that's what i have done...and it works nicely.....but...there is always a but

As i have many users a problem occurs, when i refresh that particular form i delete the entries from the table and replace with new...so if i have many users online at the same time there is only one temp table and it gets refreshed with the records of the user who is looking at the last displayed record...and the other users "form" displaying that table will show "deleted" on all the rows...

So i thought if i made a dynamic recordSet that was only in the memory of the users computer it wouldn't effect the other users online at the same time....i have a front and separate backend (on a network) for my project

I hope i explained myself correctly....

Is there another way of accomplishing what im trying to do....? Basically basing a form on a recordset that is Dynamically created on that specific users computer....?

Just to clarify:
The form im talking about is on the mainForm and it displays some relevant data for the mainForms current record, i could use some listbox to accomplish the same...but its already working except for the issue explained above....

 

[Galaxiom]

This is what I use to create an empty recordset. (late binding):

In the Declarations section of the module. (Otherwise in the procedure the recordset would be dumped at the end)

 Dim rs as Object

Private Sub Form_Load
 
Set rs = CreateObject("ADODB.Recordset")
 
    With rs.Fields
        .Append "ID", adInteger
        .Append "fieldname", adVarChar, 200
        ' etc
    End With
 
    rs.Open , , adOpenDynamic, adLockOptimistic
 
Set Me.Recordset = rs
 
End Sub

Remember the ad constants must also be declared for late binding.
http://msdn.microsoft.com/en-us/library/ee266173(v=BTS.10).aspx

 

[zozew]

Thanks for the quick reply.

Ok i have never used the CreateObject method before in VBA. Lets see if i understand you correctly.

So i have to add the fields first in a "with" statement, can i then just use the addNew like this where etalsDisplayRecordSet is the empty recordset?:

With etalsDisplayRecordSet
    .AddNew
    !InmateID = etalsRecordSet!InmateID
    !LastName = etalsRecordSet!LastName
    !FirstName = etalsRecordSet!FirstName
    !MiddleName = etalsRecordSet!MiddleName
    !CaseNo = etalsRecordSet!CaseNo
    !Branch = etalsRecordSet!Branch
    !Crime = etalsRecordSet!Crime
    .Update
End With

This With statment is inside another With statement (etalsRecordSet)

 

[Galaxiom]

The CreateObject Method is for Late Binding which does not need the reference to Microsoft ActiveX Data Object. You can do it with Early Binding if you prefer. Early Binding is easier because won't require the constants to be specified and Intellisense will work.

Either way the recordset is created but has no fields because there is no Connection or Command property.

Fields are specified with the Append Method then you add the data after it is opened much like you have indicated.

One major difference is unlike DAO, ADO recordsets do not use the Edit Method. You can simply change the value of fields in the current record.

Note also that to Filter a form, apply the filter to the recordset. Attempting to filter the form itself will throw an error.

Finding and filtering records is done in the recordset using ADO methods. Find is more limited with ADO and it uses EOF instead of NoMatch. Read up on ADO if you need to do this.

Requery is also unusual being done by setting the form's recordset to itself.
Set Me.Recordset = Me.Recordset

 

[zozew]

Ok i get the gist of it...but early and late binding....Intellisense...what is that......?

Update:
I did some google search on it and it seems early binding is faster....so how would i do it as early binding...

 

[Galaxiom]

Intellisense...what is that......?

When you type the dot after a term and the options are dropped down for you. It only works with the Early Binding.

... it seems early binding is faster

Faster, but it relies on the client machine having the exact library you referenced. Late binding uses whatever appropriate library they have so generally causes far less trouble in the field on different machines.

how would i do it as early binding...

Under Tools References in the VB editor set a reference to Microsoft ActiveX Data Objects x.x

The Dim and Set lines become:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

 

[zozew]

Ok, so basically i've been doing early binding in my VBA coe until now...starting to undarstand the syntax. That's how i've been manipulating tabels data.

Great thanks and ill get right on to it...

 

[zozew]

Hi again...im running the code now and it seems to be populating the dynamic recordset.(did some testing) but when i try to assign that recordset to my form in the main Form with:

Forms!InmateEtals.Recordset = etalsDisplayRecordSet

It just error on cann't fin the "InmateEtals" form referred to in the VBA or macro...and then highlights the code above...I read somewhere that you can't use certain syntaxes for some methods..but i cant find it again...so...any idea on whats the problem...?

I've tried all kinds of syntax but i cant refer to the unbound form in the main form

Update:
I have removed the Forms RecordSource and left the fields in it refering to the same name fields in the dynamically created recordset (btw its beeing populated perfectly) Only thing now is that i can't seem to reference the form in the mainForm (from VBA) to display my new dynamically created recordset

 

[zozew]

just to show the code: Red is the Dynamically created rs and the blue is the line i get the error on...

Dim reportSql1 As String
Dim reportSql2 As String
Dim reportSqlFull As String
Dim cnnX As ADODB.Connection
Set cnnX = CurrentProject.Connection

Dim etalsDisplayRecordSet As New ADODB.Recordset
Dim myRecordSet As New ADODB.Recordset
Dim etalsRecordSet As New ADODB.Recordset
[COLOR=Red]
With etalsDisplayRecordSet.Fields
    .Append "InmateID", adInteger
    .Append "LastName", adVarChar, 200
    .Append "FirstName", adVarChar, 200
    .Append "MiddleName", adVarChar, 200
    .Append "CaseNo", adVarChar, 200
    .Append "Branch", adVarChar, 200
    .Append "Crime", adVarChar, 200
End With

etalsDisplayRecordSet.Open , , adOpenDynamic, adLockOptimistic[/COLOR]

myRecordSet.ActiveConnection = cnnX
etalsRecordSet.ActiveConnection = cnnX


myRecordSet.Open "SELECT tblInmateCases.*, tblBranches.Branch FROM tblBranches RIGHT JOIN tblInmateCases ON tblBranches.BranchID = tblInmateCases.Branch WHERE (((tblInmateCases.InmateID)=" & Me!InmateID & "))"

With myRecordSet
    Do Until .EOF
    etalsRecordSet.Open "SELECT tblInmateCases.InmateID, tblInmateCases.Crime, tblBranches.Branch, tblInmateCases.CaseNo, tblInmatesProfile.LastName, tblInmatesProfile.FirstName, tblInmatesProfile.MiddleName FROM tblBranches INNER JOIN (tblInmatesProfile INNER JOIN tblInmateCases ON tblInmatesProfile.InmateID = tblInmateCases.InmateID) ON tblBranches.BranchID = tblInmateCases.Branch"
    'MsgBox ![CaseNo]
            With etalsRecordSet
                Do Until .EOF
                    'etalsDisplayRecordSet.Open ""
                    'Exit Sub
                    If myRecordSet![CaseNo] = ![CaseNo] And myRecordSet![InmateID] <> ![InmateID] Then
                       [COLOR=Red] With etalsDisplayRecordSet
                            .AddNew
                            !InmateID = Nz(etalsRecordSet!InmateID, "")
                            !LastName = Nz(etalsRecordSet!LastName, "")
                            !FirstName = Nz(etalsRecordSet!FirstName, "")
                            !MiddleName = Nz(etalsRecordSet!MiddleName, "")
                            !CaseNo = Nz(etalsRecordSet!CaseNo, "")
                            !Branch = Nz(etalsRecordSet!Branch, "")
                            !Crime = Nz(etalsRecordSet!Crime, "")
                        End With[/COLOR]
                    End If
                .MoveNext
                Loop
                .Close
            End With
        .MoveNext
        Loop
    .Close
End With
[COLOR=Blue]Me.InmatesEtals.Form.Recordset = etalsDisplayRecordSet[/COLOR]
'InmatesEtals.Form.Requery
End Sub

 

[CBrighton]

You are using "Me.InmatesEtals.Form.Recordset". This is looking for a subform control called "InmateEtals" within the form where the code is being run. It is then setting it's recordset.

Is this what you are trying to do, to assign your recordset to a subform on the form where the code is run?

 

[Galaxiom]

The recordset is an object so it needs the Set keyword.

Set Me.InmatesEtals.Form.Recordset = etalsDisplayRecordSet

 

[CBrighton]

The recordset is an object so it needs the Set keyword.

Set Me.InmatesEtals.Form.Recordset = etalsDisplayRecordSet

This is incorrect, RecordSet does not require the Set keyword.

Here is a working example from one of my databases without Set:

strRecordSource = "SELECT tblCascade.CascadeStage, "
    strRecordSource = strRecordSource & "[tblagent].[Forename] & ' ' & [tblagent].[surname] AS ToName, "
    strRecordSource = strRecordSource & "tblCascade.CascadeTo, "
    strRecordSource = strRecordSource & "tblAgent.Forename, "
    strRecordSource = strRecordSource & "tblAgent.Surname, "
    strRecordSource = strRecordSource & "tblAgent.Location, "
    strRecordSource = strRecordSource & "tblAgent.DepartmentID, "
    strRecordSource = strRecordSource & "tblAgent.HomePhone, "
    strRecordSource = strRecordSource & "tblAgent.MobilePhone, "
    strRecordSource = strRecordSource & "tblAgent.WorkPhone, "
    strRecordSource = strRecordSource & "tblAgent.NextOfKin, "
    strRecordSource = strRecordSource & "tblAgent.NextOfKinRelationship, "
    strRecordSource = strRecordSource & "tblAgent.NextOfKinPhone, "
    strRecordSource = strRecordSource & "tblAgent.NextOfKinMobile, "
    strRecordSource = strRecordSource & "tblAgent.RAG, "
    strRecordSource = strRecordSource & "tblAgent.SystemKnowledge, "
    strRecordSource = strRecordSource & "tblAgent.HomeTown "
    strRecordSource = strRecordSource & "FROM tblAgent "
    strRecordSource = strRecordSource & "INNER JOIN tblCascade "
    strRecordSource = strRecordSource & "ON tblAgent.FileNumberID = tblCascade.CascadeTo "
    strRecordSource = strRecordSource & "WHERE tblCascade.CascadeTo = '" & Nz(Node.Tag, 0) & "' "
    strRecordSource = strRecordSource & "ORDER BY tblCascade.CascadeStage;"
    Forms!frmCascade!objSub1.Form.RecordSource = strRecordSource

:edit:

I've now noticed we are talking about recordset rather than recordsource...

In which case Set may be required, I always use RecordSource!

 

[Galaxiom]

I've now noticed we are talking about recordset rather than recordsource...

In which case Set may be required, I always use RecordSource!

The misunderstanding is not surprising. I suspect the use of disconnected recordsets is not particularly common.

When I was learning to use ADO recordsets like this I came across a post on another site that suggested the developer asking the question was "one of about four people in the world doing this."

That comment was undoubtedly an exaggeration. In fact disconnected ADO recordsets are an incredibly useful technique that I would thoroughly recommend all developers become familiar with.

 

[Galaxiom]

Sorry it is off the topic but I couldn't resist commenting on this excellent example of what must be one of the most common yet bizzare concepts used in coding. Not only is something like sixty to seventy percent of the code pointless repetion of the variable name it is also quite inefficient to process as the variable is repeatedly read and rewritten.

strRecordSource = "SELECT tblCascade.CascadeStage, "
    strRecordSource = strRecordSource & "[tblagent].[Forename] & ' ' & [tblagent].[surname] AS ToName, "
    strRecordSource = strRecordSource & "tblCascade.CascadeTo, "
    strRecordSource = strRecordSource & "tblAgent.Forename, "
    strRecordSource = strRecordSource & "tblAgent.Surname, "
    strRecordSource = strRecordSource & "tblAgent.Location, "
    strRecordSource = strRecordSource & "tblAgent.DepartmentID, "
    strRecordSource = strRecordSource & "tblAgent.HomePhone, "
    strRecordSource = strRecordSource & "tblAgent.MobilePhone, "
    strRecordSource = strRecordSource & "tblAgent.WorkPhone, "
    strRecordSource = strRecordSource & "tblAgent.NextOfKin, "
    strRecordSource = strRecordSource & "tblAgent.NextOfKinRelationship, "
    strRecordSource = strRecordSource & "tblAgent.NextOfKinPhone, "
    strRecordSource = strRecordSource & "tblAgent.NextOfKinMobile, "
    strRecordSource = strRecordSource & "tblAgent.RAG, "
    strRecordSource = strRecordSource & "tblAgent.SystemKnowledge, "
    strRecordSource = strRecordSource & "tblAgent.HomeTown "
    strRecordSource = strRecordSource & "FROM tblAgent "
    strRecordSource = strRecordSource & "INNER JOIN tblCascade "
    strRecordSource = strRecordSource & "ON tblAgent.FileNumberID = tblCascade.CascadeTo "
    strRecordSource = strRecordSource & "WHERE tblCascade.CascadeTo = '" & Nz(Node.Tag, 0) & "' "
    strRecordSource = strRecordSource & "ORDER BY tblCascade.CascadeStage;"
    Forms!frmCascade!objSub1.Form.RecordSource = strRecordSource

I can't comprehend why any developers persist with iterative concatenation instead of continuation which would read something more like this.

strRecordSource = "SELECT tblCascade.CascadeStage, " _
                & " [tblagent].[Forename] & ' ' & [tblagent].[surname] AS ToName,"
                & " tblCascade.CascadeTo," _
                & " tblAgent.Forename," _
                & " tblAgent.Surname," _
                & " tblAgent.Location," _
                & " tblAgent.DepartmentID," _
                & " tblAgent.HomePhone," _
                & " tblAgent.MobilePhone," _
                & " tblAgent.WorkPhone," _
                & " tblAgent.NextOfKin," _
                & " tblAgent.NextOfKinRelationship," _
                & " tblAgent.NextOfKinPhone," _
                & " tblAgent.NextOfKinMobile," _
                & " tblAgent.RAG," _
                & " tblAgent.SystemKnowledge," _
                & " tblAgent.HomeTown" _
                & " FROM tblAgent" _
                & " INNER JOIN tblCascade" _
                & " ON tblAgent.FileNumberID = tblCascade.CascadeTo" _
                & " WHERE tblCascade.CascadeTo = '" & Nz(Node.Tag, 0) & "'" _
                & " ORDER BY tblCascade.CascadeStage;"
 
Forms!frmCascade!objSub1.Form.RecordSource = strRecordSource

BTW Note how I also move the space to the beginning of the next line where it is more obvious. (I would also alias the table names and but that is not the point I am making.)

 

[CBrighton]

There is a limit to the amount of times you can use _ & [newline] in a single . I used to use it until I hit that limit once and it refused to compile.

For consistency and readability I try to do things the same way each time, so this is the way I ended up using for this.

I'm not sure if that particular piece of code is too long for your suggested method, but I have come across it before and that's what caused me to change how I ocded long variables.

As for space as a prefix rather than suffix, other websites, etc which I have seen agree with you but I am in the habit of doing it at the end. I'll give you that one, your way is the standard in this case.

This piece of code sets the rowsource for a sub-form and it happens instantly. It's readable, functional and quick. That's enough for me.

 

[zozew]

To comment your "off topic" as an old flash developer i was really surprised to see how VBA was being coded when it comes to giving variables values particularly string values. I understand the need to split them up because of the lengthy SQL strings but still.

In ActionScript (Flash's native language) they have a simple "varName =+ addedValue" to add method if you feel the need to brake things up. Anyway I still have tons to learn about VBA so thanks for all your input.

You are using "Me.InmatesEtals.Form.Recordset". This is looking for a subform control called "InmateEtals" within the form where the code is being run. It is then setting it's recordset.

Yes my form is on the mainForm and called InmatesEtal.

I've tried all possible paths to the form and i get a bunch of error ranging from error 2450 to error 91 and so on.

So it all seems a bit strange almost like the form is not there....although its clearly present and open.

But as always if you cant solve your problem try another way...I've solved my problem by removing the tempTable from the backend and putting it locally in the frontend of each user that way the form is not beeing affected by other users record browsing..

Thanks for showing how to use and create empty recordsets dynamically..im sure it will come in handy

Zozew

ps. i will still try and understand why i can't set the recordset or refer to the form....

 

[Galaxiom]

There is a limit to the amount of times you can use _ & [newline] in a single . I used to use it until I hit that limit once and it refused to compile.

The limit is 24 continuations. In 2007 the VBA Editor gives a warning when limit this is reached on entry without having to wait for compilation.

Of course it is quite possible to break the continuation by starting a line with a concatenation than continuing on again. It can be a tidy way to split up the SQL generation between subqueries or between the field listing and FROM clauses.

Indeed the subqueries can be generated as separate strings and then concatenated.

Moreover, without the wasted space used in double repetition of the variable name on every line, it is often quite practical to list several fields on each line. It is rare to have more than 24 fields in a query anyway.

For consistency and readability I try to do things the same way each time, so this is the way I ended up using for this.

I will accept that if you leave out "readability" because all the clutter in the iterative concatenation certainly does not enhance readability.

 

[Galaxiom]

Yes my form is on the mainForm and called InmatesEtal.

That may be the name of the form object itself but did you refer to the name of the subform control on the main form? Its name may be different from the form you are using as its Source Object.

i will still try and understand why i can't set the recordset or refer to the form....

Did you use the Set keyword as I indicated earlier? It is definitely required.