| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
