Issues connecting to Access data

Question

Using both Excel XP and 2007

I just started a new job and one of my first tasks is to fix a spreadsheet that is displaying data from an Access database. The workbook has three spreadsheets, each links to a table or query in an Access mdb (created in Access XP). The problem lies in the sheet that connects to a crosstab query. The other two, that connect to tables seem to work OK.

When the workbook was first created, the crosstab shows data from 2004-2008. The crosstab has changed to cover 2006-2010. But the Connection properties still showed the older columns. When the workbook was opened, it threw an error that it couldn't find the 2004 column. I changed the columns to reflect 2006-2010. I now get an ODBC error: invalid character value for cast specification in Column 6 (2009). I can't find any fix for this.

I then tried creating a new connection in Excel XP. Even though I can see the query on the list, when I try to finish creating the question I get a message that mdb.Usage_Crosstab (the name of the query) cannot be found and to check the spelling,.

When I try creating the connection using Excel 2007. I don't see any crosstab queries on the list, though other queries are there.

I have created a query that duplicates the data in the spreadsheet. However,t he spreadsheet contains titles and summing formulas. So I want to place the data from the query starting in the first data row (6). Not sure how to do that so a code sample would be appreciated.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 1

For questions related to access, post your query on the forum :

http://social.answers.microsoft.com/Forums/en-US/addbuz/threads

BLACK BARRON

---

P.S : Please post back with the status of the issue. If this post was helpful to you, please vote for my post If this post has helped you resolve your issue, please mark my post as answered.

Answer 2

Hi Ron,

Sorry, but many of my databases are connected to external proprietary data so it would be difficult to provide samples.

You said, that you connected Excel using MS Query. What I did was use Connections from Data ribbon to connect to the Access database. I select Add, then find the Access file (its an MDB). When I do that, I don't see the crosstab queries on the Select Table listing, though other queries do show. Does using MS Query involve different steps?

However, I did solve that issue by the expedient of running a Make Table query prior to opening the spreadsheet and I linked the spreadsheet to that table. So that solved the data refresh issues.

I have two remaining issues with this application and they are related. There is code in the Excel workbook, that populates values in the main sheet. Basically the code goes down 10 columns of data, filling in values from the linked tables based on part numbers. This works, but takes a long time to complete. Also, the user sometimes wants to filter the list of part numbers. There is code in the database that modifies the linked tables to filter for the selected part. However, when I open the spreadsheet, the links show all the data, even though the linked tables show only one or a few rows. I've built a query in Access that produces the correctly filtered results. That's why I would like to just dump the results of that query starting in row 6 of the spreadsheet. I assume I would have to use Office Automation to do this, but not sure of the code I need.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 3

Hi, Scott

I'm trying to help, but I can't duplicate your experience.

Here's what I did:

• Created a crosstab query in MSA2007

• Connected Excel to that query via MS Query (no problem recognizing the MSA X-tab query)

• Changed the query data in MSA

• Attempted to refreshed the query in Excel

...Received the error about not finding the column

...edited the definition in the Connection to use the new column

• Refreshed the data without incident.

Comments:

• Avoiding connections to X-tab queries is usually advisable to avoid the dynamic-field-name issues you're experiencing

• Perhaps if you copy the surviving element of the SQL into Notepad, you'll spot the syntax you need to use.

• To pull the data into the body of a report-like structure, I typically deposit the data (with headings) where I

   need it and just hide the query table headings row.

Any chance you can post shrinky-dink XL and MSA files in one of those free file-sharing sites and post the link so we can play with the files?

Please let us know how you fare.

---

Ron Coderre

Microsoft MVP - Excel (2006 - 2010)

P.S. If any post answers your question, please mark it as the Answer

(That way it won't keep showing as an open item.)