Results 1 to 5 of 5
  1. #1
    chacha1 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    3

    How to update the data into Access Database in Excel VBA

    Dear all,

    May I know how to update the modify data?

    I export the list of data from Access, then if the user have modify a data and he/she click "Update" button.



    The data will updated.

    Below code is my insert data.

    Code:
    Private Sub cmdadd_Click()
    
    
    If Me.txtStaffID.Value = "" Then
    MsgBox "Please enter the Staff ID", vbExclamation, "Staff Master Form"
    Me.txtStaffID.SetFocus
    Exit Sub
    End If
    
    
    If Not IsNumeric(Me.txtStaffID.Value) Then
    MsgBox "The Staff ID must contain a number.", vbExclamation, "Staff Master Form"
    Me.txtStaffID.SetFocus
    Exit Sub
    End If
    
    
    If Me.txtName.Value = "" Then
    MsgBox "Please enter the Name.", vbExclamation, "Staff Master Form"
    Me.txtStaffID.SetFocus
    Exit Sub
    End If
    
    
    If Me.cbopayrollentity.Value = "" Then
    MsgBox "Please select the Payroll Entity.", vbExclamation, "Staff Master Form"
    Me.txtStaffID.SetFocus
    Exit Sub
    End If
    
    
    If Me.cbodept.Value = "" Then
    MsgBox "Please select the Department.", vbExclamation, "Staff Master Form"
    Me.txtStaffID.SetFocus
    Exit Sub
    End If
    
    
    If Me.txtsite.Value = "" Then
    MsgBox "Please enter the Site.", vbExclamation, "Staff Master Form"
    Me.txtStaffID.SetFocus
    Exit Sub
    End If
    
    
    If Me.txtNickname.Value = "" Then
    Me.txtNickname.Value = "null"
    Exit Sub
    End If
    
    
    If Me.cbodeptbu.Value = "" Then
    Me.cbodeptbu.Value = "0"
    Exit Sub
    End If
    
    
    Dim cn As Object
    Dim strQuery As String
    Dim staffid As String
    Dim name As String
    Dim nickname As String
    Dim deptbu As String
    Dim payrollentity As String
    Dim dept As String
    Dim site As String
    Dim myDB As String
    
    
    
    
    staffid = Me.txtStaffID.Value
    name = Me.txtName.Value
    nickname = Me.txtNickname.Value
    deptbu = Me.cbodeptbu.Value
    payrollentity = Me.cbopayrollentity.Value
    dept = Me.cbodept.Value
    site = Me.txtsite.Value
    
    
    myDB = "database.mdb"
    
    
    Set cn = CreateObject("ADODB.Connection")
    
    
    With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = myDB
    .Open
    End With
    
    
    
    
    strQuery = "INSERT INTO STAFFMASTER ([STAFFID],[NAME],[NICKNAME],[DEPTBU],[PAYROLLENTITY],[DEPT],[SITE])" & _
    "VALUES (""" & staffid & """,""" & name & """,""" & nickname & """," & deptbu & ",""" & payrollentity & """,""" & dept & """,""" & site & """);"
    
    
    cn.Execute strQuery
    MsgBox "Successful Add", vbInformation, "STAFF MASTER FORM"
    Dim ctl As Control
    For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
    ctl.Value = ""
    ElseIf TypeName(ctl) = "CheckBox" Then
    ctl.Value = False
    End If
    Next ctl
    cn.Close
    Set cn = Nothing
    
    
    End Sub


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I have no idea what you are doing with that code. Is it in Excel or Access?

    Maybe this will help you http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    chacha1 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    3
    Quote Originally Posted by June7 View Post
    I have no idea what you are doing with that code. Is it in Excel or Access?

    Maybe this will help you http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    Hi there, is it in the Excel but the database is Access

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Did you mean "it is in the Excel"?

    I don't understand the code. Did you build a form in Excel? Is that where textboxes and comboboxes are?

    Why is Excel even involved?

    What is wrong with the code you have, what happens - error message, wrong results, nothing?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    chacha1 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    3
    Quote Originally Posted by June7 View Post
    Did you mean "it is in the Excel"?

    I don't understand the code. Did you build a form in Excel? Is that where textboxes and comboboxes are?

    Why is Excel even involved?

    What is wrong with the code you have, what happens - error message, wrong results, nothing?

    Hi, i able to do this. Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  2. Update Existing Access Tables with Data from Excel
    By ChelseaC in forum Import/Export Data
    Replies: 7
    Last Post: 08-10-2014, 04:28 PM
  3. Data update Access to Excel
    By b82726272 in forum Programming
    Replies: 3
    Last Post: 06-19-2014, 09:06 AM
  4. Replies: 7
    Last Post: 04-16-2014, 07:07 AM
  5. Replies: 1
    Last Post: 01-09-2013, 04:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums