Preventing tables,queries, forms and reports to be imported into a new database

[Micron]

Let me know if I've missed anything

Maybe


LIKE, NOT, ALTER TABLE, BY (as in GROUP BY)?

Perhaps aggregate functions (MIN, MAX, etc.) but where does one stop? I would be happy with Like vs LIKE, but that's just me.
Would it make sense to move these values to a table so that they can be easily added to? That would also allow user to opt between LIKE and Like just by changing the 'new case' value.

[isladogs]

Maybe
LIKE, NOT, ALTER TABLE, BY (as in GROUP BY)?

Perhaps aggregate functions (MIN, MAX, etc.) but where does one stop? I would be happy with Like vs LIKE, but that's just me.
Would it make sense to move these values to a table so that they can be easily added to? That would also allow user to opt between LIKE and Like just by changing the 'new case' value.

I've now added Like, Not & changed Group to Group By
I thought about CREATE TABLE, ALTER TABLE but as data definition queries can only be written in SQL view, it seemed superfluous.
Mind you that's also true for union and passthrough queries.

With regards to aggregate functions, I don't usually bother with capitals myself.

[isladogs]

Further minor update attached to provide example code for use with SELECT queries in VBA as well as action queries.
No further changes planned, at least for now

Anyway, I hope its useful to the OP or other forum members who are learning how to convert query SQL to VBA or vice versa.

[Micron]

Colin, a fine example of your altruism.
Thanks on behalf of those 'future' members.

[Emmanuel]

Seems am the only one having issue with this sql to vba converter. Honestly, I have tried using it and I seem not o understand it’s usage.

[isladogs]

The utility has been around for many years, both in its original version by Allen Browne & my updated version. Most people seem to find it straightforward to use
Did you follow Micron's instructions from post #12. That's basically all you need to do to convert query SQL to VBA

Just saying you don't understand its usage doesn't really help anyone explain it to you

If you are still stuck then:
a) either just use saved queries but add other other security measures as outlined in my Access security articles
or
b) do the query SQL to VBA conversions manually yourself

[isladogs]

Attached is a further update to my utility with some bug fixes including:
a) fixed some issues with disabled buttons
b) restored the vertical scrollbar to the tab control ...etc

[moke123]

NOTE: @Moke123 also has a nice CodeGen utility which he may be willing to upload

Wish I could find it.

[isladogs]

Hi Moke
Here it is!😁

[moke123]

Hi Moke
Here it is!

WOW, Thanks!

I've looked for that many times in 100's of folders. I figured it got deleted or left behind on old machines. Any idea when I made that? It's got to be a few years.
Time for some updating!

Thanks.

[moke123]

Feels good to fix something that was in my head for a few years but couldn't fix because I couldn't find the file.
I didn't know how to identify the PK in the index collection back then so used a crappy work around.

[Welshgasman]

@Moke123
I was keen to see this, and downloaded it?
However when I tried it out, just on the Querydef, it missed the first field?

What have I done wrong?

[moke123]

@Moke123
I was keen to see this, and downloaded it?
However when I tried it out, just on the Querydef, it missed the first field?

What have I done wrong?

You did nothing wrong. ( edit: if you also select the PK field in the list , it should work as is)That's what I was up to 1am last night fixing. When I originally posted that in one of the forums it wasn't done yet but I posted it for some reason or another. It got lost and never finished. I think that has to go back at least 3-4 years ago, if not longer. Colin was probably Ridders at that time.

Not sure what I was thinking at the time, but as I said I used a crappy work-around. I didn't know how to identify the primary key field of a table with vba, so I iterated through the array of fields 1 to UBound instead of 0 to UBound. I always have my PK's as the first field. Last night I started fixing that issue and so far so good.

Here's an example of a change I made last night which seems to work good so far.

Public Function fUpdateSetString(strIN As String, strTable As String) As String

    Dim varString As Variant
    Dim i As Integer
    Dim strOut As String
    Dim iCount As Integer
    Dim Pk As String

    Pk = fncPrimaryKey(strTable)

    varString = Split(strIN, ",")

    For i = 0 To UBound(varString)

        If varString(i) <> Pk Then

            strOut = strOut & Space(10) & Chr(34) & varString(i) & " = p" & iCount & "," & Chr(34) & "  &  _ " & vbNewLine

            iCount = iCount + 1

        End If

    Next i

    If strOut <> "" Then

        strOut = Left(strOut, Len(strOut) - 11) & Chr(34) & "  &  _ "

    End If

    strOut = strOut & vbNewLine & Space(10) & Chr(34) & " Where " & Pk & "  =  " & "p" & iCount & Chr(34)

    fUpdateSetString = strOut

End Function

This puts the PK field in the right place in the where clause whether you select it in the listbox or not.

     Const Sql_Update As String = _
          "Update tblPeople  Set "  &  _ 
          "FirstName = p0,"  &  _ 
          "MiddleName = p1,"  &  _ 
          "LastName = p2,"  &  _ 
          "Suffix = p3"  &  _ 
          " Where PartyID  =  p4"

     With CurrentDb.CreateQueryDef("", Sql_Update)
          .Parameters(0) = Me.FirstName
          .Parameters(1) = Me.MiddleName
          .Parameters(2) = Me.LastName
          .Parameters(3) = Me.Suffix
          .Parameters(4) = Me.PartyID
          .Execute dbFailOnError
          .Close
     End With

Another change I made is when you generate the textboxes they are bound to the field rather than unbound.

Lots of changes to come. I'll post a copy after a few more tweaks and updates if you'd like.

[Welshgasman]

Yes, I amended the loop from 0 to Ubound() for field and values, and that appears to work at my end.

I would be unlikely to use it in earnest, but I was keen to see what it did.

No doubt plenty of others would put it to good use though?

[isladogs]

Hi
I haven't read the code changes you made above...but can confirm that it dates back to at least 2017 before I changed my user name.
IIRC you posted it in a thread related to my SQL2VBA utility. Anyway that's where I saved the file.

Glad I could help you by finding the file