|
List Info
Thread: Updating ADO records
|
|
| Updating ADO records |
|
2006-12-12 23:05:57 |
To the Help Group,
I'm still attempting to work with ADO without the data
control. I'm
successrfully opening the connection and recordset, but when
I attempt to
change, add, or update, I receive the following error
message:
"Runtime Error 3251:
Current recordset does not support updating. This
may be a
limitation of the Provider
or the locktype."
I'm pasting the "Form_Load() langauge below:
Private Sub Form_Load()
conn.Open ("Provider = Microsoft.Jet.OLEDB.4.0;Data
Source =" & App.Path
& "General
Ledger.mdb")
'set rsTrxno.Open = "SELECT * FROM Glact Order by
Acno"
,adopendynamic,adlockoptimistic
'Establishes RecordSource
Set rsTrxno = conn.Execute("SELECT * FROM
Trxno", adLockOptimistic)
Set rsGlact = conn.Execute("SELECT * FROM Glact
Order by Acno",
adLockOptimistic)
Set rsGltrx = conn.Execute("SELECT * FROM Gltrx
Order by Ref",
adLockOptimistic)
I've tried numerous other combinations, but without success.
I'm now
working from two ADO books recently purchased and have
received a great deal
of help from those sources. However, neither of those nor
any of the 14
others reference this partifular problem. Can someone give
me an idea?
Don Gaither
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://grou
ps.yahoo.com/group/helpwithvb/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://
groups.yahoo.com/group/helpwithvb/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:helpwithvb-digest@yahoogroups.com
mailto:helpwithvb-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
helpwithvb-unsubscribe@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.c
om/info/terms/
|
|
| Updating ADO records |
|
2006-12-12 23:20:16 |
Hi Don,
Explain a little more about what you need to do.
Like..., " I changed 3 field values, and
now I want to place that new data into the Database."
Or...., the User has updated the fields by using
the form I created, but how do I get the new
data into the Recordset ?
Steve
From: helpwithvb@yahoogroups.com
[mailto:helpwithvb@yahoogroups.com] On Behalf Of Donald
Gaither
Sent: Tuesday, December 12, 2006 6:06 PM
To: helpwithVB@yahoogroups.com
Subject: [helpwithvb] Updating ADO records
To the Help Group,
I'm still attempting to work with ADO without the data
control. I'm
successfully opening the connection and Recordset, but when
I attempt to
change, add, or update, I receive the following error
message:
"Runtime Error 3251:
Current Recordset does not support updating. This may be a
limitation of the Provider
or the locktype."
I'm pasting the "Form_Load() langauge below:
Private Sub Form_Load()
conn.Open ("Provider = Microsoft.Jet.OLEDB.4.0;Data
Source =" & App.Path
& "General
Ledger.mdb")
'set rsTrxno.Open = "SELECT * FROM Glact Order by
Acno"
,adopendynamic,adlockoptimistic
'Establishes RecordSource
Set rsTrxno = conn.Execute("SELECT * FROM Trxno",
adLockOptimistic)
Set rsGlact = conn.Execute("SELECT * FROM Glact Order
by Acno",
adLockOptimistic)
Set rsGltrx = conn.Execute("SELECT * FROM Gltrx Order
by Ref",
adLockOptimistic)
I've tried numerous other combinations, but without success.
I'm now
working from two ADO books recently purchased and have
received a great deal
of help from those sources. However, neither of those nor
any of the 14
others reference this partifular problem. Can someone give
me an idea?
Don Gaither
.
.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://grou
ps.yahoo.com/group/helpwithvb/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://
groups.yahoo.com/group/helpwithvb/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:helpwithvb-digest@yahoogroups.com
mailto:helpwithvb-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
helpwithvb-unsubscribe@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.c
om/info/terms/
|
|
| Updating ADO records |
|
2006-12-13 02:13:44 |
|
Don,
It’s somewhat hard to follow your
code…it looks like your missing major chunks of the process. Here’s
a pretty well commented routine that illustrates the general principles of
using ADO
commands.
Private Sub Command1_Click()
'Define the ;three ;objects that we need,
' A Connection Object - connects to our data source
' A Command Object - defines what data to ;get from the data source
' A RecordSet Object - stores the data we ;get from our data source
Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset
'Defines the connection string for the Connection. Here we ;have used fields
'Provider, Data Source and Mode to assign ;values to the properties
' conConnection.Provider and ;conConnection.Mode
conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "" & "database.mdb;Mode=Read|Write"
'Define the ;location of ;the cursor engine, in this case we are opening an Access database
'and adUseClient is our ;only choice.
conConnection.CursorLocation = adUseClient
'Opens our connection using the password ";Admin"; to access the database. If there was no password
'protection on the database this field could be ;left out.
conConnection.Open
'Defines our command object
' .ActiveConnection tells the command to use our newly created command object.
' .CommandText tells the command how to get the data, in this case ;the command
' ; will evaluate the text as an SQL string ;and we will return all
' ; records from a table called tabTestTable
' .CommandType tells the command to ;evaluate the .CommandText property ;as an SQL string.
With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM tabTestTable;"
.CommandType = adCmdText
End With
'Defines our RecordSet object.
' .CursorType sets a static cursor, the only choice for a client side cursor
' .CursorLocation sets ;a client side cursor, the only choice for an Access database
' .LockType sets an optimistic lock type
' .Open executes the cmdCommand object against ;the data source and stores the
' returned records in our RecordSet object.
With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With
'Firstly test to see if any records have been returned, if some have been returned then
'the .EOF property of the RecordSet ;will be false, if none have been returned then the
'property will be ;true.
If rstRecordSet.EOF = False Then
; 'Move ;to the first record
; rstRecordSet.MoveFirst
; 'Lets ;move through the records one at a time until we reach the last record
'and print out the values of each field
; Do
; 'Access the ;field ;values using the fields ;collection and print them to a message box.
'In this case I do ;not know what you might call the columns in your ;database so ;this
'is the safest way to do ;it. If I did know the names of ;the columns ;in your table
'and they were called "Column1" and "Column2" I could reference their values using:
; ' rstRecordSet!Column1
' rstRecordSet!Column2
MsgBox "Record " & rstRecordSet.AbsolutePosition & " " & _
; rstRecordSet.Fields(0).Name & "=" & rstRecordSet.Fields(0) & " " & _
; rstRecordSet.Fields(1).Name & "=" & rstRecordSet.Fields(1)
; 'Move ;to the next record
; rstRecordSet.MoveNext
Loop Until rstRecordSet.EOF = True
; 'Add a new record
; With rstRecordSet
.AddNew
.Fields(0) = "New"
.Fields(1) = "Record"
.Update
End With
; 'Move ;back to the first record and delete it
; rstRecordSet.MoveFirst
rstRecordSet.Delete
rstRecordSet.Update
'Close the recordset
; rstRecordSet.Close
Else
MsgBox "No records were returned using the query "; & cmdCommand.CommandText
End If
'Close the connection
conConnection.Close
'Release your variable references
Set conConnection = Nothing
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
End Sub
From: helpwithvb@yahoogroups.com [mailto:helpwithvb@yahoogroups.com] On Behalf Of Donald Gaither
Sent: Tuesday, December 12, 2006
6:06 PM
To: helpwithVB@yahoogroups.com
Subject: [helpwithvb] Updating ADO
records
To the Help Group,
I'm still attempting to work with ADO
without the data control. I'm
successrfully opening the connection and recordset, but when I attempt to
change, add, or update, I receive the following error message:
"Runtime Error 3251:
Current recordset does not support updating. This may be a
limitation of the Provider
or the locktype."
I'm pasting the "Form_Load() langauge below:
Private Sub Form_Load()
conn.Open ("Provider = Microsoft.Jet.OLEDB.4.0;Data Source
=" & App.Path
& "General
Ledger.mdb")
'set rsTrxno.Open = "SELECT * FROM Glact Order by Acno"
,adopendynamic,adlockoptimistic
'Establishes RecordSource
Set rsTrxno = conn.Execute("SELECT * FROM Trxno";,
adLockOptimistic)
Set rsGlact = conn.Execute("SELECT * FROM Glact Order by Acno",
adLockOptimistic)
Set rsGltrx = conn.Execute("SELECT * FROM Gltrx Order by Ref",
adLockOptimistic)
I've tried numerous other combinations, but without success. I'm now
working from two ADO
books recently purchased and have received a great deal
of help from those sources. However, neither of those nor any of the 14
others reference this partifular problem. Can someone give me an idea?
Don Gaither
__._,_.___
__,_._,___
|
| Updating ADO records |
|
2006-12-13 00:11:06 |
Hey Don,
Maybe I misunderstood what you are trying to do.
Look at this web page, and the other web pages
that it has links to. All very basic ADO stuff.
http://w
ww.vb-helper.com/AdoTalk/AdoTalk.htm
..and this page shows how to update the Recordset:
http://www
.vb-helper.com/AdoTalk/Ado2b.htm
To be honest, that will probably not make much
sense because the code is taken out of its context.
Might be better to download the entire sample
program / project and look through it to see
how all of the pieces work together.
Then when you start to ask other questions, but
now your code uses the Bang ( ! ) Operator
we can yell at you for that < g >
adoRS!SomeField = 42&
is another way to write...
adoRS.Fields(FieldName).Value = 42&
In my previous e-mail I mention a situation where
by way of your form the User has supplied all of
the needed data ( filled in all fields )
for a new record.
Now we need to put that new record
into the database table:
http:/
/www.vb-helper.com/howto_ado_insert.html
Remember to look at the Recordset as just
another array type, or another large
tractor trailer going down the highway.
The Recordset is just a transportation vehicle
when viewed in its simplest form.
All The Best,
Steve
7:10 PM EST Tuesday, December 12, 2006
.
.
From: helpwithvb@yahoogroups.com
[mailto:helpwithvb@yahoogroups.com] On Behalf Of Donald
Gaither
Sent: Tuesday, December 12, 2006 6:06 PM
To: helpwithVB@yahoogroups.com
Subject: [helpwithvb] Updating ADO records
To the Help Group,
I'm still attempting to work with ADO without the data
control. I'm
successrfully opening the connection and recordset, but when
I attempt to
change, add, or update, I receive the following error
message:
"Runtime Error 3251:
Current recordset does not support updating. This may be a
limitation of the Provider
or the locktype."
I'm pasting the "Form_Load() langauge below:
Private Sub Form_Load()
conn.Open ("Provider = Microsoft.Jet.OLEDB.4.0;Data
Source =" & App.Path
& "General
Ledger.mdb")
'set rsTrxno.Open = "SELECT * FROM Glact Order by
Acno"
,adopendynamic,adlockoptimistic
'Establishes RecordSource
Set rsTrxno = conn.Execute("SELECT * FROM Trxno",
adLockOptimistic)
Set rsGlact = conn.Execute("SELECT * FROM Glact Order
by Acno",
adLockOptimistic)
Set rsGltrx = conn.Execute("SELECT * FROM Gltrx Order
by Ref",
adLockOptimistic)
I've tried numerous other combinations, but without success.
I'm now
working from two ADO books recently purchased and have
received a great deal
of help from those sources. However, neither of those nor
any of the 14
others reference this partifular problem. Can someone give
me an idea?
Don Gaither
.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://grou
ps.yahoo.com/group/helpwithvb/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://
groups.yahoo.com/group/helpwithvb/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:helpwithvb-digest@yahoogroups.com
mailto:helpwithvb-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
helpwithvb-unsubscribe@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.c
om/info/terms/
|
|
[1-4]
|
|