INSERT SQL

[MarkK]

My 2c is that running SQL in VBA is simpler, safer, more readable, and easier to maintain, if you use a temporary DAO.QueryDef object. Consider code like...

Sub TempQDFSample()
    
    Const SQL As String = _
        "INSERT INTO MyTable " & _
            "( SomeDate, StringField ) " & _
        "SELECT Date1, prmComment " & _
        "FROM OtherTable " & _
        "WHERE StartDate = prmD1 AND EndDate prmD2 "
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("prmComment") = Me.txtComment
        .Parameters("prmD1") = Me.tbDateStart
        .Parameters("prmD2") = Me.tbDateEnd
        .Execute dbFailOnError
    End With

End Sub

- String parameters are handled for you so your text data can contain apostrophes and double quotes. Names like O'Malley, and data like 8' - 2" x 4" are handled seamlessly, and exactly as entered by your user.
- Date parameters are handled for you so you don't have to worry about formats. Simply assign a valid date to a date parameter. Done.

 

[Pat Hartman]

Running queries using .Excecute is generally better than using built in Access methods but that doesn't mean that you need to embed the SQL string.

QBE is still the simplest way to create queries, especially for people who can't write SQL without a manual open beside them. I've been writing SQL since the 70's when I was working with DB2 (IBM's RDBMS) I used to dream of a tool to create SQL strings some way other than by typing them out character by character. The QBE was one of the things that made me fall in love with Access because it took away a large part of the tedium of writing SQL.

If you are working entirely in SQL Server then you can use the tools in SQL Server which provide intellisense to help you to write the queries. if you are working in VBA, you are SOL and have to type everything by hand or find similar queries and cut and paste. Not my idea of fun.

QBE has a couple of problems. One that really irks people who know how to write SQL (even me) is that it reformats your SQL statement and if your criteria is complex enough, almost turns it into gobbeldy gook. There is a solution though. and that is to NEVER switch the query to QBE view before saving it if you want YOUR formatting to stick. It is during the save that Access rewrites the query to make it easier for itself to render it in QBE view. When I have really complex criteria, I also use a safety play and store the SQL string in a table so I can recover it intact if I need to.

So probably 90% of what I need to do, I can do using QBE (assuming I don't care what the SQL string looks like - and why would I). For complex criteria, I switch to SQL view. I almost never use subselects since Access does not optimize them well. I use nested queries instead. They also happen to be easier to test. Plus QBE can't render them so you are stuck looking at them garbled by Access unless you embed them in VBA OR use the trick I described earlier.

MS has been promising for at least 20 years to give us an updated QBE. Maybe next year in Jerusalem.

 

[GPGeorge]

Spoiler: etc

Running queries using .Excecute is generally better than using built in Access methods but that doesn't mean that you need to embed the SQL string.

QBE is still the simplest way to create queries, especially for people who can't write SQL without a manual open beside them. I've been writing SQL since the 70's when I was working with DB2 (IBM's RDBMS) I used to dream of a tool to create SQL strings some way other than by typing them out character by character. The QBE was one of the things that made me fall in love with Access because it took away a large part of the tedium of writing SQL.

If you are working entirely in SQL Server then you can use the tools in SQL Server which provide intellisense to help you to write the queries. if you are working in VBA, you are SOL and have to type everything by hand or find similar queries and cut and paste. Not my idea of fun.

QBE has a couple of problems. One that really irks people who know how to write SQL (even me) is that it reformats your SQL statement and if your criteria is complex enough, almost turns it into gobbeldy gook. There is a solution though. and that is to NEVER switch the query to QBE view before saving it if you want YOUR formatting to stick. It is during the save that Access rewrites the query to make it easier for itself to render it in QBE view. When I have really complex criteria, I also use a safety play and store the SQL string in a table so I can recover it intact if I need to.

So probably 90% of what I need to do, I can do using QBE (assuming I don't care what the SQL string looks like - and why would I). For complex criteria, I switch to SQL view. I almost never use subselects since Access does not optimize them well. I use nested queries instead. They also happen to be easier to test. Plus QBE can't render them so you are stuck looking at them garbled by Access unless you embed them in VBA OR use the trick I described earlier.

MS has been promising for at least 20 years to give us an updated QBE. Maybe next year in Jerusalem.

If you can believe the Access Road Map, you don't have to wait a full year from now.

 

[Pat Hartman]

That's for VBA. Are they using it for the query editor also?

 

[GPGeorge]

That's for VBA. Are they using it for the query editor also?

I do believe that IS for SQL, yes.

 

[Pat Hartman]

But if it's been out since last October, why haven't I seen it yet? I'm not on the bleeding edge channel but I'm on the next one. Do you have it? Does anyone here have it????

 

[isladogs]

It isn't out. Currently slated for Dec 2022 but I expect that date will slip again.

 

[GPGeorge]

It isn't out. Currently slated for Dec 2022 but I expect that date will slip again.

Am I right in thinking it's a SQL editor? These days assumptions tend to betray me more often than not.

 

[isladogs]

I thought it was just for the VBE and not the QBE ...but I may be wrong

 

[GPGeorge]

I thought it was just for the VBE and not the QBE ...but I may be wrong

No both you and Pat think the same. That makes me odd man out.

 

[Pat Hartman]

OK, we're back to my "next year in Jerusalem" remark. Promises, promises. instead we'll get some stupid "feature" like mult-value fields so they can push off actually useful improvements.

 

[GPGeorge]

OK, we're back to my "next year in Jerusalem" remark. Promises, promises. instead we'll get some stupid "feature" like mult-value fields so they can push off actually useful improvements.

We have a Dataverse connector in beta testing!

 

[isladogs]

We have a Dataverse connector in beta testing!

Now I know you're winding up Pat!

 

[Pat Hartman]

You're all very funny But the way MS treats Access is very sad

 

[isladogs]

Pat
We can all agree about that point. You may possibly be interested in my Web article

A Complex Deep Hidden Attachment Mystery

This is the fifth in a series of security challenges. In this case, a problem occurs when deep hiding attachment fields and a solution is provided. But why does the solution work?

www.isladogs.co.uk

 

[Pat Hartman]

@isladogs - great detective work. You even have the ear of MS being a current MVP. Any chance they're going to fix the problem?

 

[GPGeorge]

You're all very funny But the way MS treats Access is very sad

You misspelled "special".

 

[Pat Hartman]

If that's the only typo you found, you need to clean your glasses

 

[isladogs]

@isladogs - great detective work. You even have the ear of MS being a current MVP. Any chance they're going to fix the problem?

Ha!
The bug has existed ever since the complex field types were added with Access 2007 so only 15 years so far!
That even exceeds issues such as the much more important 'monster bug' which is only 4 years old so far (and on the roadmap to fix) ....

I contacted the Access team about this particular bug last year and they were able to replicate it but stated it was low priority(!) for fixing.
I reminded them in January & got the same response.
To be fair, I agree that its an issue that is very obscure and has probably rarely arisen in all that time.
I suspect it will never be fixed whilst other much more pressing issues still need attention.

However, I've recently been in discussion with ex-MVP Ben Clothier about this issue
As I'm sure you're aware, Ben is far more knowledgeable than almost any other Access developer / MVP.
With some very helpful prompts from Ben, I believe I am very close to fully explaining the problem ... and I already have a solution!

 

[isladogs]

If that's the only typo you found, you need to clean your glasses

Perhaps George has 'special glasses' with rose tinted filters