Linking to Excel...?

[AlanGNW]

I want to use the data contained in Access table in an Excell app. What is the best way to link them - preferably in 'real time' - is there a way excell can read directly from the tables? We need to analyse some of the data in the database through Excell....

Any suggestions welcomed...?

 

[Sergeant]

Two things:
1. Open table in Access...click Tools/Office Links/Analyze it with EXCEL

2. From Excel, Data/Import External Data/New Database Query (the query will stay with the workbook so you can go Data/Refresh Data in the future.)

 

[AlanGNW]

Thanks - I'll give it a try. Sounds pretty simple - I hope. It needs to be for me

 

[Sergeant]

Two things:
1. Open table in Access...click Tools/Office Links/Analyze it with EXCEL

2. From Excel, Data/Import External Data/New Database Query (the query will stay with the workbook so you can go Data/Refresh Data in the future.)

1 & 2 are either/or methods.

 

[AlanGNW]

Will option 1 allow me to refresh dat as option 2 does?

 

[Sergeant]

I don't think so.

There are other options that require familiarity with VBA:
1. Export the data from Access via VBA using TransferSpreadsheet method of DoCmd object. (Data in XL is only as new as the last time you refreshed it from Access.)
2. Use ADO from XL to pull data from Access in real time. (Requires VBA in XL.)
3. With option 2 of my earlier post, you can automate the refresh of the database query from VBA using ThisWorkbook.RefreshAll

 

[AlanGNW]

I guess the simle options from earlier will work best for me. Know nothing of VB. Can I put multiple tables in a workbook this way?

I guess option 2 sounds better - it sounds like the data can't be refreshed using option 1...

 

[AlanGNW]

Is it also possible to link into a querie rather than an entire table?

 

[Sergeant]

I'm pretty sure that Queries are interchangeable with Tables in most contexts. Why don't you try it and see?