Fabricated ADO recordset - adding records (1 Viewer)

mdlueck · Dec 24, 2013

Galaxiom said:
mdlueck said:

Oh... so does that means you do not use ADO.Command objects to execute Stored Procedures, resulting in an ADO.Recordset, which you then bind to a Multiple Items / Continuous style form?

Click to expand...

I have that but I had not tried updateable functionality.

I was not trying to provide update capabilities. Rather read-only view of multiple qualifying records.

So, how are you able to achieve such for read-only display of records?

Or do you run your queries through ADO.Recordset objects and not ADO.Command objects?

Please post some example code.

Galaxiom · Dec 26, 2013

I create a recordset using the ADODB command to access the parameterised Stored Procedure.

This is the relevant part of the function I use to return the recordset.

Code:

    Set cmd = CreateObject("ADODB.Command")
    Set rst = CreateObject("ADODB.Recordset")
 
    With cmd
        Set .ActiveConnection = MyConnectionObject
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.StoredProcedureName"
    End With
 
    With cmd.Parameters
        .Append cmd.CreateParameter("@Member", adInteger, adParamInput, , {FunctionArgument1})
{etc}
    End With
 
    rst.Open cmd, , adOpenStatic, , adCmdStoredProc
    rst.ActiveConnection = Nothing

These are the settings I use in the connection object.

.Mode = adModeRead
.CursorLocation = adUseClient

Note the recordset CursorLocation is inherited from the connection object.

The recordset works either connected or disconnected.

SQL Server 2005 Standard.
Access 2007 on XP and 2010 on Win7.
mdb front end.

Hope this helps.

mdlueck · Dec 27, 2013

Galaxiom, your slight of hands is indeed intreaguing... I had no idea that I could load up an adoCMD object and then create an adoRS object, feed it the adoCMD object having the adoRS object actually execute what had been loaded into the adoCMD object in the first place.

Please see the updated example prototype attached to this post: ADO_MIF_2.zip

And the code within the form:

Code:

Option Compare Database
Option Explicit

Private adoRS As Object

Private Sub Form_Open(Cancel As Integer)

  Dim adoCMD As Object
  Dim strSQL As String

  strSQL = "SELECT [t].[pid],[t].[quoteid],[t].[metflg],[t].[metid],[t].[mettitle],[t].[toolstatusid],[t].[tooltypetitle],[t].[partnumber],[t].[parttitle],[t].[partvendortitle],[t].[toolstatustitle],[t].[lttotal],[t].[toolduedate],[t].[besttoolcost],[t].[prodpartflg]" & vbCrLf & _
           "FROM [tblRptPartsToolCost] AS [t]" & vbCrLf & _
           "ORDER BY [t].[partnumber]"

  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .CommandText = strSQL
  End With

  Set adoRS = CreateObject("ADODB.Recordset")
  With adoRS
    .ActiveConnection = Nothing
    .CursorLocation = adUseClient
    .Open Source:=adoCMD, _
          CursorType:=adOpenStatic, _
          Options:=adCmdText
  End With

  Set adoCMD = Nothing

End Sub

Private Sub Form_Load()

  Set Me.Recordset = adoRS

End Sub

Private Sub Form_Close()

  Set adoRS = Nothing

End Sub

I needed to interact with the adoRS object in a slightly different order of operation than your example code. If I tried to nuke the connection once the adoRS object was already open, it grumbled at me. This order of LOC's works in A2007.

So, next question is how to set the Form to a longer height than it keeps resetting to?

Otherwise, looks like a very promising prototype. Thank you for assisting me.

Galaxiom · Dec 27, 2013

mdlueck said:
I needed to interact with the adoRS object in a slightly different order of operation than your example code. If I tried to nuke the connection once the adoRS object was already open, it grumbled at me.

Disconnected recordsets are often opened with the data loaded and a adLockBatchOptimistic lock before the connection is removed. Then you are supposed to be able to reconnect after editing the data and BatchUpdate the changed data back to the source. (Note this doesn't work when bound to a form though because the update is run by the form.)

Your experience doesn't seem to fit with that at all.

However I have experienced some bewildering differences between what happens with SQL Server and what have tried with Access. Maybe that is the key.

One thing to note is that CurrentProject.AccessConnection is recommended rather than CurrentProject.Connection when the source is Access, especially when working with bound forms. Maybe try that.

So, next question is how to set the Form to a longer height than it keeps resetting to?

This should not be affected by the recordset. I would try copying the controls to a new form and see if that made any difference.

Otherwise, looks like a very promising prototype. Thank you for assisting me.

No worries Michael. It is good to see someone else having a go at this ADO stuff. It still seems a bit of a black art with many aspects still not fully explored, at not least by anyone sharing what they have found.

My usage of ADO has been somewhat kludging. I have fabricated custom recordsets as required for the case at hand and loaded data in the brute force ways you mentioned. What is needed is a versatile class that configures itself to the original data structure.

This time I have made some breakthroughs working with disconnected recordsets thus avoiding the Nullable limitation in fabricated recordsets.

For one I have managed implement a record selection checkbox bound to a disconnected recordset without including the boolean field in the form's original recordsource. It is a lot cleaner than what I have previously done with fabricated recordset. This task is traditionally done with a temp table.

I will work this up into a class. I think others could find this very useful.

mdlueck · Dec 28, 2013

Galaxiom said:
One thing to note is that CurrentProject.AccessConnection is recommended rather than CurrentProject.Connection when the source is Access, especially when working with bound forms. Maybe try that.

I put a Watch on the CurrentProject object and compared the two connection subobjects. On A2007, Connection has V12 attribute things whereas AccessConnection has V10 attribute things. I think I will stick with using Connection.

The test application worked the same either way.

Galaxiom said:
This (Form height) should not be affected by the recordset. I would try copying the controls to a new form and see if that made any difference.

I found this post:

"trying to set form size"
http://www.access-programmers.co.uk/forums/showthread.php?t=112864#post514864

And coded this up based on that:

Code:

Private Sub Form_Load() 
 
  With Me 
    .InsideHeight = 8400 
    '.InsideWidth = 2200 
    Set .Recordset = adoRS 
  End With 
 
End Sub

Works great.

I have tried building this form twice from scratch, and each time I remove the Form's Record Source property, then the height drops to the bare minimum.

Snooping inside the Form's Me object, I see prior to the height getting set, it has an initial value of 1575. Seems I would like it at 8400, thus the update of the property does exactly what I expect.

Usually I do not work with Multiple Items / Continuous forms with the Form \ Data \ Record Source property blanked out, as I work with bound forms for this type of form, binding them to FE temp tables.

Now, I just noticed something. Since I am working with a test database file and a local Access table, obviously the table really is in the local DB file. So I tried putting the Form \ Data \ Record Source back to the local Access table. That solved the form height problem. I can successfully leave commented out the InsideHeight property setting LOC. When I am working in Client / Server mode without a Linked nor FE temp table, there will be no table to leave the form bound to, so in that case I will rely on Mr. Larson's example I discovered.

mdlueck · Jan 3, 2014

I was able to translate what I had working against the local Access FE DB and have the same code scheme execute a Stored Procedure. I also found that I could simplify the code slightly, doing all of the work in the Form Open event, and no longer need the additional Form Load event.

Code:

Option Compare Database
Option Explicit

Private adoRS As Object

Private Sub Form_Open(Cancel As Integer)

  Dim adoCMD As Object
  Dim adoRS As Object

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.ADODBConnectionObj()
    .CommandText = "clsObjProductsTbl_RefreshLocalTmpTbl_All"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@projectid").Value = 8
  End With

  Set adoRS = CreateObject("ADODB.Recordset")
  With adoRS
    .ActiveConnection = Nothing
    .CursorLocation = adUseClient
    .Open Source:=adoCMD, _
          CursorType:=adOpenStatic, _
          Options:=adCmdStoredProc
  End With

  With Me
    .InsideHeight = 8400
    '.InsideWidth = 2200
    Set .Recordset = adoRS
  End With

  Set adoCMD = Nothing

End Sub

Private Sub Form_Close()

  Set adoRS = Nothing

End Sub

Notes: Still the behavior is for the form to default to a very minimum height, so I need to force it taller. A further side effect of that behavior is to not center the form correctly on the screen. Is there anyway to manually invoke the "center form on screen" behavior through VBA?

Galaxiom, do you spot anything which you would suggest changing? Thank you!

spikepl · Sep 15, 2014

Here is some discussion and a link to a detached ADO recordset where one can add records!

http://www.access-programmers.co.uk/forums/showthread.php?t=269186

Fabricated ADO recordset - adding records (1 Viewer)

mdlueck

Sr. Application Developer

Galaxiom

Super Moderator

mdlueck

Sr. Application Developer

Attachments

Galaxiom

Super Moderator

mdlueck

Sr. Application Developer

mdlueck

Sr. Application Developer

spikepl

Eledittingent Beliped

Similar threads

Users who are viewing this thread