What's the best approach: Need multiple queries displayed on Excel on the same page

[bobdole22]

I'm brand new at my job and I'm trying to get hired on here, so it makes me extra nervous if I can't figure this out.

My boss has asked me to automate his reports he gets through a combination of access and excel. I say, "Sure, no problem." I get started on it (being fairly new to VBA and very new to Access) and I am starting quick. Right of the bat, learned a ton of important functions. He needs me to have a button to choose a db to show. Easy, just created a button and saved it to a variable.

Next, he needs me to run a bunch of his Microsoft Access Macros through Excel. Also, no problem. Just told it to open the Access File with a ".OpenCurrentDatabase" run the macros with ".DoCmd.RunMacro" and close it with ".CloseCurrentDatabase & .Quit."

Now he needs me to display some of his queries on Excel. I'm thinking sure, no problem. I tell it to connect to the database with some "ADODB.Connection" because it's a accdb file. Test out some SQL statements with:

Set cn = New ADODB.Connection
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open MyConn
            Set rs = .Execute(sSQL)
         End With

The simple statements work fine and show up nice and neatly. Although, I've hit a snag and I need some outside help. The SQL statements he has in his queries are insanely long and complex. They won't show up through this process. After spending over an hour trying to format the length in a string (because VB has a line limit, so you have to do a lot of &'s), I finally get it to run without a syntax error. But now I get an error saying It's returning 0 queries. I know for a fact it's not returning 0 queries, we run it all the time. It's just that I don't think excel can handle a statement 20 lines long without syntax errors.

Let me know if there is a way to do this please

[bobdole22]

Next I try to display the queries through ".DoCmd.RunMacro" back up top. I make a macro that exports to excel. The problem is, after tons of research. That I need to display 14 different queries and that way will only display 1 per Worksheet or tab. I need them all on the same page. I have no clue what to do now.


He has his Excel page originally linked to Access through the old-fashioned way. Just clicking the "link-button." So it works fine for displaying at first glance. But he says it needs to be able to switch computers and not get a filepath error. Now, that is impossible to my knowledge this way. Is their any way to code and change the file path of an already linked Excel sheet? Or even to re-link one through VBA only? From my research I haven't found anything, that's why I'm asking y'all. Any help would be great. My boss is waiting impatiently and I have already wasted the week finding out those errors.

Cross Thread: http://www.excelfox.com/forum/f2/wha...1414/#post6650

[SamT]

Bob,

Have you decided to follow the course of action in Can Excel run this big SQL? or is this thread a separate and ongoing question?

If the first, please mark this thread solved.

If the second, just consider this post as a friendly bump to the top of the list.

[Jan Karel Pieterse]

An Excel sheet linked to *what* precisely?
A querytable connection you mean?

[bobdole22]

An Excel sheet linked to *what* precisely?
A querytable connection you mean?

An excel sheet linked to an access table. As in when you manually click the link button. There is not code to set that link, only code for connecting ADO correct?

[Jan Karel Pieterse]

You may benefit from moving the complex queries to Access and then calling the query itself from Excel with SQL like this: SELECT * FROM MyComplexQuery
Note though, than Access can handle VBA functions in queries, which Excel (MSQuery) cannot.

[bobdole22]

I didn't know that was possible! If it is, that should fix everything.





Although, after trying SELECT * FROM qry_SAP_Rates"

I get this error: An action query cannot be used as a row source.




Note: The queries are already on Access, I'm trying to get them to run and display data in Excel.

[Jan Karel Pieterse]

What is the SQL of the query itself?

[bobdole22]

"SELECT Sum(Data.PBO) As SumOfPBO
Sum(Data.AccruedInterest) As SumOfAccruedInterest
Sum(IIf([Status]='Claims' Or [Status]='Forbearance'
[AccruedInterest],IIf([Status]='Repayment',0
IIf([SubsidyIndicator]='N',[AccruedInterest],0)))) As ITB
Sum(IIf([ABI]=0,0,[pbo]/[ABI])) As Borr, Count(Data.BorrowerUniqueID) As Loans"
Sum([PBO]*[RemainingRepaymentTerm])/Sum([PBO]) As RepayTerm
Sum([pbo]*[InterestRate])/Sum([PBO]) As IntRate FROM Data LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID HAVING (((Data.PBO)<>0));"