Question How can I link Excel files to a current database as a LINK?

[Ponoms]

I have a simple Access database (no dependencies, calculations, just pure data). Its about 10 columns, and 3200 rows.

I would like to create a link within that database (in a separate column) and I would like that link to open a specific Excel file when it is clicked. Its like a URL to a website, but linking to Excel file. Is anything close to that possible? Maybe a link can be a button or something else that is easy to access.

Second part of the problem is that I will have 100s of these Excel files. If it possible to link to these Excel files then would it possible to save them all within my Access database? In the end I want to have have ONE Access file that would contain Excel files and be opened from any computer (that has Access) without a hassle and path-finding issues for those Excel files.

I appreciate any input. Thanks!

EDIT: If these Excel files have to be converted to Access databases to be linked from the big database then I can do that as well through Import wizard. I would prefer those files to stay in Excel. Nothing in the actual database will be dependent on those files (for the exception of the link itself if it were to happen).

EDIT2: If I convert Excel to Access tables then how do I "hyperlink" them to the big database?

These Excel files I want hyperlinked have very different information and contain too many details about chemical formulations and things unrelated to the real database information. So, these files have to be somehow separate (and yet be openable from the database) from the rest of the database regardless of format.

 

[apr pillai]

Hyperlinks to Excel files

1. Add a New field to your Table with Hyperlink Field type.
2. Save the Table and Open it in Datasheet View.
3. Right-Click on the Hyperlink field of a record and select Hyperlink - -> Edit Hyperlink.
4. Click Browse for Files... at the right side, find your file, select it and click ok and OK again to close the dialog control.

Hyperlink to the file is saved in your table. Links to each Excel File you have to create in separate records or in separate Fields.

5. After saving the link, Click on it to open the Excel File.

Visit the page

http://www.msaccesstips.com/2007/05/open-forms-with-hyperlinks-in-listbox.shtml

for another example.

www.msaccesstips.com

 

[georgedwilkinson]

It should be quite easy to set up a hyperlink to an external Excel file. The problem comes when you want to store the Excel inside the database. You are most likely right in assuming that you'd have to store the Excel data in a table, at which time hyperlinks no longer apply.

Check out the "Is Hyperlink" property of your text or combo box for the external version.

 

[Ponoms]

It should be quite easy to set up a hyperlink to an external Excel file. The problem comes when you want to store the Excel inside the database. You are most likely right in assuming that you'd have to store the Excel data in a table, at which time hyperlinks no longer apply.

Check out the "Is Hyperlink" property of your text or combo box for the external version.

If I convert Excel to Access tables then how do I "hyperlink" them to the big database?

See, these Excel files want I hyperlinked have very different information and contain too many details about chemical formulations and things unrelated to the real database information. So, these files have to be somehow separate (and yet be openable from the database) from the rest of the database regardless of format.


Thanks to all for your replies!

 

[Ponoms]

Bump! Any other possibilities to keep everything in ONE file?

 

[Pat Hartman]

Access is not a good database in which to store OLE objects although A2007 is much better than earlier versions.

I have an application that manages 5,000 word documents. The documents are stored in a series of directories depending on category. The database contains a table that defines the categories and the "path" to that category. Another table contains a list of the documents along with their category type and document name. To make this work, I could not use the Hyperlink data type since it requires that you store the entire path which I did not want to do. I wanted the path to be separate from the file name.

If you go my method, you need code in the clidk event of a form to look up the path and concatenate it with the file name and then you can use the FollowHyperLink method.

 

[Ponoms]

Access is not a good database in which to store OLE objects although A2007 is much better than earlier versions.

I have an application that manages 5,000 word documents. The documents are stored in a series of directories depending on category. The database contains a table that defines the categories and the "path" to that category. Another table contains a list of the documents along with their category type and document name. To make this work, I could not use the Hyperlink data type since it requires that you store the entire path which I did not want to do. I wanted the path to be separate from the file name.

If you go my method, you need code in the clidk event of a form to look up the path and concatenate it with the file name and then you can use the FollowHyperLink method.

Dang I have no idea how to do anything you just said. I don't know how to code anything.

Were you trying to say that you can specify the filename and the system will look for the file on any computer?

Do you know how PowerPoint allows you to create a CD presentation and it includes all the image and sound files in one folder?

Could I maybe save all these files in the same folder as the database? If I keep everything in the same folder...would Access find correct files if this whole folder would move to a different computer (and different path) ?

 

[Pat Hartman]

With the method I used for the app I described, I stored the path name in a table. If the paths ever need changing, the table will need to be updated.

To answer your question, Access can find the files whereever they are if you write the code to do it. If you want it to look in the database directory, you can do that but you need to find out the name of the database directory (I'm assuming that you keep moving the database and that is what is causing the problem) and then concatenate it with the actual file name so you can use the FollowHyperlink method.

Public Function OpenManual()
    Dim strInput As String
    Dim strDBName As String
    
On Error GoTo Err_Proc
    strDBName = DLookup("Database", "qLinkedTables")
    strInput = Left(strDBName, InStrRev(strDBName, "\"))
    strInput = strInput & "DEP_Audit_Database_UserManual.doc"
    Application.FollowHyperlink strInput, , True

Exit_Proc:
    Exit Function
Err_Proc:
    Select Case Err.Number
        Case 490
            MsgBox "Make sure that the applicaiton manual is in the same directory as the data file.", vbOKOnly
            Resume Exit_Proc
        Case Else
            MsgBox Err.Number & "-" & Err.Description
            Resume Exit_Proc
    End Select
End Function

Here is a snippit that will get just the path name.

Dim dbs As DAO.Database
Dim path As String
Set dbs = CurrentDb
path = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

 

[Dairy Farmer]

Why not just get ride of the Excel files all together and just use Access.

Basically Access tables are very simple spreadsheets. Forms, queries and reports are used to enter and display the data. So instead of having hundreds of Excel files you have one DB. Further to that is that everything is in a standardised formate.

A bonus is that the users need not even have Access or the version of Access that you create the app in. Just deploy the app with Runtime.