UPDATE statement help / MS Access db

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jamesfreddyc - 09 Apr 2008 17:56 GMT

VB6 app

Trying to setup an UPDATE statement to modify all records in a single column
(Access db). Everything works fine until it hits the UPDATE statement (no
errors, but no updates made to the table).

Connection is fine, table is fine, field is fine, just not updating.

Dim sql_updPid As String
sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID = Left([f_TBLPID_PID],4)
& "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7);"

Reply to this Message

jamesfreddyc - 09 Apr 2008 18:41 GMT

Here is the original SQL Statement that correctly executes in Access:

UPDATE TBLPID SET TBLPID.f_TBLPID_PID = Left([f_TBLPID_PID],4) & "-" &
Mid([f_TBLPID_PID],5,2) & "-" & Mid([f_TBLPID_PID],7);

> VB6 app
>
[quoted text clipped - 7 lines]
> sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID = Left([f_TBLPID_PID],4)
> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7);"

Reply to this Message

SMussler - 10 Apr 2008 00:34 GMT

Not sure if it's the problem, but you have spaces around the dashes in one
update statement and not in the other...

> Here is the original SQL Statement that correctly executes in Access:
>
[quoted text clipped - 15 lines]
>> Left([f_TBLPID_PID],4)
>> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7);"

Reply to this Message

Dmitriy Antonov - 09 Apr 2008 19:34 GMT

> VB6 app
>
[quoted text clipped - 9 lines]
> Left([f_TBLPID_PID],4)
> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7);"

Please submit the relevant portion of the code, which executes the stament.

Dmitriy.

Reply to this Message

jamesfreddyc - 10 Apr 2008 14:10 GMT

> Please submit the relevant portion of the code, which executes the stament.
>
> Dmitriy.

Dmitriy,

Public Function updateTBLPID_PID()

Dim sql_updPid As String
sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID =
Left([f_TBLPID_PID],4) & ""-"" & Mid([f_TBLPID_PID],5,2) & ""-"" &
Mid([f_TBLPID_PID],7)"

'Setup Connection to MS Access Table
Dim connExceptions As ADODB.Connection
Set connExceptions = New ADODB.Connection
connExceptions = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\StormsVB" & _
"\Exceptions.mdb;"
connExceptions.Open

Dim rsDOR_Exceptions As ADODB.Recordset
Set rsDOR_Exceptions = New ADODB.Recordset
rsDOR_Exceptions.Open sql_updPid, pConn, adOpenStatic, adLockBatchOptimistic

rsDOR_Exceptions.Close
connExceptions.Close

MsgBox "Updated PID Field in TBLPID"

End Function

Reply to this Message

Dmitriy Antonov - 10 Apr 2008 15:24 GMT

> Public Function updateTBLPID_PID()
>
[quoted text clipped - 22 lines]
>
> End Function

There is no need to use recordset object in such cases. I don't even know if
it should work at all. Recordset is used if some dataset is requested, which
is not the case here.
You may use Command object. Or simply (air code - I hope I remember the
correct constant, which is, generally, optional anyway):

connExceptions.Execute sql_updPid,,adCmdText

Removing spaces around dashes, IMO, shouldn't change anything unless there
is an error due to violation of string lengths constraint on the column and
you just call this function inside improperly used error handler, which just
silently ignores an error.

Dmitriy.

Reply to this Message

jamesfreddyc - 10 Apr 2008 14:26 GMT

This did it.

sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID = " & _
"Left([f_TBLPID_PID],4) & ""-"" & " & _
"Mid([f_TBLPID_PID],5,2) & ""-"" & Mid([f_TBLPID_PID],7,4);"

> VB6 app
>
[quoted text clipped - 7 lines]
> sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID = Left([f_TBLPID_PID],4)
> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7);"

Reply to this Message

Saimvp - 11 Apr 2008 09:45 GMT

######your code######

> Dim sql_updPid As String
> sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID = Left([f_TBLPID_PID],4)
> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7);"

######try this######

> Dim sql_updPid As String
> sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID = Left([f_TBLPID_PID],4)
> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7) where "yourfield" ='" & "yourIDtext" & "''";

Signature

To be Happy is To be Yourself

> VB6 app
>
[quoted text clipped - 7 lines]
> sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID = Left([f_TBLPID_PID],4)
> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7);"

Reply to this Message

Douglas J. Steele - 19 Apr 2008 15:27 GMT

The quotes don't look correct in the suggested statement. Try:

> Dim sql_updPid As String
> sql_updPid = "UPDATE TBLPID SET TBLPID.f_TBLPID_PID =
> Left([f_TBLPID_PID],4)
> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7) & "
> where yourfield" ='" & "yourIDtext" & "''";

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> ######your code######
>> Dim sql_updPid As String
[quoted text clipped - 24 lines]
>> Left([f_TBLPID_PID],4)
>> & "" - "" & Mid([f_TBLPID_PID],5,2) & "" - "" & Mid([f_TBLPID_PID],7);"

Reply to this Message

VB Forum / Database Access / April 2008

UPDATE statement help / MS Access db