Best practice to connect to SQL Server tables from Excel 2007 1

[Pack10]

Not sure where this fits VBA, SQL Server, or Excel... if I were to pull
data from SQL Server into Excel via VBA how would I do that...would I put a command button on the sheet and click it or have a macro run the connection code.....what is the best practice?

 

[PHV]

Why not simply use MSQuery ?
Data -> External data

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

Hi PHV

Thanks for all your help over the years. I know I can use MSQuery
but I was trying to understand how to do this in case I need to automate a process
and load table info into excel.

 

[PHV]

in case I need to automate a process
You may use the CopyFromRecordset method in VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

or using MS Query, Refresh on Open is an option.

Is that "automatic" enough?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pack10]

OK, so nobody does this via code?

 

[SkipVought]

Once you have a querytable object on your sheet, it is there, until you delete it. The query can be refreshed any time either manually or programmatically or via a built-in option as previously stated.

Yes, code can be used for ADO or even MS Query parameter SQL, but in many cases it is not necessary. I generally use ADO when I am coding a recursive bill-of-material like application where there are multiple levels or related (parent-child) data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pack10]

OK, thanks for the information. If one was going to write code to pull data in from SQL Server -- how do you invoke the code. Do you put the code in a click event, or make it a macro? I am simply trying to understand how the process would work from an Excel prospective.

 

[SkipVought]

Kind of depends on what you're doing. It certainly could be a control to kick off whatever code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Personally I always use ADO from vba

Generally I write a piece of code to:

Create a connection to the database
Execute a query
Return results to a recordset object
either dump recordset to sheet or manipulate 1st

Once the code is written, how you invoke it is really up to you - button click, workbook_open event, sheet_activate event...really depends on what the data you are getting is meant for:

Dim rs as ADODB.recordset 
Dim strCon as ADODB.connection 
Dim strSQL as string 
set strcon = new ADODB.connection 
strcon.open "ODBC;DSN=ODBC Driver Name" 'or an OLEDB string: "Driver={SQL Server};Server=ServerName;User ID=abc;Password=abc123;Database=Customer_Info"

strSQL = "SQL String here" 
set rs = strcon.execute(strSQL) 
'work with rs - fields collection for headers, copyfromrecordset method to dump data to worksheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question