Using DSN-Less Connectino from Excel to SQL

[Panda]

Hi All,

Have trawled the posts and I guess I'm missing something. This is the
code I'm running...

Dim cnn As ADODB.Connection
Dim strConn As String
Set cnn = New ADODB.Connection
strConn = "Driver={SQL
Server};Server=POServer;Database=Live_Integra;Uid=xxx;Pwd=xxxxx;"
With cnn
.Open strConn
.CursorLocation = adUseClient
End With

'Get the total Quantity sold in that period
strSQL = "SELECT sum(c.QTY)"
strSQL = strSQL & " FROM acocmp1.currsale c WHERE (c.STKCODE Not In
('i98','SUBS'))"
strSQL = strSQL & " and c.trandate between '" & VarStartDate & "' and
'" & varEndDate & "'"
strSQL = strSQL & " and c.stkcode = '" & StockCode.Value & "'"


TotalQuantity = cnn.Execute(strSQL)
MsgBox ("Total Quantity = " & TotalQuantity)

This gives run time error 450 - wrong number of arguments or invalid
property assignment against the msgbox(totalquantity) line

This sits within the click event of a button. I've used message boxes
to make sure that the variables in the query are populated and that
the query returns a result if you run it in query analyser.

To be honest this is my first attempt at VBA - although I use vbscript
quite a bit to automate admin tasks...

I'm guessing that I've misunderstand how to run the query or how to
manipulate the response...

If anyone can help that would be incredible...

Paul

 

[Roger Whitehead]

Not sure that this answers the way you want, but drop these lines into your
code

Dim RST as ADODB.Recordset

Dim cnn As ADODB.Connection
Dim strConn As String
Set cnn = New ADODB.Connection
strConn = "Driver={SQL
Server};Server=POServer;Database=Live_Integra;Uid=xxx;Pwd=xxxxx;"

set RST = new DODB.Recordset



'Make the strSQL here ish
cnn.Open strconn
With cnn

RST.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic, -1 '
arguments 2 & 3 are flexible
msgbox RST.Fields(0)

RST.Close
End with
cnn.Close


--
HTH
Roger
Shaftesbury (UK)

 

[Panda]

Now I get RunTime error 91 - Object Variable or With block Variable
not set

Dim cnn As ADODB.Connection
Dim strConn As String
Dim RST As ADODB.Recordset

Set cnn = New ADODB.Connection
strConn = "Driver={SQL
Server};Server=POServer;Database=Live_Integra;Uid=sa;Pwd=caravan;"

'Get the total Quantity sold in that period


strSQL = "SELECT sum(c.QTY) as 'Quantity'"
strSQL = strSQL & " FROM acocmp1.currsale c WHERE (c.STKCODE Not In
('i98','SUBS'))"
strSQL = strSQL & " and c.trandate between '" & VarStartDate & "' and
'" & varEndDate & "'"
strSQL = strSQL & " and c.stkcode = '" & StockCode.Value & "'"

cnn.Open strConn
With cnn

RST.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic, -1

MsgBox RST.Fields(0)

RST.Close
End With
cnn.Close

Thanks for you help!

 

[Panda]

Now I get Run-Time error 91 - Object Variable or With block variable
not set

Thanks for your help

Dim cnn As ADODB.Connection
Dim strConn As String
Dim RST As ADODB.Recordset

Set cnn = New ADODB.Connection
strConn = "Driver={SQL
Server};Server=POServer;Database=Live_Integra;Uid=xx;Pwd=xxxx;"

'Get the total Quantity sold in that period


strSQL = "SELECT sum(c.QTY) as 'Quantity'"
strSQL = strSQL & " FROM acocmp1.currsale c WHERE (c.STKCODE Not In
('i98','SUBS'))"
strSQL = strSQL & " and c.trandate between '" & VarStartDate & "' and
'" & varEndDate & "'"
strSQL = strSQL & " and c.stkcode = '" & StockCode.Value & "'"

cnn.Open strConn
With cnn

RST.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic, -1

MsgBox RST.Fields(0)

RST.Close
End With
cnn.Close

 

[Panda]

Ah, didn't set the RST object...

I get a msgbox that says 304....

Thanks so much is working perfectly now...