Solved

EXCEL SQL Server Connection Question

Posted on 2015-02-10
20
79 Views
Last Modified: 2016-02-11
Hi,

I want to start with a blank Excel. Then I want to connect to a SQL Server such that the connection property
will look at this (see attached image file). How to do that? How to get the settings as shown in the image file?

Thanks.

JenniferSQL-Conn.png
0
Comment
Question by:jfz2004
20 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40601730
Check out this EE article and scroll down about 40% of the way to view instructions with screen shots.
0
 

Author Comment

by:jfz2004
ID: 40603277
Thank you so much for the article. I learned somthign from it and tested it on a blank Excel.
But what I wanted to achieve are:
In the connection properties dialog box,
1. there is nothing in the "connection file";
2. the Proder = SQL Server, not SQLOLEDB.

How to get these two things? The options in Excel's "From Other Sources" confuses me greatly.

Jennifer
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40605378
If you provide a connection string then you won't need to have a connection file. Connection file should be a .UDL file, I guess.
The provider is what you have installed for SQL Server connection. What do you have? MSSQL Native Client (SNAC)? .NET (ADO)? ODBC`? OLEDB?
0
 

Author Comment

by:jfz2004
ID: 40607770
Thanks. I have SQL Server and Excel.
0
 

Author Comment

by:jfz2004
ID: 40607773
Excel but no VBA code.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40607782
I mean drivers not applications. Which drivers do you have installed?
0
 

Author Comment

by:jfz2004
ID: 40607785
Drivers? How to check what drivers installed? I am new to this.
Thanks a lot.
Jennifer
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40607798
If you check in you connection string the first word is DRIVER and you have SQL Server written on it. You can verify the installed drivers in Windows menu Start / Control Panel / Administrative Tools / Data Sources (ODBC) / Drivers tab.
You should see I list now. What you have listed for SQL Server?
0
 

Author Comment

by:jfz2004
ID: 40607858
In the "Connection Properties" dialogue box, there is a Connection string box. In it,
I have "Provider=SQLOLEDB.1;;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=..."

Is my driver SQLOLEDB, then?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40607867
Yes, in that case you are using SQLOLEDB.
0
 

Author Comment

by:jfz2004
ID: 40607884
And on the "Data" ribbon, there are 5 choices on the list of "From Other Sources". I don't know which one I should choose:
1. From SQL Server
2. ...
3. ...
4. From Data Connection Wizard
5. From Microsoft Query

Thanks for any help.
0
 

Author Comment

by:jfz2004
ID: 40607888
My query will pull data from a large table in a SQL Server DB and there is a where clause as "systemdate = ?" and the "?" refers to a cell. I don't know which of the above I should use. Thanks.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40607904
What are your knowledge in SQL Server area?
And also do you know well the table that you want to connect to?
0
 

Author Comment

by:jfz2004
ID: 40607916
I have a little bit of SQL Server knowledge, I know select and where clause.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40608018
If you know T-SQL then it's good. Chose the first option (1. From SQL Server).
The '?' means that the user will be asked to provide a value. It this case a date to be used in the WHERE clause.
0
 

Author Comment

by:jfz2004
ID: 40608215
I just tried your suggestion. Thanks.

But it asks for a connection fil and I don't want to use connection file.
I jsut want to use a Sql Query freely style.
0
 

Author Comment

by:jfz2004
ID: 40608277
I have been reading and seearching on the internet for a few days and seems like there is no sample for this kind of operation. I know this can be done, but non of the tutorials teaches it.
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40609133
From your previous question, please see the screenshots I posted

Now that you have your  DSN, the steps that you said you followed in this post are correct:
1. In a blank Excel, I click "From Other Sources";
2. Then choose "From Microsoft Query"
3. In "Choose Data Source" box, I choose the right database;
4. Then choose the table and columns via MS Query.
5. And it worked. I see the EDIT button shown.

When you first create a query it may show that it is using a connection file, but as soon as you edit the properties of the query and OK it, you'll get a prompt to say that it will no longer match the connection file (as shown below).
Excel-Connection-ODC-to-ConnectionStringIf you then go into the properties of the query again, you'll see that it now shows 'Database query' with your connection string.
If you then edit the command text to include a WHERE clause
e.g. SELECT * from myTable where SystemDate=?
and OK the Connection Properties dialog again, it will prompt you for the parameters, including the options I showed the screenshots I referred to above (getting value from a cell and updating automatically when cell value changes).

To be clear, you can get to the connection manager looking the way you want it to be at least two routes:
1. Other Sources/Microsoft Query
2. Other Sources/From Data Connection Wizard/ODBC DSN
0
 

Author Comment

by:jfz2004
ID: 40614071
Thank you so much. I think this clears it. You rock!

Jennifer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now