How? Excel file that updates to most current Access data. 1

[Shinken]

I'm looking for a way to create an Excel spreadsheet that is linked to an Access query, but in a way that automatically updates the Excel spreadsheet as the data in Access changes, so that whenever the Excel spreadsheet is loaded it contains the latest data. Is there a way to do this?

BACKGROUND:

I've combined 7 crosstab queries into a single query based on a somewhat long SQL statement with 6 UNIONs. (Don't laugh, it actually runs very fast and is preferable to nesting the queries...but I digress)

The query (11 row x 14 columns in datasheet view) is the basis for a more complex Excel spreadsheet analyzing the data. Lets call this the ReportSpreadsheet

What I want to do is link the query into an Excel spreadsheet in a way that the spreadsheet is updated as the Access data changes.

I've tried using "Analyze w/Excel..." to put the Access query into an Excel spreadsheet (call it qrySomething) and then linking cells in ReportSpreadsheet to cells in the qrySomething spreadsheet. That way the qrySomething spreadsheet can be updated from Access (again using Analyze in Excel...) without trashing the ReportSpreadsheet. This necessitates the extra step of going into Access and re-creating the qrySomething spreadsheet. It works but I'd like the process to be more automatic, so that the ReportSpreadsheet always contains the latest data from Access. Can this be done?

Data flow is [AccessQuery]->[ExcelQuerySpreadsheet]->[ExcelReportSpreadsheet]

Thanks,

Shin

 

[mattdrinks]

Hi Shin,
This is just an idea it might not work how you want, I am using Excel XP

Open a new Excel workbook, then from the Data Menu select Import External Data and then New Database Query. From the Select Data Source dialog box select MS Access Database (there maybe more than one chose the one that matches your version of Access).
Next you should get a select database dialog box appear, browse to your database and select it. This should then display a query wizard with all the tables and querys from your database displayed in the left hand listbox. Select your query and click the across arrow. Click the next button on the couple of wizard pages and then finish to return the data to Excel.

At this stage an Import Data dialog box appears if you click the Properties button displayed, there should be the options to Refresh every ?? minutes and Refresh data on file open

I hope this helps
Matt

 

[Shinken]

Matt,

Your info was helpful, although I wound up taking a slightly different approach. My variation was to create the Excel worksheet from an Access query by using "Analyze with Excel" in Access. I then found an Update Link option in Excel. Essentially it's the same thing you suggested, just from the other end.

Thanks,

;o)

 

[tbellomo]

I get an odd error when i try to do this. "String data, right truncated on column nhumber 1 (TABLE_CAT)." It happens with all but one of my databases (I tested with other databases to see if it would EVER work). I don't know what I did different in the one that works, but do you guys have any ideas?

 

[mattdrinks]

I have never had this problem, although I have never been able to use a query that references a custom function written in Access VBA, I assume you query doesn't do this?

Post the full error message you get, and the SQL you are using and I'll have a look.

Also, what versions of Excel and Access are you using?

Matt

 

[Shinken]

Thellomo,

First of all, are you having the problem when you first create the Excel table, or only when you update the link?

If the problem is when you first create the table, you might try approaching it from the other end - i.e. if you're bringing it in from Excel, then you might instead try going into Access and doing "Analyze with Excel"...or the other way around.

The structure of the problem field (data type, max length, etc) would be useful to know. Also, the SQL string would be interesting to see, but if you're not getting an error running the query itself, then I would suspect it may be something different between the access field and data types in Excel.

Shin

 

[tbellomo]

just when trying to pinpoint the database that holds the tables(fields) i want, i get the message. I found another way around it though -- along the lines of shinken's advice. Thanks for your help guys.

Tim