Share via

MS Access: tab character (\t) breaks Query's SQL view

Anonymous
2023-02-09T14:45:41+00:00

Hi,

I found very annoying behaviour of MS Access SQL editor (SQL View of Query).

It cannot handle tab character.

If it would replace it with space that would be great.

If it would simple delete it, it would be ok.

But it's worse than that, if tab character is pasted into SQL view together with rest of query, it's being hidden but it will make running/changing query impossible in most frustrating way I've ever seen.

It will remove one character from string, and if I try to retype it, it will add extra character somewhere else, removing extra character somewhere else would make it look ok, but running query would add yet another character.

only solution I found is using notepad++ or other text editors able to show all characters, pasting query there, then finding and deleting or replacing tab character, then copying "cleared" query and pasting it back to access.

Is there any solution/workaround to that? it's extremely frustrating behaviour when query just breaks itself.

Cheers,

Marek

Microsoft 365 and Office | Access | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-02-09T17:27:26+00:00

    Cases when I paste SQL string into SQL Editor:

    Hi Marek,

    I think your problem is analogous to some kind of "de-RTF-er" that I used long ago.

    I had a form with one (large) control in which the original text was copied.

    The form had also a command button to run VBA-code to remove all unwanted portions.

    After clicking this command button there was still the possibility to manual edit the "cleaned" text. After this the text could be used for further processing, as in your case the SQL-property of a QueryDef.

    Perhaps you can use the idea.

    Imb.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-02-09T16:29:56+00:00

    Also good to know: MSFT is working on integrating the Monaco editor into the SQL view window. That's still a work in progress but should eventually make this problem go away.

    that actually sounds great!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-02-09T16:28:05+00:00

    Well main reason why I paste query into SQL Editor is that I avoid using it all cost.

    Bug I mentioned in first post, removing new line character whenever it wants, putting brackets all over the place are few of reasons I'm not using it to work on query.

    Cases when I paste SQL string into SQL Editor:

    • when SQL query is already there but I need to modify it slightly i.e. by using value received via email/teams, copying from there likes to copy also extra characters
    • when I prepare query in editor like SQL Developer, which has no problem with tabs, and then I need to paste it into MS Access to be used as part of Access-based tool
    • when I use SQL Formatter and not change tabs to spaces.

    Also, queries have no problem with strings containing tabs or new line characters.

    Dim rstTest As Recordset  

CurrentDb.QueryDefs("_TEST_").SQL = "   SELECT  1  " & vbTab & "FROM    DUAL    " & vbCrLf & "WHERE 1=1       ;" 
Set rstTest = CurrentDb.QueryDefs("_TEST_").OpenRecordset 
rstTest.MoveLast 
Debug.Print "records found:";rstTest.RecordCount 
rstTest.Close 
Set rstTest = Nothing

    Above works just fine.

    How it's possible to have editor which works worse than notepad.exe in text handling?

    Was this answer helpful?

    0 comments
  4. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2023-02-09T16:20:57+00:00

    Also good to know: MSFT is working on integrating the Monaco editor into the SQL view window. That's still a work in progress but should eventually make this problem go away.

    Was this answer helpful?

    0 comments
  5. George Hepworth 22,855 Reputation points Volunteer Moderator
    2023-02-09T14:53:09+00:00

    Apparently you have a procedure which requires copying and pasting a SQL string into the SQL editor. Knowing that embedded control characters, such as the tab, are not acceptable in SQL, I would imagine that the best solution is to make sure that whatever strings you must copy and paste are cleansed prior to pasting them into the SQL editor. Notepad++ does an excellent job at that. That suggests you have the optimal solution unless you can create a VBA function to cleanse your strings between copying them and pasting them into the SQL editor.

    But all of that begs the question, where and why does this copy/paste operation fit into your normal workflow?

    Was this answer helpful?

    0 comments