SQL query to SQL server - return result to a variable i Excel VBA

[Frank_T_L]

Hi
I am looking for a way to send a SQL qyuery to MS SQL Server and get the
result into a variable in Excel VBA.

The SQL looks like this:
SELECT Database.Project.Name
FROM Database.Project
WHERE Database.Project.ProjecrNo=1000

Then I want to use the result of the query (the project name) in some string
manipulation before I return it to the Excel woorkbook. So therefore I want
it returned to the variable strPrName.
I have been looking into some Excel VBA functions, but they all seems to
return the value to a range in the Excel workbook. Any sugestions?

 

[Norman Yuan]

You need to look for database programming topic in VBA help or (other
sources, such as the Internet). Basically, you use ADO to access the SQL
Server database to execute the SQL statement.

Here is an quick example:

1. In VBA Editor, set reerence to MS ActiveX Data Object Library 2.X (latest
is ADO 2.8);
2. Place the code snippet somewhere (a function, procedure... based on your
need):

Dim strProject As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strCon As String
strCon="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=myDB;Data Source=MySQLServer"
Set cn=NewADODB.Connection
c,Open strCon
Set rs=New ADODB.Recordset
rs.Open "SELECT...FROM...WHERE...",cn
If Not rs.Eof Then
strProject=rs!ProjectName
End If
rs.Close
cn.Close

Of course, use a Recordset to retrieve a single value may not be an
effiecient way, if Possible, I'd use ADODB.Command and SQL Server stored
proceedure for that. All I want to show here is a typical way to connect to
database and retrieve data by using some sort of data access technology, n
this case, it is ADO. Yes, ADO is the current databse access technology,
commonly used in VB and VBA. if you want to make your VBA code more
data-aware, you need to learn some database programming. You wouldn't regret
the time you spend on, if you do quite some Excel programming, on the data
processing side, especially.