Max Query Length - MS Access 2000

Hi, I am dynamically generating a query using VBA in MS Access. When I use that query to set the recordsource property of my form/report, I get error "2176" on runtime saying that the setting for recordsource property is too long. As per my knowledge, the max. allowed length for recordsource property is 2048, but my query length is more than that. What can be done in this case? Plz help. (I had an idea of dynamically generating some table and setting the recordsource as that table. But again, maximum query length will come into picture while inserting the data in new table)

I totally agree with eghtebas...I ran into the same problem....Make sure you Alias all your table names in your SQL

SELECT a.LastName, a.FirstName, b.Field3 FROM tblEmployees a JOIN tblTable2 b ON b.ID = a.ID ORDER BY a.LastName, a.FirstName

Taking what nic;o) said one step further you could keep the creation of your SQL statement dynamic in the open event, but append it to the querydefs collection to get around the 2048 limitation.

      Dim strSQL As String
      Dim qdefTemp as DAO.QueryDef

      strSQL = "Your huge SQL statement here"
      Set qdfTemp = CurrentDb.CreateQueryDef("qselTempForReport",strSQL)
 
      Make sure to set the record source of the form to the new query - qselTempForReport

Mike

Hi,

Thank you all for your suggestions.

I implemented what "eghtebas" suggested. That solved problem for one of the reports, but could not solve for another report with longer SQL. I think what the process mentioned by "eghtebas" does is to reduce the length of SQL as a whole using aliases like "a", "b" etc... So, it worked for my report with smaller query.

I tried to use "DAO.QueryDef" in the project as suggested by "nico5038" and "Data-Man". I think this should work as I solved a similar problem of longer query by creating a query in query builder and then setting recordset to that query. The problem I am facing here in MS Access module is that when I type "." after DAO, I do not get dropdown list of its properties. May be DAO is not installed in project. Can anyone tell how to install it so that I can use it in my MS Access module? (I installed MDAC 2.8 and also registered "dao360.dll" using "RegSvr32", but still it did not work.) I saw the dropdown list of "More Controls" in project, but I could not find anything like DAO or Data Access Objects or Microsoft DAO/Data Access Objects.

Can anyone suggest the solution?

Thanks,
JJ

You will need to set a reference to the DAO Object Library

In the VB Editor
Tools | Refereneces

scroll down until you see Microsoft DAO 3.6 Object Library and place a check in the little box.

Mike

Also other versions of DAO will work, no need for DAO 3.6 specifically.

Nic;o)