How to access MSSQL table in a user defined function

[tek.hong]

Hi

I would appreciate if someone could help me with some sample code that
I can use in my custom function to access a ODBC database and send a
SQL statement to retrieve data from a table.

BTW, how can I store the ADO recordset in a array so that my VBA
function can manipulate?

Your advice most appreciated. Thanks

Best Regards

 

[Guest]

Sub GetData()
Dim rst As New Recordset
Dim SQLcmd As String

Cells.Select
Cells.Delete

'Using northwind as example

SQLcmd = "Select * from Customers"
'If running a Stored Procedure SQLcmd = "Execute MyStoredProcedureName"

rst.Open Source:=SQLcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program
Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;"
'For SQL ODBC use the following Active Connection

'Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;

'This copies the data directly to the spreadsheet
Range("A1").CopyFromRecordset rst

End Sub