Forced to Save and Syntax Problems 1

[RobTsintas]

I'm having the weirdest problems with one particular database (Access 97)

The first problem is as descibed in THIS thread. Access will not let me close a new query without saving it, and will automatically save a changed query without asking.

This is compounded by another problem: building a query in the design grid causes a syntax error because Access itself hasn't surrounded a table name with brackets in the SQL. However, I can't get to the SQL because of the error message, I can't close the query without saving it (see above), I can't save the query because of the error message!

I'm going round in circles here, and can't find any answers. Does anyone have any idea what could be causing this bizarre behaviour?

 

[dRahme]

Hi, I had a similar problem when I used Docmd.setwarnings false.

I had setwarnings set to true where I needed them and ran into the problem you describe (Acc 2000).

I ended up using DoCmd.setwarnings true in the on load event of the main form (switchboard) and this cured the problem.

I didn't like that patch so I created a new db and imported everything from the problem db into it and then ran decompile. That fixed the problem permanently, although I am still not sure exactly what the problem was.

The decompile string looks like this:

"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "c:\Access\Development\July\YourDb.mdb" /decompile

If you want to try that, make sure you back up the db first.

Hope that helps

 

[CajunCenturion]

Have you tried opening the Query is SQL Design view, correcting the syntax error, and then saving the query?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RobTsintas]

Thanks for that dRahme. The main startup form was, indeed setting setwarnings to false. I set it to true via the debug window and now it doesn't force me to save.


Any idea about the SQL problem though? I don't know why Access should neglect to place square brackets around a long table name when it's building the SQL itself.

 

[RobTsintas]

CajunCenturion: This is what I wanted to do, but the error message was preventing me from getting to SQL view. I couldn't close the query because the other problem was forcing me to save (which I couldn't do, because of the error!).

I should now be able to get around this problem, I'm just curious as to why it came about in the first place!

 

[CajunCenturion]

You should, once the query is open, switch to SQL view from a menu option.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dRahme]

Hi, the only time I have seen the sql problem is when I have queried a couple queries (5 or 6 actually) and I changed a field in one of the underlying tables. The only way I found around that is to fix the queried problem query. Then, it would display correctly.

Table names - I think you can avoid some of those problems by changing the table name to a short, one word name and then make use of the table's properties (right click on the table for this) and give it a good description.

For example a table named:

[Distinct Properties Table]

change to:

tblPropD

That way you avoid the use of brackets altogether and it makes for cleaner looking code.

If you apply the same principle to naming forms, fields and so on, mixing in lower and upper case, you can also save time.

For example if you have text box named txtBox and when you type it in code as me.txtbox (all Lower Case), the code will correct the case to me.txtBox as it was originally named and you know you got it correct.

A little off topic but it sure saved me time when I figured that one out.