Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: Edit Record In Access from Excel Using VBA

    I want to edit an existing record in an Access table using VBA from Excel.

    The table in access has a primary key.
    A cell in Excel will has the numerical key.

    So I need the VBA to connect to my Access table.
    Locate the correct record by looking at an Excel cell.
    Update specific fields in that record.

    Seems pretty simple but I have spent hours looking for help so I am posting. The problem probably lies in my not understanding ADO and such very well.

    Thanks for your time!

  2. #2
    Join Date
    Mar 2008
    Location
    East Anglia
    Posts
    7
    I am fairly sure that you cannot acheive this - the data you can view in Excel from an Access database is read only.

  3. #3
    Join Date
    May 2009
    Posts
    5
    Thanks for your reply. I do appreciate your giving it some thought. However, I have been able to figure this one out with some code that I found and manipulated.
    Code:
    Sub LogOut()
    
    
     'Add a reference to the Microsoft ActiveX Data 2.8 or later Object Library
     'via the Tool | References... in the VB-editor
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stDB As String
    Dim stCon As String
    
     'Instantiate the ADO COM's objects.
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
     
     'Pathway and name of the database
    stDB = "T:\Trad\Data\db_StopLoss.mdb"
     
     'Create the connectionstring.
    stCon = "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" & _
    "Data Source=" & stDB & ";"
     
     'Open the connection
    cnt.Open stCon
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Find and Update the record in Access
    With rst
        .Index = "PrimaryKey"
        .CursorLocation = adUseServer
        .Open "tbl_QuoteLog", cnt, 1, 3, adCmdTableDirect
        .Seek Range("CaseNum").Value
        If Not rst.EOF Then
            .Fields("UndWrite") = Sheets("LogOut").Range("b9").Value
            .Fields("Status") = Sheets("LogOut").Range("b11").Value
            If Sheets("LogOut").Range("k2") = "p" Then
            .Fields("QtDeadDt") = Sheets("LogOut").Range("b12").Value
            End If
            .Fields("SpecDed") = Sheets("LogOut").Range("b13").Value
            .Fields("EmpNum") = Sheets("LogOut").Range("b14").Value
        .Update
        Else
        MsgBox "Log Out Failed"
        End If
    End With
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    'Close the recordset and close the connection.
    rst.Close
    cnt.Close
    
    Set cnt = Nothing
    Set rst = Nothing
    
    End Sub

  4. #4
    Join Date
    Mar 2008
    Location
    East Anglia
    Posts
    7
    Well found, usefull that for the future!

Posting Permissions

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