Add hyperlink to table when linking excel to access

Question

I have a function that successfully takes data from multiple excel files in different locations and combines them all into one access table.

Dim rst As New ADODB.Recordset
rst.Open "Files", CurrentProject.Connection
rst.MoveFirst
Do While Not rst.EOF
MsgBox rst.Fields("Filelist")    
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MechanicalData", rst.Fields("Filelist"), True    


Dim rstmech As New ADODB.Recordset
rstmech.Open "MechanicalData", CurrentProject.Connection
rstmech.MoveFirst
Do While Not rstmech.EOF
    If IsNull(rstmech.Fields("FolderPath")) Then
        rstmech.Fields("FolderPath") = rst.Fields("Filelist")    'Error 3251 here
        rstmech.Update
        End If
        rstmech.MoveNext
Loop
        rstmech.Close
        Set rstmech = Nothing

rst.MoveNext
Loop
rst.Close
Set rst = Nothing

I want to add a field which has the proper hyperlink for each row in the new table, so that the user will be able to navigate back to the excel file. Is there a quick and dirty way to do this? I know I could probably throw a nested loop under the DoCmd.TransferSpreadSheet clause, but I don't know how I would index that.

I've updated my code with a nested while loop, which is supposed to go through each row in the new table and add the appropriate link. I'm getting the error:

Run-Time error '3251':
Current Recordset does not support updating. 
This may be a limitation of the provider, or of the selected locktype.

Any ideas as to how to fix this?

Alternative, possibly faster method: the outer loop above currently adds data to the bottom, so if the link is added to each record at the end of each iteration, I can have it add the link only to empty entries in that field, essentially doing one batch per hyperlink. Something along the lines of:

UPDATE [MechanicalData] SET [MechanicalData].OpenFile = rst.Fields("Filelist")
WHERE [MechanicalData].OpenFile Is Null

Obviously I can't run that code as is, but I am not sure where to go from there.

Presumably your spreadsheets have more than one row each, do you want to repeat the hyperlink for each row in each spreadsheet? As a side-question, do your spreadsheets change location/name, or are they fixed? If the latter, you might consider linking directly to the files as linked tables. You can then run append queries to copy the data to a persistent table in Access. This allows you to define the data handling more accurately. — Tim, Apr 15 '14 at 04:03
@Tim I do want to repeat the hyperlink for each row in each spreadsheet. The spreadsheets are located under the same parent directory, but no further subdirectories are guaranteed. Currently the code I have recursively looks for "summary" in the name of each file in the parent directory, which is how the "Files" table is populated. — user3241316, Apr 15 '14 at 13:41

score 0 · Accepted Answer · answered Apr 15 '14 at 16:45

0

I would build an Update query with a parameter which you can feed in & execute on every iteration of the loop in your code. This discussion is probably for an older version than you are using, but I think the points raised could be useful: http://www.utteraccess.com/forum/modify-hyperlink-addre-t1927596.html

answered Apr 15 '14 at 16:45

Tim

756
1
7
12

I've added 2 WIP solutions to my main post. I'd appreciate any feedback you can give – user3241316 Apr 15 '14 at 19:47
Your recordsets are ReadOnly - "The default cursor for an ADO Recordset is a forward-only, read-only cursor located on the server." Documentation here >> http://msdn.microsoft.com/en-us/library/windows/desktop/ms675544%28v=vs.85%29.aspx But I still recommend the Update query over the recordset approach >> http://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update – Tim Apr 16 '14 at 04:35

Add hyperlink to table when linking excel to access

1 Answers1