7 Replies - 2218 Views - Last Post: 29 August 2011 - 10:03 AM Rate Topic: -----

#1 justmine10   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-August 11

Error in updating column in database (MS access) in vb6

Posted 24 August 2011 - 05:21 AM

i have 2 tables named TRAININGS and TRAININGSTEMP. i would like to copy the column PERSONNELID in TRAININGSTEMP table and replace the PERSONNELID in TRAININGS table where LastName,FirstName, and MiddleName match between the 2 tables.

i've tried 2 options to do this. Below the codes. Pls help me to figure out what i am missing to achieve correct output. Thanks a lot...

1.) OPTION 1 = via recordset. i have declared the recordset for 2 tables and do the loop. But the output is ONLY the first record of field PERSONNELID of trainings table were replace instead the whole column if found match in Names.

[/code]
Set rsT1 = New ADODB.Recordset
rsT1.Open "Select * from TrainingsTemp", cn, 3, 2

Set rsT2 = New ADODB.Recordset
rsT2.Open "Select * from Trainings ", cn, 3, 2
Dim C As Integer
Dim n As Integer

If Not rsT1.EOF Then
For C = 1 To rsT2.RecordCount
If (rsT1!LName = rsT2!LName) And (rsT1!FName = rsT2!FName) And (rsT1!MName = rsT2!MName) Then
rsT2!PersonnelID = rsT1!PersonnelID
rsT2.Update

End If
rsT2.MoveNext
Next
End If


rsT1.Close
rsT2.Close
[/code]


2.) OPTION 2 = i used UPDATE statement as below. in which the column PERSONNELID in TRAININGS TABLE will be updated once found LastName,FirstName,MiddleName matches in TRAININGSTEMP. by the way, all the fields in both tables are the same. But i got the error as below.

ERROR
Run-time error '-2147467259 (80004005)':
OPERATION MUST USE AN UPDATEABLE QUERY.


[b]
[/b]
Dim sql3 As String
         sql3 = "Update Trainings "
         sql3 = sql3 & " set PersonnelID = (select TrainingsTemp.PersonnelID from TrainingsTemp where TrainingsTemp.Lname = Trainings.LName and TrainingsTemp.Fname = Trainings.FName and TrainingsTemp.Mname = Trainings.MName)  "
         sql3 = sql3 & " where exists "
         sql3 = sql3 & " (select TrainingsTemp.PersonnelID from TrainingsTemp where TrainingsTemp.Lname = Trainings.LName and TrainingsTemp.Fname = Trainings.FName and TrainingsTemp.Mname = Trainings.MName)  "
       cn.Execute sql3



Is This A Good Question/Topic? 0
  • +

Replies To: Error in updating column in database (MS access) in vb6

#2 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Re: Error in updating column in database (MS access) in vb6

Posted 24 August 2011 - 09:10 AM

Option 1

You aren't moving the cursor in table 1 so you will be checking everything against the same record. Try these...on the second method here with the find, there a few variations of find or findfirst so you'll have to see what references you have set up...


THERE ARE TWO WAYS OF DOING THIS...BARE WITH, I TYPED IT HERE
If Not rst1.EOF Then rst1.movefirst
'This would work if the records had the exact same id in both tables
'The way your loop works, you're comparing every record in rsT2 to the same record, line 1, in rsT1...try this
For C = 1 To rst2.RecordCount
    If (rst1!LName = rst2!LName) And (rst1!FName = rst2!FName) And (rst1!MName = rst2!MName) Then
        rst2!PersonnelID = rst1!PersonnelID
        rst2.Update
    End If
rst2.movenext
rst1.movenext 'VERY IMPORTANT
Next


'''ORRRR THIS WILL WORK IF THEY DON'T SHARE THE SAME ID IN THE TABLES

For C = 1 To rst2.RecordCount
    rst1.movefirst
    rst1.findfirst "LName='" & rst2!LName & "', FName='" & rst2!FName & "', MName='" & rst2!MName & "'"
    If rst1.nomatch = False Then
        rst2!personalid = rst1!personalid
    End If
rst2.movenext

Next




Ok, there are ways to use both of the options for what you want to do but I don't have a lot of time so now so that's option one or here are some other ideas to try to fix 2...if I have time later i'll come back and take a second look at 2

OPTION 2
For now, what I would do and what I find to be the most efficient use of connecting to access as your database, is write the query in access. It provides a nice user friendly interface and is efficient.

Once you have done that you can do one of two things...

You can either call the query...you would have to use

'Typed here
dim AccApp as new Access.Application

'Create the AccApp connection then

AccApp.OpenQuery "MyQuery"

'AccApp.Close



And that would accomplish what you want to do...


OR (and this is my preference when I'm stuck)

Write the query in access, make sure it works how you want then view the SQL output and try using that in your code. You'll have to make a few small changes but it's a good way to get some SQL help on what you're directly working on. I've done it and found out I was missing the stupidest thing like a comma or there was a shorter way to actually write what I was working on.

Hope this all helps :)

This post has been edited by guyfromri: 24 August 2011 - 09:16 AM

Was This Post Helpful? 0
  • +
  • -

#3 justmine10   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-August 11

Re: Error in updating column in database (MS access) in vb6

Posted 25 August 2011 - 08:18 AM

Thanks Guyfromri for your reply.

I've tried Option 1 (First way) since i realized that you're right, im lacking the loop (rsT1.movenext)
= i have add rsT1.movenext 'same as you quoted below but there's error
[ERROR]
Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a currect record
[\ERROR]

= ive tried to re-arrange the movenext thing to as below. There's no error but the output is still the same. Only the first record were updated.

Set rsT1 = New ADODB.Recordset
rsT1.Open "Select * from TrainingsTemp", cn, 3, 2

Set rsT2 = New ADODB.Recordset
rsT2.Open "Select * from Trainings ", cn, 3, 2

If Not rsT1.EOF Then
rsT1.MoveFirst
For C = 1 To rsT2.RecordCount

If (rsT1!LName = rsT2!LName) And (rsT1!FName = rsT2!FName) And (rsT1!Mname = rsT2!Mname) Then
rsT2!PersonnelID = rsT1!PersonnelID
rsT2.Update
End If
rsT2.MoveNext
Next
rsT1.MoveNext
End If

'For example on below table RST2, if loop found matches the name from rST1 to rsT2, personnelID of rsT2 will be updated. This should be the output. But,

In the case of above loop, only the first record PersonnelID#185 were updated. The remaining ID# dont changed which is wrong.

PERSONNEL ID LNAME FNAME MNAME
185 Miller Joan Uy
185 Miller Joan Uy
186 Dizon Roy Sy
187 Nastor Lester Jones
188 Carlos Miranda Lozano
188 Carlos Miranda Lozano
188 Carlos Miranda Lozano



'ive also tried below but also the first record only updated. really cant figure out whats im missing on both ways...Hope you can spare me of your time...thanks

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rsTA As DAO.Recordset
Dim rsTB As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase _
("c:\HRTS\HRTISDATABASE\HRTS.mdb")
Set rsTA = db.OpenRecordset("TrainingsTemp", dbOpenDynaset)
Set rsTB = db.OpenRecordset("Trainings", dbOpenDynaset)

If Not rsTA.EOF Then
For C = 1 To rsTB.RecordCount
rsTA.MoveFirst
rsTA.FindFirst "'" & rsTA!LName & "' = '" & rsTB!LName & "' and '" & rsTA!FName & "' = '" & rsTB!FName & "' and '" & rsTA!MName & "' = '" & rsTB!MName & "' "

If rsTA.NoMatch = False Then
rsTB.Edit
rsTB!personnelID = rsTA!personnelID
rsTB.Update
MsgBox "updated"
End If
rsTB.MoveNext
Next
End If
rsTA.Close
db.Close






View Postguyfromri, on 24 August 2011 - 09:10 AM, said:

Option 1

You aren't moving the cursor in table 1 so you will be checking everything against the same record. Try these...on the second method here with the find, there a few variations of find or findfirst so you'll have to see what references you have set up...


THERE ARE TWO WAYS OF DOING THIS...BARE WITH, I TYPED IT HERE
If Not rst1.EOF Then rst1.movefirst
'This would work if the records had the exact same id in both tables
'The way your loop works, you're comparing every record in rsT2 to the same record, line 1, in rsT1...try this
For C = 1 To rst2.RecordCount
    If (rst1!LName = rst2!LName) And (rst1!FName = rst2!FName) And (rst1!MName = rst2!MName) Then
        rst2!PersonnelID = rst1!PersonnelID
        rst2.Update
    End If
rst2.movenext
[color="#008080"]rst1.movenext 'VERY IMPORTANT[/color]Next


'''ORRRR THIS WILL WORK IF THEY DON'T SHARE THE SAME ID IN THE TABLES

For C = 1 To rst2.RecordCount
    rst1.movefirst
    rst1.findfirst "LName='" & rst2!LName & "', FName='" & rst2!FName & "', MName='" & rst2!MName & "'"
    If rst1.nomatch = False Then
        rst2!personalid = rst1!personalid
    End If
rst2.movenext

Next




Ok, there are ways to use both of the options for what you want to do but I don't have a lot of time so now so that's option one or here are some other ideas to try to fix 2...if I have time later i'll come back and take a second look at 2

OPTION 2
For now, what I would do and what I find to be the most efficient use of connecting to access as your database, is write the query in access. It provides a nice user friendly interface and is efficient.

Once you have done that you can do one of two things...

You can either call the query...you would have to use

'Typed here
dim AccApp as new Access.Application

'Create the AccApp connection then

AccApp.OpenQuery "MyQuery"

'AccApp.Close



And that would accomplish what you want to do...


OR (and this is my preference when I'm stuck)

Write the query in access, make sure it works how you want then view the SQL output and try using that in your code. You'll have to make a few small changes but it's a good way to get some SQL help on what you're directly working on. I've done it and found out I was missing the stupidest thing like a comma or there was a shorter way to actually write what I was working on.

Hope this all helps :)

Was This Post Helpful? 0
  • +
  • -

#4 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Re: Error in updating column in database (MS access) in vb6

Posted 25 August 2011 - 04:40 PM

I'm not trying to be a d#ck but could you post between code tags, so much easier to read that way....then I'll see if I can't be of more help. Thanks
Was This Post Helpful? 0
  • +
  • -

#5 justmine10   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-August 11

Re: Error in updating column in database (MS access) in vb6

Posted 27 August 2011 - 03:22 AM

im sorry i forgot to enclosed in code tags....pls see below...


Thanks Guyfromri for your reply.

I've tried Option 1 (First way) since i realized that you're right, im lacking the loop (rsT1.movenext)
= i have add rsT1.movenext 'same as you quoted below but there's error
[ERROR]
Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a currect record
[\ERROR]

= ive tried to re-arrange the movenext thing to as below. There's no error but the output is still the same. Only the first record were updated.

Set rsT1 = New ADODB.Recordset
rsT1.Open "Select * from TrainingsTemp", cn, 3, 2

Set rsT2 = New ADODB.Recordset
rsT2.Open "Select * from Trainings ", cn, 3, 2

If Not rsT1.EOF Then
rsT1.MoveFirst
For C = 1 To rsT2.RecordCount

If (rsT1!LName = rsT2!LName) And (rsT1!FName = rsT2!FName) And (rsT1!Mname = rsT2!Mname) Then
rsT2!PersonnelID = rsT1!PersonnelID
rsT2.Update
End If
rsT2.MoveNext 
Next
rsT1.MoveNext 
End If




'For example on below table RST2, if loop found matches the name from rST1 to rsT2, personnelID of rsT2 will be updated. This should be the output. But,

In the case of above loop, only the first record PersonnelID#185 were updated on below example. The remaining ID# dont changed which is wrong.
PERSONNEL ID LNAME FNAME MNAME
185 Miller Joan Uy
185 Miller Joan Uy
186 Dizon Roy Sy
187 Nastor Lester Jones
188 Carlos Miranda Lozano
188 Carlos Miranda Lozano
188 Carlos Miranda Lozano



'ive also tried below but also the first record only updated. really cant figure out whats im missing on both ways...Hope you can spare me of your time...thanks


Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rsTA As DAO.Recordset
Dim rsTB As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase _
("c:\HRTS\HRTISDATABASE\HRTS.mdb")
Set rsTA = db.OpenRecordset("TrainingsTemp", dbOpenDynaset)
Set rsTB = db.OpenRecordset("Trainings", dbOpenDynaset)

If Not rsTA.EOF Then
For C = 1 To rsTB.RecordCount
rsTA.MoveFirst
rsTA.FindFirst "'" & rsTA!LName & "' = '" & rsTB!LName & "' and '" & rsTA!FName & "' = '" & rsTB!FName & "' and '" & rsTA!MName & "' = '" & rsTB!MName & "' "

If rsTA.NoMatch = False Then
rsTB.Edit
rsTB!personnelID = rsTA!personnelID
rsTB.Update
MsgBox "updated"
End If
rsTB.MoveNext
Next
End If
rsTA.Close
db.Close





View Postguyfromri, on 25 August 2011 - 04:40 PM, said:

I'm not trying to be a d#ck but could you post between code tags, so much easier to read that way....then I'll see if I can't be of more help. Thanks

Was This Post Helpful? 0
  • +
  • -

#6 BobRodes   User is offline

  • Product Manager
  • member icon

Reputation: 601
  • View blog
  • Posts: 3,072
  • Joined: 19-May 09

Re: Error in updating column in database (MS access) in vb6

Posted 28 August 2011 - 01:16 PM

I really think that your Option 2 is the simpler one. However, cn.execute looks a lot like an ADO connection object. The reason you're having a problem with this is because the execute method here is expecting a select query, and you're trying to send it an update query. Tell me a bit more about your "cn" object, and I'll see if I can come up with a recommendation.
Was This Post Helpful? 0
  • +
  • -

#7 justmine10   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-August 11

Re: Error in updating column in database (MS access) in vb6

Posted 29 August 2011 - 04:36 AM

hi Bob, thanks for your reply. Pls is below codes for CN. its my connection string.

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\HRT\DATABASE\HRT.mdb"
cn.Open



what im trying to do in below codes, is to edit the field PersonnelID in Table TRAININGS when found that LName,FName,MName matches with table TRAININGSTEMP. If it matches, then the PERSONNELID in TRAININGSTEMP, will be copy to table Trainings.

Hope you could help me on this...thanks a lot for your time

Dim sql3 As String 

          sql3 = "Update Trainings " 

          sql3 = sql3 & " set PersonnelID = (select TrainingsTemp.PersonnelID from TrainingsTemp where TrainingsTemp.Lname = Trainings.LName and TrainingsTemp.Fname = Trainings.FName and TrainingsTemp.Mname = Trainings.MName)  " 

          sql3 = sql3 & " where exists " 

          sql3 = sql3 & " (select TrainingsTemp.PersonnelID from TrainingsTemp where TrainingsTemp.Lname = Trainings.LName and TrainingsTemp.Fname = Trainings.FName and TrainingsTemp.Mname = Trainings.MName)  " 

        cn.Execute sql3 




View PostBobRodes, on 28 August 2011 - 01:16 PM, said:

I really think that your Option 2 is the simpler one. However, cn.execute looks a lot like an ADO connection object. The reason you're having a problem with this is because the execute method here is expecting a select query, and you're trying to send it an update query. Tell me a bit more about your "cn" object, and I'll see if I can come up with a recommendation.

Was This Post Helpful? 0
  • +
  • -

#8 BobRodes   User is offline

  • Product Manager
  • member icon

Reputation: 601
  • View blog
  • Posts: 3,072
  • Joined: 19-May 09

Re: Error in updating column in database (MS access) in vb6

Posted 29 August 2011 - 10:03 AM

That looks ok, except I'd recommend that you use the adExecuteNoRecords argument (look it up to find out why). Now, I googled around a bit and this looks promising: http://support.microsoft.com/kb/175168 . It gives four reasons why you can run into this problem with Access. My gut feeling is that the second one is where your problem is coming from. Suppose you read this and try setting the Mode property of the connection object. Let us know if you solve it!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1