Assign ADO-recordset to form's RecordSource 1

[emuti]

Is there a way to assign an ADO-based recordset to an form recordsource (an Access 2000 form - not an ADP)?

 

[jimmythegeek]

Is it a single form, or continuous form?
Is it bound, or unbound? Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[BigL]

I would like to do this also. My form is unbound.

 

[Quehay]

Why do you want to reference the form rather than the query or table that the form uses?

This is easy:

Within current Access db:

Dim Cnn as adodb.connection
Dim Rs as new adodb.recordset


Set Cnn = CurrentProject.Connection

Rs.Open "yourtblorqry",Cnn

(other arguments available use F2 to find out what you need)

 

[BigL]

Because I am trying to use Access 2000 as a front-end to SQL Server 2000 database engine. The only way I have to reference a table is to link using ODBC. I was looking for a way to load and edit a recordset just using the code in the form. I can get it to work using the following code:

Dim rst As ADODB.Recordset
Dim cnn As String
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cnn
.CursorType = adUseClient
.LockType = adLockBatchOptimistic
.Source = "SELECT * FROM tblEmployees ORDER by [Name]"
.Open
End With
Set Me.Recordset = rst

I have a textbox with the control source set to "name". Everything looks good until I try to edit the information which the form will not allow. Should I just give it up and keep doing it the way I have been?

 

[emuti]

Hi,
Looks like the recordset will only be read-only. See article:

ACC2000: Forms Based on ADO Recordsets Are Read-Only

http://support.microsoft.com/suppor... not set&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC

I ended up upsizing my Access front-end to an Access Project file (.adp), and then creating a view as the Form's recordsource.

 

[jimmythegeek]

The best way to handle this, is to make your form unbound. On load of the form, you populate your fields with the data from the ADO Recordset.

rs.Open (using a connection that should already be open)
With rs
Me.Field1 = !Field1
Me.Field2 = !Field2
etc.
End With
rs.Close

The user can now edit the fields at will, without the data being affected at all.

Add a "Save" button to your form. After the user selects the save button, you either use the Recordset.Edit method:

rs.Open (using a connection that should already be open)
With rs
.Edit
!Field1 = Me.Field1
!Field2 = Me.Field2
etc.
.Update
End With
rs.Close

Or use a the Connection.Execute method with an UPDATE statement:

strSQL = "UPDATE tblYourTable SET Field1 = '" & Me.Field1 & "', Field2 = '" & Me.Field2 & "' WHERE ....
Connection.Execute strSQL

This way has certain advantages:
1) The user cannot affect the data by simply editing the form (which is the case in a bound form)
2) Since the form is not bound, you don't have to actually "Cancel" or "Undo" the record if the user selects the "Cancel" button, you simply close the form.

We did this for an entire application with obvious modifications, and the performance was fantastic. I have some samples of forms that use this method if you are interested.


Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[petermeachem]

But what do you do about continuous forms. The whole advantage of using Access as opposed to VB, as far as I am concerened, is that you don't have to type all this stuff in. I would hope the performance would be 'fantastic'. After all you are only looking at a single record. Peter Meachem
peter@accuflight.com

 

[jimmythegeek]

We used continuous forms, with Pass-through queries as the record source (we called them "List" forms). They were not editable, so we put buttons, or double click events on the form that would open an "editing" form. This is where the single record came into play. You open an "edit" form based on the record the user selects from your "List" form. You can do the same thing with a list box (which I actually like better).

Using SQL Server as a back-end has a few drawbacks, but in my opinion, the benefits far outweigh them. Trust me, the performance of the entire application was like night and day from an Access/Access application, and we were dealing with 88 tables, over a million records in the largest one. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Francescina]

There is a very easy way to use any RECORDSET as the RECORDSOURCE of a form or report (extract from the ACCESS ONLINE ENCYCLOPEDIA at

http://www.unsoftwareag.com):

ACCESS ONLINE ENCYCLOPEDIA Article Code: R20

How can I use a recordset as recordsource for a report ? ?
Reports (and forms) recordsources have to be tables or queries. You can create a temporary table or query if you want to use a recordset as RECORDSOURCE of a form or report. Much easier and faster is the usage of the NAME property of the recordset. This returns the name of the table or query or the SQL string that builds the recordset.

If you define the recordset as a global var then you can simply use he following code in the report's OPEN event:


me.recordsource=globalRS.Name

 

[wickerman]

At first I thought that Francescina's post looked
pretty promising, except for one thing: there is
no "Name" property for an ADO recordset...

I'm trying to create an unbound report (Acc97) which
will point to an ADO recordset for it's RecordSource;
I'm doing this because it is going to be easier for
me to de-normalize our SQL data through the ADO (2.5)
recordset object than through a massive stored procedure
which would need to use cursors... *shudder* cursors,
bad... recordsets, good. jimmythegeek's option seems
to me to be the most reliable.

Carter Wickstrom
wickerman26@hotmail.com

 

[kramerica]

(Access 97) How do you use VB to select data in a listbox, on a form and send it to a field?

For example: unbound listbox on form is called grocerylst.

I want to be able to click on a button and have all of the dataitems in the unbound listbox added to a field on the form separated by commas. Is this possible?