topic Re: in VBA Forum

Update to Access via ADO

Anonymous — Thu, 06 Mar 2003 09:06:38 GMT

I have a VBA application which records attributes of specialized blocks to
an Access database. I am connecting via ADO (v2.5). I am stumped trying to
figure out how to update records in Access. I have been unable to find any
useful information except that I may NOT be able to UPDATE with ADO and
should switch back to DAO instead. If anyone has experience with UPDATEs on
Access recordsets with ADO, I appreciate hearing from you.

Thank you
Tom

Re: Update to Access via ADO

Anonymous — Thu, 06 Mar 2003 09:08:21 GMT

Dim rsJob As ADODB.Recordset
rsJob.Update

have you done this, and it doesn't work?

--
Kevin

"Java Yaboh" wrote in message
news:566C7E9E26967E38E63B8B3FAF87C73C@in.WebX.maYIadrTaRb...
> I have a VBA application which records attributes of specialized blocks to
> an Access database. I am connecting via ADO (v2.5). I am stumped trying to
> figure out how to update records in Access. I have been unable to find any
> useful information except that I may NOT be able to UPDATE with ADO and
> should switch back to DAO instead. If anyone has experience with UPDATEs
on
> Access recordsets with ADO, I appreciate hearing from you.
>
> Thank you
> Tom
>
>

Re:

Anonymous — Thu, 06 Mar 2003 09:28:22 GMT

Kevin,

Sorry, I haven't used Outlook for newsggroups before...

I have added the relevant section of code below. When I run it with .Update

after opening the connection, I get a "Syntax error in FROM clause."

message.

.

.

.

Set oConn = New ADODB.Connection

Set oRS = New ADODB.Recordset

oConn.Open CONNECT_STRING

strSQL = "SELECT * FROM Hydrant WHERE Hydrant.HydrantId = '" & intHydId &

"'" '', HydrantServiceLine

With oRS

.CursorLocation = adUseClient

.CursorType = adOpenDynamic

.LockType = adLockOptimistic

.Open strSQL, oConn, , , adCmdTable

.Update

''.EditMode

!HydrantX = HydrantX

!HydrantY = HydrantY

!PipeLength = sngConnPipeLength

''.Update

.Close

End With

.

.

.

The resulting query is: SELECT * FROM Hydrant WHERE Hydrant.HydrantId =

'9999'

When I run it in the query designer it works fine and returns the requested

recordset. Just for background, my coding background is Delphi and Java -

I'm new to VBA.

Thank you for your help,

Tom

"Kevin Terry" wrote in message
news:37B4C5CD998B6ABBA140A205971EEA8B@in.WebX.maYIadrTaRb...
> Dim rsJob As ADODB.Recordset
> rsJob.Update
>
> have you done this, and it doesn't work?
>
> --
> Kevin
>
>
> "Java Yaboh" wrote in message
> news:566C7E9E26967E38E63B8B3FAF87C73C@in.WebX.maYIadrTaRb...
> > I have a VBA application which records attributes of specialized blocks
to
> > an Access database. I am connecting via ADO (v2.5). I am stumped trying
to
> > figure out how to update records in Access. I have been unable to find
any
> > useful information except that I may NOT be able to UPDATE with ADO and
> > should switch back to DAO instead. If anyone has experience with UPDATEs
> on
> > Access recordsets with ADO, I appreciate hearing from you.
> >
> > Thank you
> > Tom
> >
> >
>
>

Re:

Anonymous — Fri, 07 Mar 2003 04:44:29 GMT

I don't see anything wrong with that FROM clause. However, the "Hydrant." is
unnecessary and certainly could be removed. Try it without that.

Is "intHydId" really a string? Its name suggests integer, but then the
concatenation would fail.

How about just updating the table with SQL? The DBMS driver is heavily
optimized for doing such manipulations. I forget the VBA convert-to-string
functions, but something like:

strSQL = "Update Hydrant set HydrantX = " & _
MakeIntoString(HydrantX) & _
", HydrantY = " & _
MakeIntoString(HydrantY) & _
", PipeLength = " & _
MakeIntoString(sngConnPipeLength) & _
" where HydrantId = '" & intHydId & "'"

If you do this, it's most convenient to use the Execute method of the
Connection object, because then you get the number of records affected.

--
jrf
Member of the Autodesk Discussion Forum Moderator Program
Please do not email questions unless you wish to hire my services

In article <26300F275FFC704C5B2C613197605E8E@in.WebX.maYIadrTaRb>, Java Yaboh
wrote:
> Kevin,
>
> Sorry, I haven't used Outlook for newsggroups before...
>
> I have added the relevant section of code below. When I run it with .Update
>
> after opening the connection, I get a "Syntax error in FROM clause."
>
> message.
>
> ..
>
> ..
>
> ..
>
> Set oConn = New ADODB.Connection
>
> Set oRS = New ADODB.Recordset
>
> oConn.Open CONNECT_STRING
>
>
> strSQL = "SELECT * FROM Hydrant WHERE Hydrant.HydrantId = '" & intHydId &
>
> "'" '', HydrantServiceLine
>
>
> With oRS
>
> ..CursorLocation = adUseClient
>
> ..CursorType = adOpenDynamic
>
> ..LockType = adLockOptimistic
>
> ..Open strSQL, oConn, , , adCmdTable
>
> ..Update
>
> ''.EditMode
>
> !HydrantX = HydrantX
>
> !HydrantY = HydrantY
>
> !PipeLength = sngConnPipeLength
>
> ''.Update
>
> ..Close
>
> End With
>
> ..
>
> ..
>
> ..
>
> The resulting query is: SELECT * FROM Hydrant WHERE Hydrant.HydrantId =
>
> '9999'
>
> When I run it in the query designer it works fine and returns the requested
>
> recordset. Just for background, my coding background is Delphi and Java -
>
> I'm new to VBA.
>
> Thank you for your help,
>
> Tom
>
>
>
> "Kevin Terry" wrote in message
> news:37B4C5CD998B6ABBA140A205971EEA8B@in.WebX.maYIadrTaRb...
> > Dim rsJob As ADODB.Recordset
> > rsJob.Update
> >
> > have you done this, and it doesn't work?
> >
> > --
> > Kevin
> >
> >
> > "Java Yaboh" wrote in message
> > news:566C7E9E26967E38E63B8B3FAF87C73C@in.WebX.maYIadrTaRb...
> > > I have a VBA application which records attributes of specialized blocks
> to
> > > an Access database. I am connecting via ADO (v2.5). I am stumped trying
> to
> > > figure out how to update records in Access. I have been unable to find
> any
> > > useful information except that I may NOT be able to UPDATE with ADO and
> > > should switch back to DAO instead. If anyone has experience with UPDATEs
> > on
> > > Access recordsets with ADO, I appreciate hearing from you.
> > >
> > > Thank you
> > > Tom
> > >
> > >
> >
> >
>

Re:

Anonymous — Fri, 07 Mar 2003 09:13:37 GMT

I don't have the ADO 2.5 doc's available, so I'll use the 2.7. ADO
certainly does update tables, but can be a little quirky. A few things I
notice:

In the recordset Open method Options argument you have used a
CommandTypeEnum value of adCmdTable. That value is intended for use when
opening an entire table by name. To open the recordset using the SQL
string, change that argument to adCmdText, or just leave it out.

You cannot use an adOpenDynamic CursorType with a adUseClient
CursorLocation. From the doc's:
a.. Static cursor - provides a static copy of a set of records for you to
use to find data or generate reports; always allows bookmarks and therefore
allows all types of movement through the Recordset. Additions, changes, or
deletions by other users will not be visible. This is the only type of
cursor allowed when you open a client-side Recordset object.

Set a Watch on the recordset object, and check it after calling the Open
method to see what properties are actually being set.

If your strSQL indeed evaluates to
SELECT * FROM Hydrant WHERE Hydrant.HydrantId = '9999'
then the HydrantId column data type should be Text. If the data type is
numeric, get rid of the single quotes.

You do not need the first Update method call. EditMode is a property
intended to test whether the current record has been changed or a new record
has been added. You don't need it in the code below.

Check the actual value of the LockType property (in the Watch window) after
the recordset is open. If it is adLockBatchOptimistic then write the
recordset with the UpdateBatch method instead of the Update method.

Final note, when you attempt multi-table joined recordsets you must use a
formal Join clause in the Select statement to get an updateable recordset.
Check out the recordset Supports method when you want to find out if a
recordset operation is possible for a given provider.
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

"Java Yaboh" wrote in message
news:26300F275FFC704C5B2C613197605E8E@in.WebX.maYIadrTaRb...
> Kevin,
> Sorry, I haven't used Outlook for newsggroups before...
> I have added the relevant section of code below. When I run it with
.Update
> after opening the connection, I get a "Syntax error in FROM clause."
> message.

> Set oConn = New ADODB.Connection
> Set oRS = New ADODB.Recordset
> oConn.Open CONNECT_STRING
>
> strSQL = "SELECT * FROM Hydrant WHERE Hydrant.HydrantId = '" & intHydId &
> "'" '', HydrantServiceLine
>
> With oRS
> .CursorLocation = adUseClient
> .CursorType = adOpenDynamic
> .LockType = adLockOptimistic
> .Open strSQL, oConn, , , adCmdTable
> .Update
> ''.EditMode
> !HydrantX = HydrantX
> !HydrantY = HydrantY
> !PipeLength = sngConnPipeLength
> ''.Update
> .Close
> End With
>
> The resulting query is: SELECT * FROM Hydrant WHERE Hydrant.HydrantId =
> '9999'
> When I run it in the query designer it works fine and returns the
requested
> recordset. Just for background, my coding background is Delphi and Java -
> I'm new to VBA.
> Thank you for your help,
> Tom
>
> "Kevin Terry" wrote in message
> news:37B4C5CD998B6ABBA140A205971EEA8B@in.WebX.maYIadrTaRb...
> > Dim rsJob As ADODB.Recordset
> > rsJob.Update
> >
> > have you done this, and it doesn't work?
> >
> > --
> > Kevin
> >
> >
> > "Java Yaboh" wrote in message
> > news:566C7E9E26967E38E63B8B3FAF87C73C@in.WebX.maYIadrTaRb...
> > > I have a VBA application which records attributes of specialized
blocks
> to
> > > an Access database. I am connecting via ADO (v2.5). I am stumped
trying
> to
> > > figure out how to update records in Access. I have been unable to find
> any
> > > useful information except that I may NOT be able to UPDATE with ADO
and
> > > should switch back to DAO instead. If anyone has experience with
UPDATEs
> > on
> > > Access recordsets with ADO, I appreciate hearing from you.
> > >
> > > Thank you
> > > Tom
> > >
> > >
> >
> >
>
>