Change workbook connection from OLEDB to Database Query?

**walterbyrd** · 01-04-2013, 12:37 PM

Using: MS-Excel 2007, and MS-SQL Server 2008

From Excel: Data -> Connections -> [Choose the connection] -> Properties -> Definition

The connection type is OLE DB Query, but it should be Database Query. I have no idea how this could have been changed, but I need to change it back, and I cannot figure out how.

Any help appreciated, thanks in advance.

**PingPing** · 01-07-2013, 04:31 AM

Under "Connection type" there is an entry for 'Connection string'. What does this entry have in it?

**walterbyrd** · 01-07-2013, 01:58 PM

Originally Posted by PingPing

Under "Connection type" there is an entry for 'Connection string'. What does this entry have in it?

Thank you for responding.

For security reasons, I cannot show everything. But it looks like this.

Please Login or Register  to view this content.

And this is completely wrong. It shold look more like this (again, some fields removed for security reasons):

Please Login or Register  to view this content.

Whenever I change the string to what it is supposed to be, the system changes it back.

**PingPing** · 01-08-2013, 08:53 AM

The system appears to be forcing you to use OLEDB to connect to the MS SQL Server database. The Connection String that you say it should look like is an ODBC connection string.

OLEDB and ODBC are two different Database APIs, ie. they are two different ways of connecting to the same Database and should give you the same result.

Does your spreadsheet still work with the OLEDB connection being forced on you?

**walterbyrd** · 01-08-2013, 01:02 PM

Originally Posted by PingPing

The system appears to be forcing you to use OLEDB to connect to the MS SQL Server database. The Connection String that you say it should look like is an ODBC connection string.

OLEDB and ODBC are two different Database APIs, ie. they are two different ways of connecting to the same Database and should give you the same result.

Does your spreadsheet still work with the OLEDB connection being forced on you?

No the spreadsheet does not work with the OLEDB connection. That is the whole problem. As I indicated in the subject line: I need to know how to change a workbook connection from OLEDB to Database Query.

Thanks again for your reply.

**PingPing** · 01-10-2013, 04:34 AM

It looks like the .dqy file has been either deleted or was never saved in the directory:

WinXP - c:\documents and settings\<username>\application data\microsoft\queries
Win7 - c:\users\<username>\AppData\roaming\microsoft\queries

You will need either to:
(a) locate the lost .dqy file;
(b) recreate the .dqy file employing a text editor like Notepad;, or
(c) go through the whole 'Get External Data' process again in Excel in order for the .dqy file to be recreated.

**walterbyrd** · 01-10-2013, 04:01 PM

It is my understanding that I can connect to an ms-sql server with a connection string in the workbook - not a file.

There are many pivot tables in the workbook. There are connect with workbook connection strings.

**walterbyrd** · 01-10-2013, 04:18 PM

I am using Windows 7. There is nothing in this directory.

Win7 - c:\users\<username>\AppData\roaming\microsoft\queries

However, I have several pivot tables in my workbook which are working. I think the connection strings are in the workbook, not in external files.

**dummy777** · 09-25-2019, 09:42 AM

I have the same problem. Any updates?

I found out what should we do as an alternative. We need to create new connections thru Data, Existing connections, Browse for more and search for your database. This will create an connection, instead of query in excel.

**FDibbins** · 09-25-2019, 08:53 PM

Originally Posted by dummy777

I have the same problem. Any updates?

I found out what should we do as an alternative. We need to create new connections thru Data, Existing connections, Browse for more and search for your database. This will create an connection, instead of query in excel.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread