Excel/Access Reports - Connection Type

[subhanak]

I created excel reports based on a dataset that exists in MS Access database. The connection propriety is “Get external data---- > from Access database”.
The challenge with this connection type is when the excel reports are open, the Access DB gets locked. To get around this challenge, I would like to change the connection type from existing to “Get external data---- > From Microsoft Query”.
The reports that I built in excel consist of many calculated fields + design, and I do not want to rebuilt them from scratch to change the connection type.

Question:

Is there a way to change the connection type of my existing report from existing to “get external data---- > From Microsoft Query”. ?
Your assistance is appreciated.

Thanks,
Sayed

 

[ranman256]

you shouldnt rebuild the entire db. I would keep the existing db to excel, but not make it a live connection.
It should do an 'update', where excel connects, then pulls data, then closes the connection.
This way it wont keep things locked.

 

[subhanak]

The "Get external data---- > From Microsoft Query” will do what you are suggesting.

If you do not mind, what do you mean by rebuild the entire DP? plz.

Thanks,
Sayed

 

[subhanak]

Hi,
I think I resolved this challenge by altering the existing connection and removing "Enable background refresh"

It seems fine now.

Thanks,
Sayed