Refresh excel query through access

[gammaman]

I am trying to refresh an excel sheet with access. The excel sheet contains a connection string to a database and has SQL as the Command Text. Now below is my vba code in access, when I run it, the excel file opens and I am presented with the box to enter the connection parameters. After entering, a message pops up saying that "A pending refresh operation will be cancelled. What am I doing wrong? All I want is for the workbook to auto-refresh, then save and close.

Public Function refreshMedData()

Set xlapp = CreateObject("Excel.Application")

xlapp.Visible = True

Set xlWrkBk2 = xlapp.Workbooks.Open("M:\BSC Benefits\PMO\Reports\Med Data Extract PROD.xlsx")

xlWrkBk2.Unprotect
xlWrkBk2.UnprotectSharing
xlWrkBk2.Activate

ActiveWorkbook.RefreshAll
DoEvents

xlWrkBk2.Save

xlWrkBk2.Close True

MsgBox "Done. Med Data Refreshed"

End Function

[ranman256]

You are using Access to link to Excel to link to SQL?
If you are in access, just link directly to SQL, skip the middleman.

[gammaman]

Yes, I have the code tied to a button in access. When I click the button I want it to open and refresh the excel file and then close it. I still need to prompt for connection password. How can I do this?

[gammaman]

I am not sure what you mean by link directly to the sql? I do not want to physically store the table as the sql produces 650,000 rows.

[June7]

I am also confused. Why is Excel involved? What kind of db is Excel linking to - SQL? Why don't you have Access link to that db?

Excel has limit of 1,048,576 rows. You are at 650,000. Will you outgrow Excel? Is this sheet slow?

[gammaman]

I have the sql in excel. I was previously running the "Refresh All" from excel to run the sql and refresh the data. I have an access DB which links to the excel spreadsheet. There are secondary analytical process and reports that runs in access after the data is refreshed in excel. So I thought that it would be easier if I could just do the refresh from access so that I do not need to go to excel first.

[June7]

What do you mean by 'have the sql in excel' - are you linking to an SQL database? Is that why there is a password issue? I still don't understand why Access cannot link directly to that db.

Maybe this is relevant to your issue http://stackoverflow.com/questions/2...l-finishes-vba

[gammaman]

It is an oracle DB. The access database itself is already 1.4 GB so I do not want to make it any larger by storing the 650,000 rows in a physical table. That is why I came up with the link to excel idea. Below is screen of how I am running data in excel. If there is a way to do the same idea in access as in the excel screenshot then I would like to know. Otherwise I need to know how I can run the excel SQL through access vba using code similar to what I first posted.

[June7]

What do you mean by 'do not want to store to a physical table'? The data is already in a physical table in Oracle. Access would merely have a link to that table. Excel has a link, why not link from Access?

Did the referenced web page help?

[gammaman]

My SQL joins five tables together. I would have to link to all five tables. Another option would be to store the data physically in its own separate access database. Then just link to that.

[cyanidem]

My SQL joins five tables together. I would have to link to all five tables. Another option would be to store the data physically in its own separate access database. Then just link to that.

As you stated yourself - LINK from Access, not STORE in Access. So why not link directly from Access as two persons before already asked?

[June7]

Right, why not link to five tables? This is the easiest and most efficient approach. Eliminate Excel.