Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Updating Access

  1. #1
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Question Unanswered: Updating Access

    I have the following code and it works wonders when the data doesn't exists. I need it to work also when the record exists so it updates it. Any idea?
    Sub Update()
    Dim cn As ADODB.Connection, ct As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    If IsEmpty(Range("Información!B3")) Then
    Beep
    MsgBox "Falta el número de sección"
    End
    End If
    If IsEmpty(Range("Información!B4")) Then
    Beep
    MsgBox "Falta el número del curso"
    End
    End If
    If IsEmpty(Range("Información!B5")) Then
    Beep
    MsgBox "Falta el nombre del curso"
    End
    End If
    If IsEmpty(Range("Información!B6")) Then
    Beep
    MsgBox "Falta la hora de reunión"
    End
    End If
    If IsEmpty(Range("Información!D3")) Then
    Beep
    MsgBox "Falta el nombre dl instructor"
    End
    End If
    If IsEmpty(Range("Información!D4")) Then
    Beep
    MsgBox "Falta el término"
    End
    End If
    If IsEmpty(Range("Información!D5")) Then
    Beep
    MsgBox "Faltan los días de reunión"
    End
    End If
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=E:\UI_Database\Registro Database.mdb;"
    ' open a recordset
    Set rc = New ADODB.Recordset
    rc.Open "Sección", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 3 ' the start row in the worksheet
    rc.Find "section = " & Range("Información!B3").Value
    If rc.EOF Then 'No record in table with this ID
    rc.AddNew
    End If
    rc.Fields("courseno") = Range("Información!B4").Value
    rc.Fields("section") = Range("Información!B3").Value
    rc.Fields("semester") = Range("Información!D4").Value
    rc.Fields("instructor") = Range("Información!D3").Value
    rc.Fields("time") = Range("Información!B6").Value
    rc.Fields("days") = Range("Información!D5").Value
    rc.Update
    ' stores/updates the new record

    rc.Close
    Set rc = Nothing
    cn.Close
    Set cn = Nothing
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=E:\UI_Database\Registro Database.mdb;"
    ' open a recordset
    Set rc = New ADODB.Recordset
    rc.Open "Sección_Estudiante", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 10 ' the start row in the worksheet
    Do While Len(Range("Resúmen!C" & r)) > 0
    rc.Find "ID = " & Range("Resúmen!F" & r).Value
    If rc.EOF Then 'No record in table with this ID
    rc.AddNew ' create a new record
    End If
    rc.Fields("ID") = Range("Resúmen!F" & r).Value
    rc.Fields("Número Curso") = Range("Resúmen!E3").Value
    rc.Fields("Sección") = Range("Resúmen!E4").Value
    rc.Fields("Nota") = Range("Resúmen!W" & r).Value
    rc.Fields("Ultima Fecha Asistencia") = Range("Resúmen!E" & r).Value
    rc.Fields("Semestre") = Range("Resúmen!E5").Value
    rc.Update
    ' stores the new record
    r = r + 1
    Loop
    rc.Close
    Set rc = Nothing
    cn.Close
    Set cn = Nothing
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=E:\UI_Database\Registro Database.mdb;"
    ' open a recordset
    Set rc = New ADODB.Recordset
    rc.Open "Estudiante", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 10 ' the start row in the worksheet

    Do While Len(Range("Resúmen!C" & r)) > 0
    ' repeat until firct empty cell in column C
    rc.Find "ID = " & Range("Resúmen!F" & r).Value
    If rc.EOF Then 'No record in table with this ID
    rc.AddNew
    End If
    rc.Fields("ID") = Range("Resúmen!F" & r).Value
    rc.Fields("Nombre") = Range("Resúmen!D" & r).Value
    rc.Fields("Apellidos") = Range("Resúmen!C" & r).Value
    ' add more fields if necessary...
    rc.Update
    ' stores the new record
    r = r + 1 ' next row
    Loop

    rc.Close
    Set rc = Nothing
    cn.Close
    Set cn = Nothing
    End Sub
    Last edited by javydreamercsw; 02-18-04 at 12:37.

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    Seems this isn't your code, else you'd know that ADDNEW adds a record, doesn't update.
    you need to rearange whole code by using "Find" and "Update" (after edit) commands. Firs of all you need an algorithm!
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Originally posted by Kakha
    Seems this isn't your code, else you'd know that ADDNEW adds a record, doesn't update.
    you need to rearange whole code by using "Find" and "Update" (after edit) commands. Firs of all you need an algorithm!
    First I never said this was my code I'm learning VBA in access. I'm looking for examples. If I use find/Update it'll add a new record if it doesn't find it?

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by javydreamercsw
    First I never said this was my code I'm learning VBA in access. I'm looking for examples. If I use find/Update it'll add a new record if it doesn't find it?
    Don't worry about it Javy... We were all learning once... (I still am! ) lol....... Okay...... I'm freely admit that I'm no ADO expert...but I think I've done this right...

    Code:
    Dim cn As ADODB.Connection, ct As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=G:\UI_Database\Registro Database.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Sección_Estudiante", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    
    r = 10 ' the start row in the worksheet
    
    Do While Len(Range("Asistencia!C" & r)) > 0
    ' repeat until first empty cell in column A
    
    rs.FindFirst "ID = " & Range("F" & r).Value
    
    If rs.NoMatch Then 'No record in table with this ID
    
    rs.AddNew ' create a new record
    ' add values to each field in the record
    rs.Fields("ID") = Range("F" & r).Value
    rs.Fields("Número Curso") = Range("E3").Value
    rs.Fields("Sección") = Range("E4").Value
    rs.Fields("Nota") = Range("W" & r).Value
    rs.Fields("Ultima Fecha Asistencia") = Range("E" & r).Value
    rs.Fields("Semestre") = Range("E5").Value
    rs.Update
    ' stores the new record
    
    Else
    
    rs.Edit  'edit existing record
    rs.Fields("Número Curso") = Range("E3").Value
    rs.Fields("Sección") = Range("E4").Value
    rs.Fields("Nota") = Range("W" & r).Value
    rs.Fields("Ultima Fecha Asistencia") = Range("E" & r).Value
    rs.Fields("Semestre") = Range("E5").Value
    rs.Update
    
    End If
    
    r = r + 1 ' next row
    
    Loop
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub
    Basically I'm using the FindFirst method of the recordset... I'm assuming that ID is the unique identifier for the record in the table...

    So I said ... while looping through the values on the worksheet... Find the first record in the table where ID matches the values on the worksheet in F?? ... If it's not found, add a record... If it is found, update the values in the existing record...

    I haven't tested it... so let me know if you have any problems...

  5. #5
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Error method not found and highlighted FindFirst. Thanx for the help even if u can't help me from this point forward

  6. #6
    Join Date
    Feb 2004
    Posts
    199
    FindFirst is DAO's recordsets method, in ADO you should use Find
    so change FindFirst with Find
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  7. #7
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Originally posted by Kakha
    FindFirst is DAO's recordsets method, in ADO you should use Find
    so change FindFirst with Find
    Same problem with Edit... I'll try to fix it but help is welcomed...

  8. #8
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by javydreamercsw
    Same problem with Edit... I'll try to fix it but help is welcomed...
    Try commenting out that line... (add an apostrophe in front of rs.Edit...
    I was just reading how it's not explicitly necessary to add this line with ADO... (Wish I was being of more assistance... I much prefer DAO... Sorry...)

  9. #9
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Originally posted by Trudi
    Try commenting out that line... (add an apostrophe in front of rs.Edit...
    I was just reading how it's not explicitly necessary to add this line with ADO... (Wish I was being of more assistance... I much prefer DAO... Sorry...)
    I already figured that out, thanx anyways. The prob now is the NoMatch thingy it doesn't work. I tried If NOt rs.EOF but says that the records will repeat/duplicate. Any idea?

  10. #10
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by javydreamercsw
    I already figured that out, thanx anyways. The prob now is the NoMatch thingy it doesn't work. I tried If NOt rs.EOF but says that the records will repeat/duplicate. Any idea?
    Umm... Yes...

    rs.NoMatch (DAO) is true if the record is not found...
    with ADO, if the record is not found, it returns rs.EOF...

    Therefore... instead of NOT rs.EOF, you should be using rs.EOF

    If rs.EOF Then
    'add a record
    Else
    'update the record it found
    End If

    Hope this makes sense...

  11. #11
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Still error here's a pic. Something's wrong in the logic.rs.EOF doesn't return EOF. Dunno if u got what i mean... rs.Find is the one that gives the EOF thing....
    Attached Thumbnails Attached Thumbnails error.bmp  

  12. #12
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by javydreamercsw
    Still error here's a pic. Something's wrong in the logic.rs.EOF doesn't return EOF. Dunno if u got what i mean... rs.Find is the one that gives the EOF thing....
    Nope... I'm definitely not following what you mean...

    That error message is not coming up before you are trying to add a new record to the table with a duplicate key... It just can't be... Do you know how to step through code??

    Is it possible for you to post a sample database with the needed components??... I'm sure I could fix this if I saw what was going on...

  13. #13
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Here's a sample database + all components. The info in the database was created with the actual VBA programing in the exel sheet. To test it u might need to modify the code a lil' because i had the DB in a jump drive (Drive G). Creating isn't an issue, updating is the prob...
    Attached Files Attached Files
    Last edited by javydreamercsw; 02-17-04 at 08:53.

  14. #14
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by javydreamercsw
    Here's a sample database + all components. The info in the database was created with the actual VBA programing in the exel sheet. To test it u might need to modify the code a lil' because i had the DB in a jump drive (Drive G). Creating isn't an issue, updating is the prob...
    I knew I should have taken more languages in school!!! lol ...I'm sorry... I'm trying here Javy but I can't make sense of this thing...

    First, looking at the code, you are updating or adding values to the database record... from cells that don't exist???... You've merged E1 to E5 to show the date... yet your code is assigning values to the table fields from E3, E4 and E5...

    Second, your table design is looking very strange... You have ID as a text field, but you're only allowing numeric data... You've designated it as the primary key for the table, yet you are allowing null values??... From the code you are taking ID from column F on the spreadsheet, which can be blank or null...

    Could you give me instructions on where to put what values into the spreadsheet?... I can't even get it to add a record without getting a validation error... Thanks!

    (I did update the location of the files in the code... )

  15. #15
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    About the design u where right about the numeric/text problem, already fixed that, still need to check if that was part of the problem. I solved the problem but found another. That's the new set of codes:
    Sub Update_Section()
    '
    ' Update_Curcs Macro
    ' Macro recorded 2/16/2004 by Fam. Ortiz
    '
    ' Keyboard Shortcut: Ctrl+t
    '
    Dim cn As ADODB.Connection, ct As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=E:\UI_Database\Registro Database.mdb;"
    ' open a recordset
    Set rc = New ADODB.Recordset
    rc.Open "Sección", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 3 ' the start row in the worksheet
    rc.Find "section = " & Range("B3").Value

    If rc.EOF Then 'No record in table with this ID
    rc.AddNew
    End If
    rc.Fields("courseno") = Range("B4").Value
    rc.Fields("section") = Range("B3").Value
    rc.Fields("semester") = Range("D4").Value
    rc.Fields("instructor") = Range("D3").Value
    rc.Fields("time") = Range("B6").Value
    rc.Fields("days") = Range("D5").Value
    rc.Update
    ' stores/updates the new record

    rc.Close
    Set rc = Nothing
    cn.Close
    Set cn = Nothing
    End Sub
    Sub Update_Student()
    '
    ' Update_Curcs Macro
    ' Macro recorded 2/16/2004 by Fam. Ortiz
    '
    ' Keyboard Shortcut: Ctrl+t
    '
    Dim cn As ADODB.Connection, ct As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=E:\UI_Database\Registro Database.mdb;"
    ' open a recordset
    Set rc = New ADODB.Recordset
    rc.Open "Estudiante", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 10 ' the start row in the worksheet
    rc.Find "ID = " & Range("F" & r).Value

    If rc.EOF Then 'No record in table with this ID
    rc.AddNew
    End If

    Do While Len(Range("C" & r)) > 0
    ' repeat until firct empty cell in column A
    rc.Fields("ID") = Range("F" & r).Value
    rc.Fields("Nombre") = Range("D" & r).Value
    rc.Fields("Apellidos") = Range("C" & r).Value
    ' add more fields if necessary...
    rc.Update
    ' stores the new record
    r = r + 1 ' next row
    Loop

    rc.Close
    Set rc = Nothing
    cn.Close
    Set cn = Nothing
    End Sub

    Sub Update_Course()
    '
    ' Update_Curcs Macro
    ' Macro recorded 2/16/2004 by Fam. Ortiz
    '
    ' Keyboard Shortcut: Ctrl+t
    '
    Dim cn As ADODB.Connection, ct As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=E:\UI_Database\Registro Database.mdb;"
    ' open a recordset
    Set rc = New ADODB.Recordset
    rc.Open "Cursos", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 3 ' the start row in the worksheet

    If rc.EOF Then 'No record in table with this ID
    rc.AddNew ' create a new record
    End If
    ' add values to each field in the record
    rc.Fields("courseno") = Range("B4").Value
    rc.Fields("course_name") = Range("B5").Value
    ' add more fields if necessary...
    rc.Update
    ' stores the new record
    rc.Close
    Set rc = Nothing
    cn.Close
    Set cn = Nothing
    End Sub

    Sub Update_Section_Student()
    '
    ' Update_Curcs Macro
    ' Macro recorded 2/16/2004 by Fam. Ortiz
    '
    ' Keyboard Shortcut: Ctrl+t
    '
    Dim cn As ADODB.Connection, ct As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=E:\UI_Database\Registro Database.mdb;"
    ' open a recordset
    Set rc = New ADODB.Recordset
    rc.Open "Sección_Estudiante", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 10 ' the start row in the worksheet
    rc.Find "ID = " & Range("F" & r).Value

    If rc.EOF Then 'No record in table with this ID
    rc.AddNew ' create a new record
    End If
    Do While Len(Range("C" & r)) > 0
    rc.Fields("ID") = Range("F" & r).Value
    rc.Fields("Número Curso") = Range("E3").Value
    rc.Fields("Sección") = Range("E4").Value
    rc.Fields("Nota") = Range("W" & r).Value
    rc.Fields("Ultima Fecha Asistencia") = Range("E" & r).Value
    rc.Fields("Semestre") = Range("E5").Value
    rc.Update
    ' stores the new record
    r = r + 1
    Loop
    rc.Close
    Set rc = Nothing
    cn.Close
    Set cn = Nothing
    End Sub

    As u can see I took some time (that i didn't had till today's evening) to recode from scratch (of course watching the old code while learning :P)
    and pulled it out. It updates/creates the record as needed. Now the problem is the following:
    I use those codes from buttons in the Excel sheet, is there a way to open all those AODB objects at the same time so i can put all code into one button? I've tried but nothing worked so far. I'll post more clear errors later, just wanted to post a half-way answer. Any idea or if anyone needs the update file catch it here. ( remember to modify code so the DB is in the right place, my code says E:\)
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •