Cannot get a SQL statement to work??? 1

[Cleis]

Hi group:

I've got a little problem below. As I'm not very strong in ADO, can someone please point out my mistake! I'm trying to run this SQL statement and post the result into a bound fieled on a form. When I run this code, I get "Method "open of Object" Recordset failed.



Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim strSQL As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset


strSQL = "SELECT TOP 1 tblCalandar.Month FROM tblCalandar WHERE (((tblCalandar.End) >= Date)) ORDER BY tblCalandar.End;"
rs.Open strSQL, conn, adOpenStatic

Me.txtDetailPeriod = rs
rs.Close
conn.Close

Set rs = Nothing


Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

 

[cmmrfrds]

Probably missing an instance of the connection object.

Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset

strSQL = "SELECT TOP 1 tblCalandar.Month FROM tblCalandar WHERE (((tblCalandar.End) >= Date)) ORDER BY tblCalandar.End;"
rs.Open strSQL, conn, adOpenStatic

One of these.
rs.Open strSQL, CurrentProject.Connection, adOpenStatic

the new keyword creates an instance.
Dim conn As New ADODB.Connection
Or
Set conn = New ADODB.Connection

 

[MajP]

I get it to run with my own sql string. Do you have a reference to Microsoft Active X data objects? Does that string work in in the Access query environment?

 

[Cleis]

HI folks!

I've tried both suggestions and still getting errors?!?! I have a ref to Active X 2.1 lib. The SLQ statement does work in the Query grid.

I'm at my wits end.


Thanks for your help so far.

 

[cmmrfrds]

Same error?

 

[Cleis]

yep. This is very frustrating. Thanks for your help.

Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

strSQL = "SELECT TOP 1 tblCalandar.Month FROM tblCalandar WHERE (((tblCalandar.End) >= Date)) ORDER BY tblCalandar.End;"
rs.Open strSQL, CurrentProject.Connection, adOpenStatic


Me.txtDetailPeriod = rs
rs.Close
conn.Close

Set rs = Nothing

 

[cmmrfrds]

strSQL = "SELECT TOP 1 tblCalandar.Month FROM tblCalandar WHERE (((tblCalandar.End) >= Now())) ORDER BY tblCalandar.End;"

Put in at this point.
Debug.Print strSQL

Do a Control G to look at the output.

I am not sure Date is a recognized function in ADO. Change Date to Now()

 

[MajP]

Try a very simple SQL string. I do not think that is your problem, but check. Maybe
"Select * from tblCalandar"

Then please list your references that you have selected.

 

[MajP]

I am thinking the same thing about date. I think it should work, but if you do not have the correct vb reference you may have a problem. Try this in a test procedure
msgbox date()

 

[Cleis]

Hi cmmrfrds!

Did as you suggest including changing date to now(). Here is a new message.

"Automation Error Unspecified error"

Any ideas?

 

[MajP]

See if you have a newer version than 2.1. Even if you do not, unselect and reselect it.

 

[cmmrfrds]

Another thing is that 2.1 is a very OLD library.

Go into references and uncheck the 2.1 library. Close and then go back into references and see if there are some later libraries to select. Maybe 2.7 or 2.8

 

[MajP]

Hey cmmrfrds,
I beat you by 1 minute.

 

[Cleis]

Hi MajP:

Thanks for your input. I'm using Date() everywhere in my app. ?Date() in the immediate window works?!?!

I also took your suggestion on changing teh SQL statement.

In teh immediate window after the debug I got the following

"rs.Open strSQL, CurrentProject.Connection, adOpenStatic"

When I hit "Me.txtDetailPeriod = rs" I got the value you entered is not valid for this field. txtDetail period is a bound text box to capture the month I'm trying to capture from the above SQL statement (Accounting Calandar)

Many thanks for your input

 

[Cleis]

Hi Guys!

I did change my ref to 2.5 to no avail same error.

 

[cmmrfrds]

Month is probably a reseved word. Probably even End is. Surround with [] if you are using reserved words - not a good idea through.

strSQL = "SELECT TOP 1 tblCalandar.[Month] FROM tblCalandar WHERE (((tblCalandar.End) >= Date)) ORDER BY tblCalandar.End;"

A couple of ways to reference the field are:

This will work since there is only 1 field in the select list.
Me.txtDetailPeriod = rs(0)

Most common.
Me.txtDetailPeriod = rs![Month]

But, make sure you are not at EOF when you do this.

 

[Cleis]

Okay this is really starting to blow my mind here! I renamed all fields ref in the SQL by adding an A in the front. I unbound the text box on the form. My new error message is at conn.close "Operation is not allowed when object is closed" WHATS going on here or am I as dumb as a box of rocks???????????

Many thanks to one and all!

Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

'strSQL = "SELECT TOP 1 tblCalandar.AMonth FROM tblCalandar WHERE (((tblCalandar.AEnd) >= Now())) ORDER BY tblCalandar.AEnd;"
strSQL = "Select * from tblCalandar"
rs.Open strSQL, CurrentProject.Connection, adOpenStatic

Debug.Print strSQL


Me.txtDetailPeriod = rs

rs.Close
conn.Close

Set rs = Nothing

 

[MajP]

I think you are close though. If I hear you right it sounds like if you use the simple strSql you are able to set the recordset (rs) (make the connection). Remember, you still have the problem with
me.txtDetailPeriod = rs
That will give an error as explained earlier.
So if this is the case the problem is in your sql string not in references, instantiation, or anything else.

Tell me again what debug.print strSql gives you. You said the following:

In teh immediate window after the debug I got the following
"rs.Open strSQL, CurrentProject.Connection, adOpenStatic"

Can you explain what you meant.
after the debug.print strSql put
debug.print rs.recordcount
I want to see if you have actually returned a recordset. This will give the number of records.

 

[Cleis]

HI MajP:

Actually I do get a record set on both sql statements. On the simple SQL statement I get one record for each month (12). The more complex SQL statement returns one record for the current month (1).

I'm getting an error statement when I pass "conn.close" The message returns "Operation is not allowed when the object is closed." This just doesn't make any sense??

BY the way, I do get the result in the unbound text box . . . however this last error has me going batty!!!


THANKS!

 

[MajP]

Here is your problem I believe
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection

You have an implicit and explicit instantion. Since these objects already exist the following should be fine. It works for me:
Dim conn as adodb.connection
set conn = currentproject.connection