Access Tables in Excel

  • Thread starter Thread starter Karlos
  • Start date Start date
K

Karlos

ok....i have an access table that i want to be updatable
in access and also excel.

i can import an access table into excel but that only
allows me to update in access.

i can link in an excel spreadsheet and have them both
updatable...but i only know how to do that if the data
exists firslty in excel. my data is in access

so how can i get an access table to be fully updatable in
both excel and access.

also the excel spreadsheet will be used over a network by
3 people. does that cause complications?

thanks in advance
 
i can link in an excel spreadsheet and have them both
updatable...but i only know how to do that if the data
exists firslty in excel. my data is in access
Click to expand...
No: I don't think that Excel has the concept of a "linked worksheet". You
could try asking in the Excel newsgroups, but I've not heard of anything
like it.
so how can i get an access table to be fully updatable in
both excel and access.
Click to expand...
You can create a Forms to read and write the data in the Jet database
direct - it's a bit of work but not too hard. If it comes to that you can
use the Worksheet_Change event to save any edits back to the database. It's
all the common OpenRecordset and .Edit and .Update that you are already
used to in Access VBA.
also the excel spreadsheet will be used over a network by
3 people. does that cause complications?
Click to expand...
I would give them separate copies: jet shares mdb's better than Excel
shares .xls's.

Hope that helps


Tim F
 
Hi Karlos,

1) In Access, click on the table (in the database window) you wish to
link to Excel
2) Click Copy
3) Go to Excel and open a new workbook
4) Click on the upper-left-hand cell in Sheet 1
5) From the menu, select Edit | Paste Special
6) Click the Paste Link radio button
7) Select Text from the As: box and click OK

The only problem I know of with this technique is that, IF you are using
Office 97, and you try to open the Excel spreadsheet that contains the
link, Excel will ask you if you want to update the linked information
from the source file. If you click "Yes," then Excel will tell you it
can't find the remote data. This means you have to open the Access db
that contains the linked table _before_ you try to open the Excel
workbook. Not a major hassle, but kind of annoying.

Alternatively, you can use Microsoft Query to retrieve data from an
external database. If you ran the default settings when you installed
Excel or Office, MS Query will not be installed. Get your disc and
search the Excel OLH for "Install Microsoft Query" for instructions.
Then MS Query Help will walk you through creating a query (it's very
much like creating a query in Access, and uses the same language)...you
might also search both the Access and Excel Help files under "OLE" and
"DDE."

Not sure if making this linked table available on a network will affect
the link.

hth,

LeAnne
 
Back
Top