Error when exporting source code for large query

[joyfullservice]

Discussed in https://github.com/joyfullservice/msaccess-vcs-integration/discussions/309

^{Originally posted by **StefanB1983** March 10, 2022} Hi, I have installed the Version Control System. Looking great and very user-friendly. When I export the source code I face on error though about a query that can not be exported. It is a big query with > 1000 lines. Do you know why the error occurs and how I can resolve this? Kind regards, Stefan

[joyfullservice]

Hi @StefanB1983, I have created an issue for this discussion since it sounds like we might be dealing with a potential technical issue here.

What is the exact error that you are seeing? Is there anything addition in the log file that points to the problem? Any additional clues or screenshots that you can provide will help us understand more about what is happening. I can't think of anything that would prevent a large query from being exported, but I haven't personally worked with one containing over 1000 lines.

[StefanB1983]

Hi @joyfullservice , thanks for the fast reply. Indeed forgot to add the printscreen.

Below an image of the error I see when running the export

What I can see from the export folder is that the .bas file exists for this query, but the .sql file is missing

When I run the export with the debug option enabled it stops at the position below. Almost sounds like it is related to the size of the query although when I save the query manually the file size is 34kb.

Hope this helps.

Kind regards, Stefan

[joyfullservice]

Hi @StefanB1983,

Thanks for the additional screenshots! Could you make one additional change as well? If you turn on the VBA option to break in class modules, you can see exactly where the export is hitting this error. I think that would point me in the right direction in potentially resolving this.

Thanks!

[StefanB1983]

Hi @joyfullservice ,

When I open the options dialog I have a different setup then yours. Do you know why?

[joyfullservice]

Just to clarify, this is the VBA options dialog in the Microsoft Access Visual Basic Editor, not the add-in options.

[StefanB1983]

Thanks that is clear. I have ran it again and now it breaks at the following line of code

[joyfullservice]

Thank you! That is where I thought it might be encountering the error. Can you try something else for me? Try running the following code in the VBA immediate window, using the name of your query.

?CurrentDb.QueryDefs("qryNavPaneGroups").SQL

This will allow us to determine whether the error is coming from Microsoft Access, or from the add-in. If the error is from Access, we may be hitting a system limitation that we can't do much about. (My hunch is that the problem is with the query definition being so large) If this is the case, here are a few things you could consider with your project:

• Simplify the query into multiple queries to reduce the size of the SQL text. I don't know what the query looks like, but perhaps this could be accomplished through some nested queries or by splitting into two or more queries that could be combined with a UNION query.
• You could also turn off the option to save the SQL text of your queries. Personally, I kind of like having the SQL because I find it easier to read the changes in version control, but I don't know what design limitations you are working with in your environment.

I hope this helps!

[StefanB1983]

When I run the code I get the following. I guess that means it is a limitation of Access. I will see if we can simplify the query. Thanks for your quick response and help!

[StefanB1983]

Hi @joyfullservice , I have adjusted the query and that indeed resolved the issue. Thanks!

[joyfullservice]

Hi @joyfullservice , I have adjusted the query and that indeed resolved the issue. Thanks!

Glad to hear that! I will go ahead and close out this issue. 👍