External Data Blank??? (1 Viewer)

[cfp76]

First, I am using Office 2010.

I have worked in a series of queries that can be used in Excel for a report.

I go through the steps to connect to an external datasource and set it to deliver as a table in cell A1.

When it "builds" the connection - there is no data. All I get is the headers from the query I'm trying to link to :/

Any suggestions on why the data (that I can see when I run the query in Access 2010) would not be showing up ?

 

[boblarson]

Can you elaborate on how you went about creating what you have? Are you trying to export to Excel or are you trying to pull data from Acces into Excel using Excel? Are you selecting ACCESS as the connection type? Also, does your query use any functions from Access or have parameters? If so, it can't.

 

[cfp76]

You answered my question.. I AM using functions in the query... so - hmmmmm I tried it again by making a query off the query that had functions and it still isn't working.

I am trying to import data from Access INTO Excel

I have tried 2 methods:

Data>>From Access>>pick the database/query>>put in cell A1.
This method returns the headers but zero data.

Data>>From Other (pick MS Query)>>pick Access database>>find db in folder tree>>select query>>nothing for filtering criteria>>some sort criteria items
This method returns an error "Too Few Parameters"

 

[boblarson]

The query can't use any Access functions nor can you have parameters. So, what is it you are really trying to do? Why the table in Excel? There are ways to code around this but just need to have more information as to what your processes are and how you expect to use this. What functions are you using in the Access query? What parameters are you needing to limit by?

 

[cfp76]

I don't need any parameters. And the select query I am currently using has no functions. I have a series of queries - the final query uses no functions but it is pulling from a query that uses functions (INSTR, MID, etc - standard functions in my opinion).

The steps are a report is pulled from a bank website and saved to a specified location (always saving over). Access links the data in (so it automatically changes on every save over) and has a series of queries to parce out the needed data for the final report. The final report is in Excel.

I am doing the data manipulation in Access and want to spit it out to Excel because the users are only familiar with Excel and do not want to learn a new tool. There is a good chance some of the final users won't even have Access on their machines.

 

[boblarson]

You aren't understanding. If ANY of the queries have ANY Access functions in them, you can't link to then in Excel. There can be parameters but you can't use parameter prompts and you can't use a form for input in the referenced query or any others it may be based on. The values would need to be hard coded.

If you need the Mid or Instr functions then you would need code in Excel to run the query in Access and return the results to the range via code, but the user would need Access installed on their machine.

 

[cfp76]

UGH - you are right.. I was thinking that the piece in Access that I was using was the only piece I couldn't use.

BLAH - back to the drawing board.
Thanks.

 

[pwbrown]

Could you not just create a table using the query results and use the table instead of the query to import to excel?

 

[cfp76]

I don't see why not but I don't know since I'd have to have the queries run and create the table automatically whenever the excel report is opened. I have completely changed my attack at this point :/ trying to do it fully in Excel but am not having much luck.

 

[boblarson]

Could you not just create a table using the query results and use the table instead of the query to import to excel?

Because of the functions and all, it would be necessary to use DAO to do this. You can use that to run the queries to make a table which you can then have linked and refresh. But the code would be from Excel since you state that the user might not have Access.

Some quick Air Code is

Function RunMyAccessQuery(strFileAndPath As String, strQuery As String)
Dim db As DAO.Database
  
  Set db = OpenDatabase(strFileAndPath)
  
  db.QueryDefs(strQuery).Execute
  
  db.Close
  
  Set db = Nothing
End Function

You would need to set a reference (at TOOLS > REFERENCES) to Microsoft DAO 3.x (where x can be 51 or 6) or you might be able to just use

Dim db As Object