Advice on Getting SQL Data 1

[gnosis13]

I am new at doing data access from Excel. I have a spreadsheet that someone types in an order number and enters an amount. I would like to use the order number to look up data on a SQL server and display it in another cell on the sheet.

We have been using an ODBC connection in Access. What is the best method for Excel? Example?

We are using MS Sequel Server 2000.

A+, N+, MCP

 

[xlbo]

Easiest is ODBC as well

You can set up a standard query that takes the value in a cell as a parameter - this can be set to run whenever the value of the cell changes...

Have a look at the menu path

Data>Get External Data

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[gnosis13]

Oops, forgot to mention; we are using Office 2003.


A+, N+, MCP

 

[xlbo]

same reply

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[fredericofonseca]

Office 97 to 2003 would all be the same for this purpose.

Depending on how you ahve your Spreadsheet work organized you may consider a function that will use ADO to retrieve a value from a predefined table.

something like

function get_name(emp_id as long) as string
define and create ADODB connection
execute sql passing emp_id as parameter
get_name = returned recordset value
end function


In the excel spreadsheet you could have formula "=get_name(A1)" in cell B1

Whenever you enter an value in A1 then the formula will be executed.

The only problem with the above is that automatic calculations will force the function to access your DB every time something changes on the spreadsheet.
There are ways around that though.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gnosis13]

I like the above approach. I already have a function in an Access database that produces the result so I should be able to just copy it.

Do you have an example of how to make the db connection? The Access database just uses the linked tables and so I don't have a good example of how to make the connection "on the fly".

I am sorry this is so basic. My programming skills have degenearted greatly over the past couple years of doing helpdesk-type support...

A+, N+, MCP

 

[fredericofonseca]

For ADO specifics you can search these forums and FAQ's and you will find plenty of information

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Take a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

This can be done quite simply, and perhaps even without any VBA code, using MS parameter Query that fires when one of the above mentions values is changed by the user.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[earthandfire]

I've been looking for a resource like that for ages. Thanks PHV - definitely deserves a star.

 

[gnosis13]

Yes, A star is born. This is a good thread for a simple problem. I will play with queries today.

A+, N+, MCP