Please Help Linking access to a database

[amfroehlich]

I have made a database to track Marketing Events. The old way of tracking is users entered information on Excel spreadsheets, which they like to do. Is it possible for those already made spreedsheets to fill the database feilds.

If so, how?

 

[Joe4]

Here are two options:

1. From your Excel file, create a file to import by creating a header row with the correct field names and import into your Access table.

2. Link your Excel data into an Access table.

The advantage to #1 is that you can add data to already existing data. The advantage to #2 is that if you change the data in your Excel file, it will also be reflected in your database.

To do either of these, from the Access table object, right click and select "Import" or "Link Tables".

 

[amfroehlich]

I like option 2 how do you do this?

 

[Joe4]

First you need to format your Excel spreadsheet. Have your headers in row 1, then only data in rows below it. It also makes it easier if you make sure the Excel file is only one Worksheet.

Then from the Tables object in Access, right click on your mouse and select "Link Tables". Browse and find your file (make sure you change File Type to Excel), then follow the few steps of the Wizard to finish.

Now because the tables are linked, any changes to this table in Access will be reflected on the Excel file and vice cersa.

 

[amfroehlich]

OK now that I have done that I see and excel icon on my table view. The information is updating correctly. But how can I get the data out of the excel file to use in Access?

 

[Norie]

Treat the linked Excel file as you would a normal Access table.

 

[amfroehlich]

OK now that I have done that I see and excel icon on my table view. The information is updating correctly. But how can I get the data out of the excel file to use in Access?

 

[Norie]

Sorry I don't understand what you mean.

You can access the data, yes?

If you want to have no further link with the Excel file you need to import the data not link.

This is the first of jmiskey's proffered solutions.

 

[Joe4]

What is it that you are hoping to accomplish?

There is no need to pull the data from Excel to Access. Access can use the data just fine from where it is.

Forget what the icon looks like. For Access purposes, this linked table is treated like any other table in Access. You can do query, forms, and reports on it.

 

[amfroehlich]

I am need to use this information to update anthor table in Access becuase i am running a report off that table.