Error when openning a recordset..

[Guest]

I am getting a error when I open this recordset. I have tested the sql statement in winsql lite it worked fine. I just keep getting this error "-2147217908","Command text was not set for the command object.","Microsoft OLE DB Provider for ODBC Drivers" everytime it trys to open the recordset.. Below is part of my code.. Thansk for the help..

'Dimmension
Dim I As Intege
Dim conSQL As ADODB.Connectio
Dim rsSQL As ADODB.Recordse
Dim strSQL As Strin
Dim strDateFrom As Strin
Dim strDateTo As Strin
Dim strLineNumber As Strin
Dim blnStartup As Boolea
Dim strDefect(100, 1) As Strin

On Error GoTo errhandle

strDateFrom = Sheets("start(1)").txtFirstdate.Text & Space(1) & "00:00:01 AM'
strDateTo = Sheets("start(1)").txtSecondDate.Text & Space(1) & "11:59:59 PM'
strLineNumber = Sheets("start(1)").Range("c5").Valu
'Builds the sql Statemen
strbuild = "select " &
"DATEDIFF(MINUTE,startdatetime,enddatetime) as Timeused " &
",tblexistingrollcut.rollnumber, " &
"tblexistingrollcut.cutname,dispositioncode,rollcutdefect from " &
" tblroll,tblexistingrollcut left outer join tblrollcutdefect " &
" on tblexistingrollcut.rollnumber = tblrollcutdefect.rollnumber AND " &
" tblexistingrollcut.cutname = tblrollcutdefect.cutname " &
" where tblroll.rollnumber = tblexistingrollcut.rollnumber AND " &
" linenumber = '" & strLineNumber & "' and " &
" startdatetime > '" & strDateFrom & " AND " &
" ENDDATETIME < '" & strDateT
MsgBox strbuil
'''''''''''''''''''''''''''''''''''''''''''''''''
'Creates the connection to the databas
Set conSQL = New ADODB.Connectio
Set rsSQL = New ADODB.Recordse
''''''''''''''''''Connecting to Production''''''''''
If conSQL.State = adStateOpen The
conSQL.Clos
End I
Application.StatusBar = "(Connecting) to Production server
'on the fly connectio
conSQL.Open "Driver={sql server};server=production;database=dbsims;UID=test;pwd=test

'''''''Pulling Defect % '''''''''''''''''''''''''''''
Application.StatusBar = "(Connected Pulling Defect % from production server
'rsSQL.Open "select * from tblroll", conSQL, adOpenForwardOnly, adLockReadOnl
rsSQL.Open strSQL, conSQL, adOpenForwardOnly, adLockReadOnly

 

[Rob Bovey]

Hi Eric,

If this is exactly the code you're using, I don't see where you're
loading the strSQL string variable that you're passing to your recordset.
The SQL statement you construct is being loaded into an undeclared variable
called strbuild.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

I am getting a error when I open this recordset. I have tested the sql

statement in winsql lite it worked fine. I just keep getting this error
"-2147217908","Command text was not set for the command object.","Microsoft
OLE DB Provider for ODBC Drivers" everytime it trys to open the recordset..
Below is part of my code.. Thansk for the help..

 

[Jake Marx]

Eric,

Just to add to Rob's response, you should definitely start using Option
Explicit at the top of all code modules if you aren't already doing so. If
you had done that, you would have received a compile error telling you that
strBuild was not declared.

To require variable declaration for all future modules, just check "Require
variable declaration" under Tools | Options in the VBE.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi Eric,

If this is exactly the code you're using, I don't see where you're
loading the strSQL string variable that you're passing to your
recordset. The SQL statement you construct is being loaded into an
undeclared variable called strbuild.

<snip>