Connect Access to Excel, use Excel userform to update Access record

Question

I know it's not recommended but my hands are tied so I have to use this for now (as a patch until I fix the database)

I am in a multi-user environment. The data is on Access and they need to access it to update some information so other department can pull reports and use the newly updated data.

Fairly simple, however, the database wasn't maintained for over 6 months, the infrastructure was bad from the beginning but I can't afford to refactor now.

I have create a userform on Excel where I can update, modify info on sheets. I want to be able to pull the information from access into the excel sheets, and show a userform to the different users, they will update what they have to update and upon closing the userform, the access record they were working on will be updated.

I haven't find any solution for that. I seemed to have find something with DAO but I'm not sure.

Answer 1

Here is a super crude example of how to use a userform to write sql. Make sure you have the correct references.

Private Sub CommandButton1_Click()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim vAr$
    Dim arrResults(2) As String

    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            tdf.RefreshLink
        End If
    Next

    Set db = DBEngine.OpenDatabase("C:\Users\Ashleysaurus\Desktop" & "\" & "xyzmanu3.accdb")

    vAr = TextBox1.Value

    Set rst = db.OpenRecordset("SELECT PartName, PartNum, Quantity FROM InventoryTbl WHERE PartName='" & vAr & "';")

    i = 0
    rst.MoveFirst
    Do Until rst.EOF
        arrResults(i) = rst.Fields(0)
        arrResults(i) = rst.Fields(1)
        arrResults(i) = rst.Fields(2)
        rst.MoveNext
        i = i + 1
    Loop

    TextBox2.Value = arrResults(0)
    TextBox3.Value = arrResults(1)
    TextBox4.Value = arrResults(2)

    Set tdf = Nothing
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing

End Sub

Private Sub CommandButton2_Click()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim vAr$: vAr = TextBox1.Value
    Dim vAr2$: vAr2 = TextBox2.Value
    Dim vAr3$: vAr3 = TextBox3.Value
    Dim vAr4$: vAr4 = TextBox4.Value
    Dim vbSql$

    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            tdf.RefreshLink
        End If
    Next

    Set db = DBEngine.OpenDatabase("C:\Users\Ashleysaurus\Desktop" & "\" & "xyzmanu3.accdb")

    vbSql = "UPDATE InventoryTbl SET PartName='" & vAr2 & ", Quantity='" & vAr4 & " WHERE PartNum='" & vAr3 & "';"
    db.Execute vbSql

    Set tdf = Nothing
    db.Close
    Set db = Nothing

End Sub

Button1 retrieves the data, stores into an array and then dumps contents into textboxes. Button two takes contents from textboxes and then up;dates the tables based off of that.

Since this isn't a script writing service, you should be able to tinker your way into what you're looking for off of this.