I am using MS Access 97 and am having the following trouble:
I have a report on which I select the record source dynamically. For this, I use the following code in the "On Open" event:
Private Sub Report_Open(CANCEL As Integer)
Dim rstClassList As Recordset
Dim sqlStatement As String
DoCmd.RunMacro "student_school_selection2.OpenDialog", 1
On Error GoTo Report_OpenErr
sqlStatement = "SELECT field1, field2 " & _
"LONG LIST " & _
"FROM table " & _
Set rstClassList = CurrentDb.OpenRecordset(sqlStatement, dbOpenDynaset)
Me.RecordSource = rstClassList.NAME
Set rstClassList = Nothing
MsgBox (Err.Description & " " & Err.Number)
------------ Code -----------------------------------------------
The problem occurs when I have a long string in sqlStatement. My sql string is quite long, around 600 characters. Most of it is taken up in specifying the fields to select. I have found that when the sql string is long, MS Access gives me a error box stating: "Microsoft Access can't find the table or query: sqlStatement".
When I truncate my SQL statement, the report opens without a problem. Not sure why this is happening.
Thank you all for replying. Here is what I have tried and what has worked:
Me.RecordSource = rstClassList.SQL
gives me an error saying the method is not found. I am using VB 5. I have not sure if that's got anything to do with it.
"Also you dont need to open the recordset at all to get the sql to the recordsource..." as suggested by namliam. I am not sure what you mean by that. Again, I am newbie at all this, so excuse my ignorance.
Finally, as mentioned in the original question replacing select fields by select * does work. So I have just done that. So thank you CyberLnyx. Just curious, if you are selecting all the fields in a table, do you have to use a *. Why can't you just list all of them?
Using a * retrieves all columns in a table. There are 2 reasons why you shouldnt use it.
1) Mosttimes you dont need all columns
2) Using * works a bit slower then listing the columns right off...
OK we are talking milliseconds but ....
If you are trying to input the sqlStatemant into the RecordSource there is no need to use an intermediat recordset. Just use Me.Recordsource = sqlStatement
Be aware tho there is a limmit to the number of line continuations: & _
You may want to look at that...
1) Not all data providers let you get away with using * and you are therefore forced to list the field names (sometimes all of them). I encountered that error once and it took a while to figure out it was the * causing the problem (of course there is no problem using * with Access).
2) Speed, When you are returning huge recordsets every extra field slows down the reurned recordset.
Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!
Or try our Ask An Expert service to answer any of your questions!
Still no excuse, the way to go is NOT use * and solve the line continuations in another way e.g. Create a strSelect, strFrom, strWhere
strSelect = "Select " & _
"Field1 as bla, " & _
"Lots more fields as BlaBla, "
strFrom = "From " & _
"Lots off tables"
strWhere = "Where " & _
"Lots of ands and ors"
then execute sql: Currentdb.execute strSelect & Strfrom & strWhere
Or build it like so:
sql = "Select "
sql = sql & "Fields, "
sql = sql & "MoreFields "
sql = sql & "From "