Solved

EXCEL SQL Server Connection Question

Posted on 2015-02-10
20
78 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 45

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 45

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 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

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 45

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 45

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:
SimonAdept 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

744 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

12 Experts available now in Live!

Get 1:1 Help Now