Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS SQL Connection and Retreival
Hey all, I have recently started dabbling in VBA and have been asked to connec an Excel Workbook to a MSSQL Server. I have not done this before an would like some help please! Things i do know: 1. Server Name 2. Username and Password will have to be the Windows Logon details 3. The table i want to access Things i don't know: 1. How to connect to the database (DSN-less) 2. How to run a query and use the results ( 5 values) Basically all i want to do is open a connection to the <SERVER an retrieve details based on a selection on a field entered on a UserForm This document has plans in being distributed so I would prefer to use non-DSN connection! Thanks for your help -- edfollet ----------------------------------------------------------------------- edfollett's Profile: http://www.excelforum.com/member.php...fo&userid=3622 View this thread: http://www.excelforum.com/showthread.php?threadid=56015 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS SQL Connection and Retreival
hey guys, i found the solution by playing around with some stuff i found on Microsoft's site: Code: -------------------- Public Sub return_data() ' Declare the QueryTable object Dim qt As QueryTable ' Set up the SQL Statement sqlstring = "select * from test" ' Set up the connection string, reference an ODBC connection ' There are several ways to do this ' Leave the name and password blank for NT authentication connstring = _ "ODBC;Driver={SQL Server};Server=(local);UID=;PWD=;Database=test" ' Now implement the connection, run the query, and add ' the results to the spreadsheet starting at row A1 With ActiveSheet.QueryTables.Add(Connection:=connstring , Destination:=Range("A1"), Sql:=sqlstring) .Refresh End With End Sub -------------------- works a treat! ed -- edfollett ------------------------------------------------------------------------ edfollett's Profile: http://www.excelforum.com/member.php...o&userid=36227 View this thread: http://www.excelforum.com/showthread...hreadid=560157 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
no DDE connection?? | Excel Discussion (Misc queries) | |||
ADO Connection | Excel Programming | |||
mass data retreival from excel book in different folders | Excel Programming | |||
Auto Refreshing: data retreival from webpage | Excel Programming |