Using DSN-Less Connectino from Excel to SQL
 

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

Using DSN-Less Connectino from Excel to SQL
 

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" wrote in message
ups.com...
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

Using DSN-Less Connectino from Excel to SQL
 

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!

On 6 Jul, 14:13, "Roger Whitehead" wrote:
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" wrote in message

ups.com...

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

Using DSN-Less Connectino from Excel to SQL
 

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

On 6 Jul, 14:13, "Roger Whitehead" wrote:
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" wrote in message

ups.com...

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

Using DSN-Less Connectino from Excel to SQL
 

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

I get a msgbox that says 304....

Thanks so much is working perfectly now...

On 6 Jul, 15:04, Panda wrote:
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

On 6 Jul, 14:13, "Roger Whitehead" wrote:

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" wrote in message

oups.com...

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