My Excel SQL connection won't refresh after I close and reopen file

[Cristian888]

So I'm connecting to our database via the "From Microsoft Query" option in Excel

I run my query and it works great, I can even refresh and new records will be added accurately

Problem is, after I close and reopen the file, I'm no longer able to refresh. The refresh button is still there and I'm able to push it, but it does nothing. I've tried right clicking on my table, hitting Table > Edit Query and that button does nothing also. I can see my query under Connection Properties > Definition tab and it's correct. The connection string and command text is accurate, but it's not refreshing. I'm running Crystal Reports simultaneously and I can see the new data when I refresh in Crystal, but not in Excel. Basically, I have to start the query from scratch and only then will the new up to date info show up. It's strange because if I don't close the file, I'm able to refresh perfectly, just not after I reopen it

Anyone have a clue as to what I'm doing wrong? Thanks in advance

[dflak]

I am fairly certain that I have a workaround for you "in pocket," but let's see if we can get to the real root cause.

Let's start with MS-Query. How are you building the query in Excel?

Then let's look at Crystal Reports. Are you connecting directly to a database or are you running off a query or some other view? Is the view name always the same? Are the field header names always the same (I bet they are since you'd get an error rather than nothing).

Download both the connection string and the command string for a newly-created query and one that happens immediately after an open and close and the same when you rebuild another new one. So build a query and check that it works. Capture the two strings. Close the workbook. Open the workbook. According to what you are saying, the refresh doesn't happen. Record these two strings. Then while in the workbook, delete and rebuild the query and record the resulting strings.

If you put the strings in Excel Cells you can compare them to see if they are equal. I find this to be more reliable than what my eyeballs reveal.

My suspicion is with the connection string to crystal reports. It may be "broadcasting" something different on each session. I haven't got a clue why it would do this and I am sort of hoping that it isn't.

Let us know if what the tests reveal and then maybe we can figure out the next step.

[Cristian888]

In Crystal, I'm connecting directly to our SQL Server database, every time I refresh I just enter the database password

1.jpg

Those are the same credentials I'm working with when I establish the connection in Excel

Let's start with MS-Query. How are you building the query in Excel?
Data > From Other Sources > From Microsoft Query > I gave the data source a name > Selected SQL Server under "Select a driver for the type of database you want to access" > Connect > I selected the appropriate Server > Logged in with the database username and password

Here's a fresh connection string and command string for a newly created query that refreshes correctly:

Connection String:

DRIVER=SQL Server;SERVER=SERVER\E2;UID=sa;;APP=Microsoft Office 2013;WSID=CRISTIAN-PC;DATABASE=Euroline

Command Text:

SELECT DISTINCT TimeTicketDet.TicketDate, OrderDet.PartNo, TimeTicketDet.WorkCntr, OrderDet.UnitPrice, TimeTicketDet.PiecesFinished, (OrderDet.UnitPrice * TimeTicketDet.PiecesFinished)
FROM Euroline.dbo.OrderDet INNER JOIN Euroline.dbo.TimeTicketDet ON euroline.dbo.orderdet.jobno = euroline.dbo.timeticketdet.jobno
WHERE (TimeTicketDet.TicketDate=cast(getdate() as date)) AND TimeTicketDet.PiecesFinished IN (0,1)
ORDER BY TimeTicketDet.WorkCntr

After closing and reopening workbook:

Connection String:

DRIVER=SQL Server;SERVER=SERVER\E2;UID=sa;APP=Microsoft Office 2013;WSID=CRISTIAN-PC;DATABASE=Euroline

Command Text:

SELECT DISTINCT TimeTicketDet.TicketDate, OrderDet.PartNo, TimeTicketDet.WorkCntr, OrderDet.UnitPrice, TimeTicketDet.PiecesFinished, (OrderDet.UnitPrice * TimeTicketDet.PiecesFinished)
FROM Euroline.dbo.OrderDet INNER JOIN Euroline.dbo.TimeTicketDet ON euroline.dbo.orderdet.jobno = euroline.dbo.timeticketdet.jobno
WHERE (TimeTicketDet.TicketDate=cast(getdate() as date)) AND TimeTicketDet.PiecesFinished IN (0,1)
ORDER BY TimeTicketDet.WorkCntr


I'm incapable of creating a new connection in the reopened workbook, the From Other Sources > From Microsoft Query button does nothing. I can select it, but just like the refresh button, nothing happens

So the Command Text is the same for both, the Connection String is different, there's an ommited ; in the reopened file. I tried typing it in and hit Okay, but the Okay button doesn't close the window. I can hit the button, but again, it just acts like a dummy button. Only when I delete the second ; will it allow me to hit Okay

[Cristian888]

I may have a bit more information

So I downloaded Power Query and low and behold, the same thing happens

Power Query is very nice

However, I'm suffering the same issue, able to refresh while file is open and suddenly, refresh button does nothing upon reopening it. I am able to right-click on my table and hit Table > Edit Query. When I edit the query and hit okay, nothing changes, when I reopen the Edit Query window again, I see that my changes were not saved, reverting back to whatever the query was when I last closed the workbook

That's under Table Tools > Design Tab

Under Table Tools > Query, I am able to hit refresh (not sure if this refresh is different that the refresh under Table Tools > Design)

Anyway, when I hit refresh here, I get these messages:

Enable Connections
External data sources are used to import data into Excel, but connections to external data sources can be used by queries to access confidential information available to other users, or to perform other harmful actions. If you trust the source of this file, click Enable

So I click enable and I get this message:

Enable Connections
External data connections couldn't be enabled. If this workbook is embedded in another Office document, you need to manually enable external data connections when opening this workbook from the other Office documents.

I hit close, and that's it, nothing happens

Interestingly, if I try to run a new power query in the reopened workbook, I get those exact same messages regarding enabling connection

[dflak]

OK, you're right. It is weird that you can't even modify the connection or do anything else with MS-Query -- that it should be "deactivated" too.

Before I give you what I think will be a workaround, I’d like to try something else first. I am seeing things like cast(getdate() as date). MS-Query doesn’t do well with functions it does not know about. I am not sure it understands cast or getdate.

Are you able to represent this query graphically?

Try this: Open the connection but instead of selecting a table, cancel out that screen. Then click on the SQL button on MS-Query. Have your query written out in notepad or some other text editor. Where it says:

TimeTicketDet.TicketDate=cast(getdate() as date))

Try TimeTicketDet.TicketDate=[MyDate]

And copy and paste the SQL statement to the SQL box.

If the query can be displayed graphically, this will appear as a parameter that can be prompted.

When you first run the query, you will get a box prompting you to provide the date.

When the data is returned, right click in the data. One of the options is Parameters. One of its options is to read the data from a cell. So you can have a formula in this cell like =Today() or =Today()-1. You can even do things like =TEXT(Today(),”dd-mmm-yyyy”) if that is the format the database is expecting for a date.

From what I can see, the query should be able to be displayed graphically. If not, we are still not hopelessly lost in the woods. There is a short VB code segment I can give you that reads the SQL from an Excel table and rewrites the SQL at runtime!

[Cristian888]

Are you able to represent this query graphically?

No, but the data displayed is correct and it refreshes new data correctly as well before closing workbook

Try this: Open the connection but instead of selecting a table, cancel out that screen. Then click on the SQL button on MS-Query. Have your query written out in notepad or some other text editor. Where it says:

TimeTicketDet.TicketDate=cast(getdate() as date))

Try TimeTicketDet.TicketDate=[MyDate]

And copy and paste the SQL statement to the SQL box.

If the query can be displayed graphically, this will appear as a parameter that can be prompted.

When you first run the query, you will get a box prompting you to provide the date.

The MyDate function doesn't work, I just get an error saying "Invalid column name 'mydate'. Statements(s) could not be prepared. This is after the message saying it can't be displayed graphically either

I ran a simple query for

SELECT * FROM orders

And that one can be displayed graphically, however, same issue after closing and reopening workbook, refresh button does nothing, Edit Query button does nothing

[Cristian888]

Okay, I figured it out

The error message I was getting on the Power Query really helped

Basically, by default, all external data connections are blocked in Excel 2013

I just had to go in there and change the settings

It's weird that an external data connection was allowed in the first place given this setting, but it's definitely the culprit, feel like an idiot now

Here's the article regarding external data connections

https://support.office.com/en-us/art...6-575b0847a795

[dflak]

D'oh! That's what happens when you overlook the obvious. However, I am surprised that you did not get a warning message when you opened the file.

Thanks for reporting back. Good luck with the project and if you ever need help with MS-Query, I do have a "bag of tricks."