Reading data from Excel & inserting records directly into Access?

[Reimondo]

Hi,

I'm helping a friend build an access database and have a problem.
I currently have a list of hyperlinks contained within an excel file. At the moment, I have to insert each hyperlink one by one via a RunSQL statement using a macro. This method works but will be rather troublesome for my friend to update the list of links later on (as he does not know much about Access).
I wish to be able to create a button in the form that can automatically read each hyperlink (each cell) from the excel file, and insert them into a sub form table within access.
So that later on when my friend wants to add new hyperlinks, he can simply add them via Excel/Word (the hyperlinks can either be stored in Excel or Word)

Does anyone know how to do this? I am only familar with the Access interface design and macros, but not very familar with VB.

Your help will be most appreciated,

Raymond

 

[tledwards]

Try linking the Excel table to the database then data is available for use. Form the database use File/Get External Data/ Link Tables.

 

[Reimondo]

Thanks for your reply.

When I import the hyperlinks from the excel spreadsheet, they become plain 'text' fields in Access. Furthermore, Access doesn't allow me to change the field type for linked tables?
Any ideas on how to keep the linked data as hyperlinks (without losing the hyperlinked addresses aswell?)
The hyperlinks are linked to Word documents.

 

[BNPMike]

I just created an Excel sheet, imported it into Access (you can't preserve the hyperlink property at this stage) and then in design view changed the field to hyperlink. And guess what? - they're now hyperlinks again! Couldn't be easier...

 

[Reimondo]

Thanks for your reply.

It seems to work if you 'import' the excel sheet to Access but if I create a linked table, then Access doesn't allow me to change the table properties. As I wish to have a linked table to Excel, not an imported one.

Also with the 'import' facility, even though you can change the field back to a hyperlink, the original hyperlink address is lost though? As I wish to preserve the original hyperlink address.

 

[BNPMike]

&quot;>>>Also with the 'import' facility, even though you can change the field back to a hyperlink, the original hyperlink address is lost though?<<<<&quot;

Nothing is lost. The hyperlink is preserved throughout. It just temporarily loses its ability to trigger an html call. It's a solution. If you insist on linking, it's not...