Solved

ODBC COnnection - EXCEL

Posted on 2006-11-27
9
3,210 Views
Last Modified: 2008-05-29
I have established a System DSN connection to my SQL server (hosting my accounting software) at any rate how can i
 connect to the sql dB via excel?
0
Comment
Question by:shoris
9 Comments
 

Author Comment

by:shoris
ID: 18020179
I have MS OFfice 2000 on Windows XP
0
 
LVL 7

Expert Comment

by:DenisCooper
ID: 18020363
hopefully this will help...

 SQL.REQUEST  
Show All
Hide All
Some of the content in this topic may not be applicable to some languages.

Connects with an external data source, and runs a query from a worksheet. SQL.REQUEST then returns the result as an array without the need for macro programming. If this function is not available, you must install the Microsoft Excel ODBC add-in program (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) (XLODBC.XLA). You can install the add-in from the Microsoft Office Web site.

Syntax

SQL.REQUEST(connection_string,output_ref,driver_prompt,query_text,col_names_logical)

Connection_string    supplies information, such as the data source name, user ID, and passwords, required by the driver being used to connect to a data source and must follow the driver's format. The following table provides three example connection strings for three drivers.

Driver Connection_string
dBASE DSN=NWind;PWD=test
SQL Server DSN=MyServer;UID=dbayer; PWD=123;Database=Pubs
ORACLE DNS=My Oracle Data Source;DBQ=MYSER VER;UID=JohnS;PWD=Sesame

You must define the data source name (DSN) used in connection_string before you try to connect to it.

You can enter connection_string as an array or a string. If connection_string exceeds 250 characters, you must enter it as an array.

If SQL.REQUEST is unable to gain access to the data source using connection_string, it returns the #N/A error value.

Output_ref    is a cell reference where you want the completed connection string placed. If you enter SQL.REQUEST on a worksheet, then output_ref is ignored.

Use output_ref when you want SQL.REQUEST to return the completed connection string (you must enter SQL.REQUEST on a macro sheet in this case).

If you omit output_ref, SQL.REQUEST does not return a completed connection string.

Driver_prompt    specifies when the driver dialog box is displayed and which options are available. Use one of the numbers described in the following table. If driver_prompt is omitted, SQL.REQUEST uses 2 as the default.

Driver_prompt Description
1 Driver dialog box is always displayed.
2 Driver dialog box is displayed only if information provided by the connection string and the data source specification is not sufficient to complete the connection. All dialog box options are available.
3 Driver dialog box is displayed only if information provided by the connection string and the data source specification is not sufficient to complete the connection. Dialog box options appear dimmed and unavailable if they are not required.
4 Driver dialog box is not displayed. If the connection is not successful, it returns an error.


Query_text    is the SQL statement that you want to execute on the data source.

If SQL.REQUEST is unable to execute query_text on the specified data source, it returns the #N/A error value.

You can update a query by concatenating references into query_text. In the following example, every time $A$3 changes, SQL.REQUEST uses the new value to update the query.

"SELECT Name FROM Customers WHERE Balance > "&$A$3&"".

Microsoft Excel limits strings to a length of 255 characters. If query_text exceeds that length, enter the query in a vertical range of cells, and use the entire range as the query_text. The values of the cells are concatenated to form the complete SQL statement.

Column_names_logical    indicates whether column names are returned as the first row of the results. Set this argument to TRUE if you want the column names to be returned as the first row of the results. Use FALSE if you do not want the column names returned. If column_names_logical is omitted, SQL.REQUEST does not return column names.

Return Value

If this function completes all of its actions, it returns an array of query results or the number of rows affected by the query.
If SQL.REQUEST is unable to access the data source using connection_string, it returns the #N/A error value.
Remarks

SQL.REQUEST can be entered as an array. When you enter SQL.REQUEST as an array, it returns an array to fit that range.
If the range of cells is larger than the result set, SQL.REQUEST adds empty cells to the returned array to increase it to the necessary size.
If the result set is larger than the range entered as an array, SQL.REQUEST returns the whole array.
The arguments to SQL.REQUEST are in a different order than the arguments to the SQLRequest function in Visual Basic for Applications.

Example
SQL.REQUEST("DSN=NWind;DBQ=c:\msquery;FIL=dBASE4", c15, 2,
"Select Custmr_ID, Due_Date from Orders WHERE order_Amt>100", TRUE)
0
 

Author Comment

by:shoris
ID: 18022285
Thank you for that information.. But maybe i'm missing the point.. I added the ODBC connection in the add-in.. i created the System DSN.. but what are the steps from me to access the server to pull down data via excel.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 10

Expert Comment

by:Phadke_hemant
ID: 18025733
which authentication you are using? windows or SQL? try using username as "sa" and password as password set for sa.
0
 
LVL 7

Expert Comment

by:DenisCooper
ID: 18027147
you need to use the sql.request command in excel to pull down data from your database....you would need to program in what part of the data you want to pull down into it.....

although you have the sql connection setup, this basically just allows communication to your database, but excel won't know what data you want to pull in to it automatically, hence the reason for the SWL.REQUEST command....

The above example shows you you would do this....

hope this helps.
0
 
LVL 1

Accepted Solution

by:
kaka1228 earned 500 total points
ID: 18043026
Hi,

I'm not sure if this is what you wanted, but try going to 'Data' menu in Excel and select 'Import External Data' -> 'New Database Query'.

You should see your current connections in System DSN as one of the data sources to choose from when you run the above.

Alternatively you can specify a new source and go to 'Data' -> 'Import External Data' -> 'Import Data'

Hope this helps.

cheers.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As the title indicates, I have done this before. It chills me everytime I update the OS on my phone, (http://www.experts-exchange.com/articles/18084/Upgrading-to-Android-5-0-Lollipop.html) because one time I did this and I essentially had a bricked …
In a recent article here at Experts Exchange (http://www.experts-exchange.com/articles/18880/PaperPort-14-in-Windows-10-A-First-Look.html), I discussed my nine-month sandbox testing of the Windows 10 Technical Preview, specifically with respect to r…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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