Me.Recordset -> DoCmd.RunSQL "UPDATE ...............

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to uptdate the complete recordset stored in my form by running
an sql command. There is no open connection between the form and the external
data. So I need to read the recordset.

but can't get it running ;-(

any chance ?
 
Not sure if I'm correctly understanding, but if you want to update the
form's data, just cycle through the form's recordsetclone:

With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
.Fields("FieldName").Value = "MyNewValue"
.Update
.MoveNext
Loop
End With
 
I actually want to write back the record using sql because destination is an
unopened external .mdb. but at once - if at all possible.

This would work but is a bit unefficient:

With Me.Recordset
.MoveFirst
Do While .EOF = False
DoCmd.RunSQL "UPDATE ...............
.MoveNext
Loop
End With
 
What is the form's recordset if it's not tied to that external data? How are
you creating it?


--

Ken Snell
<MS ACCESS MVP>
 
I am trying to open an ado connection to update the table of an external,
passwd protected mdb.

The forms recordSource is created with an sql string on the external mdb.
 
I have not done what you seek to do (I rarely work with ADO recordsets
within forms), but I did a little testing with data in one file, and perhaps
you can adapt what I've tried to your situation.

What I did was open a form and use its Load event to set the Recordset of
the form based on DAO recordset SQL statement:

Private Sub Form_Load()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable", dbOpenDynaset)
Set Me.Recordset = rst
Set rst = Nothing
End Sub


This fills the form's recordset with the data from MyTable, and sets the
form's Recordsource to the SQL statement itself ("SELECT * FROM MyTable;").
I believe it's possible to use an ADO recordset to fill a form's Recordset
in a similar manner.


I then used a command button on the form to generate an SQL statement for a
"make-table" query (you can modify my approach for an update query), and
then ran that statement to create a new table based on the form's
RecordSource:

Private Sub Command4_Click()
Dim strs As String
strs = Me.RecordSource
' This next code step manipulates the form's RecordSource to
' make the new query that will put the data into a new table
' (runs a make-table query)
strs = Left(strs, InStr(strs, " FROM ")) & " INTO ThisIsMyTable" & _
Mid(strs, InStr(strs, " FROM "))
CurrentDb.Execute strs, dbFailOnError
End Sub
 
I prefer to use ADO because I think it is going to be faster to port to
VB.NET later on. Also I think MS may discontinue DAO at some point.
 
DAO is the preferred library for interacting with Jet database engine. MS
has reversed its previous actions in ACCESS 2000 and 2003, where there is no
default reference for DAO, by adding back that default reference in 2003.
And it doesn't appear that Jet is being discontinued as a database engine.

ADO has some features that DAO does not, and vice-versa. Because DAO is the
primary method by which data are manipulated in ACCESS databases since
before the days of ACCESS 97, and because those "old" databases are still in
operation (and databases are still being developed in ACCESS 97), and
because these represent millions of databases and users, and because MS is
not in business to drive away customers, I think it's not likely that you'll
see DAO discontinued any time soon.

--

Ken Snell
<MS ACCESS MVP>
 
n...but when you do

Set Me.Recordset Recordset = rst

than the from is set to read-only.

Is there a way to avoid this ?
 
Your code step is wrong. It should be this:
Set Me.Recordset = rst

The form will be "read only" only if the rst recordset itself is
"read-only".
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top