Save text in access when clicking on a hyperlink in Excel

Question

I have an Excel sheet with a code that gives every row a hyperlink at the end. What should be happening next is: Once i click this hyperlink, "Data is confirmed" should be saved to an Access file.

For example: There are 10 rows, thanks to the current code, a hyperlink gets added to every single row. If i click on this hyperlink on row 8 , "Data is confirmed" should be added to the Access file on row 8 (and only row 8!)

Thanks to Basodre, i currently have this code but can't figure out a way to get a text saved in Access. Any ideas?

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'Confirm that this is a hyperlink in column 3

If Not Intersect(Target.Range, Columns(3)) Is Nothing Then
    MsgBox SaveData(Target.Range)
End If

End Sub

Private Function SaveData(rng As Range) As Boolean
Debug.Print rng.Address & " has been saved."
SaveData = True
End Function

How is this different from your [previous question](https://stackoverflow.com/questions/64427382/save-data-to-access-file-after-clicking-on-a-hyperlink)? Did the ADODB approach not work? — BigBen, Oct 19 '20 at 17:37
I'll follow up here from our previous thread. I think you're going to be better off determining which of the fields from Access serves as the primary key, and using that to make the update. Updating based on row number can lead to inconsistent results. What if someone sorts the Excel sheet such that row is now actually in position 10? If you want to post the database schema, we might be able to help determine a primary key. Once we have that, the rest should be straightforward. (Side note, I don't know if you can update Access based on row number. Someone else will have to chime in). — basodre, Oct 19 '20 at 17:52
Why is Excel used as interface? Can't use an Access table row number to specify record, must use a field value. — June7, Oct 19 '20 at 18:01
The code above didn't work for me. Basodre, There is a unique row ID in each line so that can be used as primary key. — JustinSane, Oct 20 '20 at 07:51

HackSlash · Answer 1 · 2020-10-19T20:46:10.030

I have an class module I use for creating DB objects. It's name is AccessBackEnd.

Option Explicit

' ConnectModeEnum
'Private Const adModeRead = 1
'Private Const adModeReadWrite = 3
Private Const adModeShareDenyNone As Long = 16

' adStateEnum
'Const adStateClosed As Long = 0                  'Indicates that the object is closed.
Const adStateOpen As Long = 1                    'Indicates that the object is open.
'Const adStateConnecting As Long = 2              'Indicates that the object is connecting.
'Const adStateExecuting As Long = 4               'Indicates that the object is executing a command.
'Const adStateFetching As Long = 8                'Indicates that the rows of the object are being retrieved.

' CursorTypeEnum
Const adOpenStatic As Long = 3

' LockTypeEnum
Const adLockOptimistic As Long = 3

Private dataSource As Object

Public Property Get Connection() As Object
    If dataSource Is Nothing Then
        Set dataSource = CreateObject("ADODB.Connection")
        
        With dataSource
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Mode = adModeShareDenyNone
        End With
    End If
    
    Set Connection = dataSource
End Property

Private localFileName as string
Public Property Get FileName() As String
        FileName = localFileName 
End Property

Public Property Set FileName(newFileName As String) As String
        localFileName  = newFileName 
End Property

Public Sub Connect(ByVal dataBaseName As String)
    Connection.Open "Data Source=" & dataBaseName & ";" 
End Sub

''' Recordset command is used to access table data
Public Function Record(ByVal sqlQuery As String) As Object
    If Not ((Connection.state And adStateOpen) = adStateOpen) Then
        Connect FileName
    End If
    
    Set Record = CreateObject("ADODB.Recordset")
    Record.Open Source:=sqlQuery, ActiveConnection:=Connection, CursorType:=adOpenStatic, LockType:=adLockOptimistic
End Function

Public Sub Dispose()
    If dataSource Is Nothing Then
        Debug.Print "You disposed of nothing..."
    Else
        If (Connection.state And adStateOpen) = adStateOpen Then dataSource.Close
        Set dataSource = Nothing
    End If
End Sub

This example shows how you can update an Access field from Excel.

Dim thisDB As AccessBackEnd
Set thisDB = New AccessBackEnd
thisDB.FileName = "Your DB full path goes here.accdb"
With thisDB.Record("SELECT * FROM yourTable WHERE ID=" & PrimaryKey & ";")
    If Not (.EOF Or .BOF) Then
        .Fields("Your Field to update goes here").Value = rng.Address
        .Update
    End If
End With

thisDB.Dispose

Save text in access when clicking on a hyperlink in Excel

1 Answers1