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

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