Solved

Max Query Length - MS Access 2000

Posted on 2004-04-02
11
1,184 Views
Last Modified: 2012-06-21
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)
0
Comment
Question by:janmejay_bhatt
  • 4
  • 2
  • 2
  • +1
11 Comments
 

Author Comment

by:janmejay_bhatt
ID: 10745466
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)
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 250 total points
ID: 10745943
In OnOpen event of your report include:

me.RecordSource=fnMyReport

-----
In a standard module, include:

Function fnMyReport() As String

'    fnMyReport=... build your SQL, don't use table names where not necessary or use alia names like
'                       Select A.Name From tEmplyee A Order By Name
End If
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10748008
I totally agree with eghtebas...I ran into the same problem....Make sure you Alias all your table names in your SQL

0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10748013
SELECT a.LastName, a.FirstName, b.Field3 FROM tblEmployees a JOIN tblTable2 b ON b.ID = a.ID ORDER BY a.LastName, a.FirstName
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 250 total points
ID: 10748047
A workaround will be to store the query's SQL and base the form on the query instead of the SQL string.
Just use in the form's OnOpen event:

dim qd as dao.querydef

set qd = currentdb.querydefs("qryFormXdanamic")
qd.SQL = <generated string>

And make sure the form's source is set to qryFormXdanamic

That's all.

Nic;o)
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10752403
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

0
 

Author Comment

by:janmejay_bhatt
ID: 10762091
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
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10762119
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


0
 
LVL 54

Expert Comment

by:nico5038
ID: 10767136
Also other versions of DAO will work, no need for DAO 3.6 specifically.

Nic;o)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now