Excel data connection refreshes but data is not updated 1

[VBAPrincess]

I've got an Excel 2007 workbook that has four data connections. Each of these is pulling data from an Access database. I have charts that are using the data. I recently modified one of the queries in the Access database, but now when I refresh the data, the spreadsheet does not reflect the new values.

I have a tab called Data which has the return values from each connection. I have verified that the connection definition is pulling the correct query from the correct Access database. When I run the query in Access, I get the corrected values that I want to see in Excel. I have refreshed the connection, I have deleted and recreated the connection, and still I am getting the old values.

I've attached screenshots of the data, the connection properties, and the Access results that should be appearing in the worksheet.

HELP! THANKS!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[SkipVought]

hi,

RIGHT-CLICK in each of the resultsets, and select REFRESH.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VBAPrincess]

Skip,

I've tried that several times, and even made sure that the properties option "Overwrite existing cells with new data, clear unused cells" option was selected, even though the number of rows shouldn't change. Still no update to the data.

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[SkipVought]

Try this...

Right-Click > Table > Edit Query

and what happens

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VBAPrincess]

I got the Edit OLE DB Query dialog and could see the connection properties. It all looked ok. I clicked OK and then it said in the status bar that it was connecting to the data source, but again it did not update the values. I opened the edit query dialog again and changed the command type to SQL and pasted the SQL statement from the query. I ran again and it took longer but still nothing updated. Maybe the file is corrupt?

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[xlbo]

if this is a querytable (sounds like it is) then try setting the preservecolumninfo property to false

for each qt in thisworkbook.querytables

qt.PreserveColumnInfo = false

next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VBAPrincess]

Geoff,
I tried your code, but had to change to Activesheet.QueryTables to get it to run. However, I still got the old bad data after the refresh. Suggestions? To get the report ready to run for June I've gone ahead and just deleted the data in the spreadsheet and did a manual copy/paste from Access. That at least gives me a few weeks to figure this out until July needs to be run.

Thanks!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[xlbo]

Can you provide the FULL connection string that is being used (not all is shown in your screenshot)

Please can you also provide some infoon how the query has changed? is it purely different data but in the same structure or are there now more / less / different fields returned by the query?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VBAPrincess]

Geoff,

Sorry! On Friday I deleted the query table from the worksheet, created the table manually, ran the reports needed for a meeting this morning, and left it alone til today. I've now deleted my manually created table of data for the chart and added the data connection back in. I'm still getting old numbers. I had hoped with the elapsed time some gremlins would disappear. Guess not.

To answer your questions about the query, nothing other than the criteria in the subquery changed. I was pulling data based on one field (Trans Status = cancelled/non-renew) but added the criteria to another field (Line Status). This criteria change modified the summary values considerably (Apr went from $18K to $35K) but I'm not seeing those new values in the spreadsheet. I can see them in Access.

The query is as follows:

SELECT P.Branch, DateValue(Right(P.[acctmonth],2) & "/1/" & Left(P.[AcctMonth],4)) AS MoYr, P.Amount AS Planned, Sum(Q.ActualBiz) AS Actual
FROM

(SELECT Q.[Acct Month], Producers.Branch, IIf([Agency Net]<0,[Agency Net],0) AS ActualBiz FROM [2012 Production] AS Q INNER JOIN Producers ON Q.Producer=Producers.Producer WHERE (Q.[Trans Status]="cancelled" Or Q.[Trans Status] Like "non-re*") Or (Q.[Line Status]="cancelled" Or Q.[Line Status] Like "non-re*")) AS Q​

INNER JOIN [2012 Budget Lost Biz] AS P ON (Q.[Acct Month]=P.AcctMonth) AND (Q.Branch=P.Branch)
GROUP BY P.Branch, DateValue(Right(P.[acctmonth],2) & "/1/" & Left(P.[AcctMonth],4)), P.Amount;
​

The full connection string in Excel is as follows:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=R:\Chris Mangum\ProductionDatabase_2012.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False​

Last week when this started, I had made changes to two other query tables on other worksheets and those I modified considerably. Both had the addition of 2-5 columns, and one had a couple of calculated columns. Those all worked beautifully and then I went to update this last one and it tanked.

Thanks for any more help you can provide!



Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[SkipVought]

and added the data connection back in

Hmmmmmmmm???

The process that I use to query an external data source, does not simply have a data source!

Please explain the STEPS in your process.

The process that I am familiar with and use just about every day is...
[tt]
Data > Get External Data > From Access .... then drill down to the db
[/tt]
You establish the data source in a step that leads to specifying the TABLE(s) and FIELDS(s) joins and criteria. Then you File > Return data to Excel.

After establishing the query table, a simple refresh, er uh, REFRESHES the resultset.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VBAPrincess]

Really? ... Well that is exactly what I've been doing OVER and OVER but the data is NOT refreshing, hence my post in this forum. By "adding the data connection back in" I meant that I started over from scratch. I deleted the data table in Excel which had a connection to Access. I answered YES to the dialog that said "Do you want to delete the query in addition to the range?" Boom! No more connection and no more data. Then I repeated my initial process of Data > Get External Data > From Access > select the database > select the query > put data into same place it was before and the data that appears is NOT the result set I get when I run the same query in Access. Click Refresh multiple times -- still same set of OLD data. I've even done the same process on ANOTHER worksheet and still get bad data. I know I'm selecting the right database and the right query.

Per your previous suggestion, I (yet again) did a right-click > Table > Edit Query and got a dialog with the following information:
Connection:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=R:\Chris Mangum\ProductionDatabase_2012.mdb;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False
Command Type:
Table
Command Text:
qPlannedvActualLostBiz

Data Source - correct!
The query name - qPlannedvActualLostBiz - correct!

So why doesn't refresh work for this query? I've got three other data tables in this workbook and they are refreshing.

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[SkipVought]

Are the other queries to the same db or some other?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Given all that you have said (especially that you have deleted and re-created the query) I can think of nly 3 scenarios to cause this:

1: you are pointing to a different version of the database from excel compared to when you run live in access
2: the query name has been changed and you are ponting to the original query still
3: (a long shot) the querydef object in the access database has somehow not been updated - the updated query has not created a permanent querydef object and the excel connection is pointing to the querydef rather than the query itself

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VBAPrincess]

Skip: yes, all are coming from the same database.

Geoff: I was hoping for #3, even though you said it was a long shot because I'm quite certain I'm pointing to the right database (there's only one I can point to with this information) and the name of the query is correct. I've even gone so far as to delete the query in the database and recreate it with the exact name that Excel is referencing (I copied the name from properties before going to the database and 'pasting' the name to the new query). I ran some code to check the querydef and the QD exists and it has the SQL that I would expect for this query.

I really feel like this is one of those situations where I'll have to recreate the file OR just update the data manually each month. I already have to import data into the db twice a month so I can just run the query, get the updated data for the current (or previous) month and go to the spreadsheet to update the data myself. Not ideal, but actually preferable to recreating the workbook.

Thanks guys! If you have other ideas, let me know!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[SkipVought]

I would recreate the ENTIRE QUERY, SQL and all. I would NOT use an already created querydef

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ColVBA]

Hi, I had the same problem, and removed the 'Like' criteria. Creating new column e.g. Expr1: = Left([Line Status],6)
and using = "non-re" in the criteria line was the workaround.
Col.