How to access MSSQL table in a user defined function
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 |
How to access MSSQL table in a user defined function
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;Databa se=Your_Database_Name;Uid=Your_Username;Pwd=Your_P assword; 'This copies the data directly to the spreadsheet Range("A1").CopyFromRecordset rst End Sub " wrote: 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 |
All times are GMT +1. The time now is 08:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com