"LINQ for VBA" and Typed Tables, Queries and Field Names using VBA

Hi,

If you've ever programmed in a .NET programming language you may have worked with LINQ. LINQ is a short term for "Language Integrated Query" and is a great concept from Microsoft to integrate SQL queries directly into the programming language as part of the language itself. (LINQ can of course do more than that, for example query collections of variables and so on.)

Using a typed dataset in the .NET language Visual Studio automatically generates code for any field and table of the data source which makes it easier to create compiler-safe code and you can use IntelliSense to work for example with a list of fields instead of assembling a SQL string and look up the name of a field in the table or query to see how it was written.

The same problem we Access programmers have, we also have in VBA, unfortunately we don't have LINQ or typed datasets. We need to carefully assemble SQL strings in case of dynamic SQL (everytime where no saving of a query is possible) and there are additional problems like the strange Access syntax for special fields like date/time fields with "#" surrounding the value and so on.

To solve this and make it easier to work with tables, queries and fields in VBA I created a kind of "LINQ for VBA" which makes it possible to write code directly in VBA looking like a SQL string. It's not a replacement for using real SQL, of course, but for the most simple things like assembling a SQL string for forms and reports it is usable.

It doesn't support JOINs because Access has it's own method of using brackets to build JOINs which doesn't exist in other SQL dialects and Access sets these brackets depending on the optimization of a query so it would be hard to emulate that in code. Normally it is not necessary to use that in dynamic SQL because we can build a general query using JOINs and save it and use that as basis to build a dynamic query for a form or report.

The little project is separated in two parts: The generator, which loops through all tables and queries and their fields of the current database and generates enums and functions for each of them, and two classes which emulates the "LINQ". They are contained in the attached ZIP file.

1. The Module Generator

The Module Generator is independent of the two classes, you can also use it if you do not want to use the classes. Only the generated function "CCSQL" and it's object variable has a relation to them, they can be deleted from the generated module if the classes will not be used.

To generate the module (after importing the module "modCCSQL_CreateEnums.bas") you only need to enter this into the immediate window of the VBA editor:

CreateEnumModule

Select all Open in new window

In the current folder where the database file itself is located the module "modCCSQL_TableEnums.bas" will be generated, then you can import this module in your database, that's all it takes.

Now you have enums for all tables and queries available in IntelliSense which you can use everywhere in your code so you never need to use literals for any table, query or field name (including query aliases).

If you need to change a field name or table or query name anytime during the development of your database you simply use the described procedure above to regenerate the module, then delete the old one from your database and reimport the new one. If you compile your code now you will immediately find all the places where the old names were used as they generates a compile error and then you can change them easily using IntelliSense.

2. Usage of the generated module

The usage of the generated module is very easy. For any table there is an enumeration which begins with "Tab_" and ends with the name of the table. A table named "Table1" would create an enum named "Tab_Table1". This enum contains an entry for each fieldname of this table and each entry begins with the prefix "enm_" to avoid collision with other names. With the command:

?Tab_Table1.enm_ID
                       0 

Select all Open in new window

you would get a "0" as result if there exists a field named "ID" in "Table1" and if it is the first field in the list. That alone would not be helpful in your own code because normally you need the column number of a field only rarely (but if you can of course use it). More interestingly would be the name of the field. So because of that, there is a function for each table which can be used to lookup the correct field name. This is how it works (in the immediate window):

?Fld_Table1(enm_ID)
                      [ID]

Select all Open in new window

Of course it would be shorter to simply write "[ID]" in your code but if the fieldname ever changes you must search for any field name called "ID" and additionally you may have used it several times not only for one table so you cannot simply search and replace in the whole project code.

The special feature of these field functions here are that they use the above described enumerations so IntelliSense lists only the fields which really exists in the wanted table after you wrote the first bracket after the function name. You can now simply select the wanted field and close the bracket.

If you used the same field name in several tables you need to additionally prefix the field name with the enum name, otherwise the compiler says that there is a name conflict - another security for you to make sure that you can replace the name using search and replace for the whole project in that case. The function looks like that in this case:

?Fld_Table1(Tab_Table1.enm_ID)

Select all Open in new window

If you want to have a complete list of fieldnames there is also an additional function with the suffix "_All" for each table/query in the generated module, like "Fld_Table1_All" in the example. This will output a list of all fields of a table/query (see below for the syntax using queries) where every fieldname is enclosed in square brackets and all are separated by commas so you can use it directly in a query (which is better than using "*").

This is especially useful if you want to write a code to duplicate a record. When writing a new record some fields mustn't be used like an automatic generated ID or a timestamp field (in case of SQL Server). To exclude some fields from the complete list you can enter a list of fields as parameters for the "..._All" functions ("ParamArray"). For example:

?Fld_Table1_All(Tab_Table1.enm_ID)
                      [Last Name],[City]

Select all Open in new window

Or, if the field name "ID" doesn't exist in another table, then you can drop the Tab_Table1 prefix:

?Fld_Table1_All(enm_ID)
                      [Last Name],[City]

Select all Open in new window

In this case you can not (unfortunately) get IntelliSense support because ParamArray only allows Variant as variable type. But the function tests if the parameters are of type number (which is returned by the enum) and returns an error if it is not.

You can use IntelliSense anyway if you prefix the list of fields with the enum name, then you get the fields of this enum only. But the function can of course not test if you have used the correct enum, that's on you.

The same for queries, the only difference is that the enums for queries are prefixed with "Qry_" and the field functions are prefixed with "QFld_".

There are also two more functions which lists the names of all tables or all queries, "Tab_Names_All" and "Qry_Names_All". This can for example be used to fill a listbox with these names by assigning the result string as RowSource (in a German version for example you need to replace the commas with semicolon before assigning it).

Moreover there is also the possiblity to get the single names of a table or query. For this purpose there are two functions "Tab_Name" and "Qry_Name" which uses the enums "EnmMyTables" and "EnmMyQueries". To get the table name of "Table1" you can use:

?Tab_Name(enmMyTables_Table1)
                      [Table1]

Select all Open in new window

Here you have again of course IntelliSense and here there should be no name duplicates as there are no two equal table or query names in the list of tables and the list of queries. All names are automatically enclosed in square brackets.

3. "LINQ for VBA"

Based on the generated module you can now use the classes "clsCCSQL" and "clsCCSQL_Condition" to get a kind of "LINQ" in VBA. After importing these two classes into the own database and after generating the module and import it like described above you should be able to compile your code without error message. The classes needs the enums and functions of the generated module as basis.

If the compiler is through without an error you can start using these classes immediately. The above generated module contains an object variable and a function named "CCSQL" which automatically instantiates the class "clsCCSQL" and save the instance into the object variable "prv_objCCSQL" of the generated module.

You can of course also use your own object variables and instantiate as many additional objects of the class as you want. The second class "clsCCSQL_Condition" is automatically maintained by the class "clsCCSQL".

So now you can start to write SQL commands into the code like this (to show a very simple case):

    With CCSQL
                              .SELECT_ Fld_Table1(Tab_Table1.enm_ID)
                              .FROM_ enmMyTables_Table1
                              Debug.Print .SQLString
                          End With

Select all Open in new window

The "commands" ends with "_" to avoid name conflicts with real SQL commands or other keywords of VBA. Using "With" to get an object of the class "clsCCSQL" you can simply start each "command" with a dot and IntelliSense shows you all available properties and methods of the class.

At the end you can get the complete SQL string with the function "SQLString". The result would be here:

SELECT [ID] FROM [Table1]

Select all Open in new window

The SELECT_ "command" uses again a ParamArray which wants to have the name of the columns as simple strings so you need to use the "Fld_" functions to get the field names. The reason is that a SELECT can also have completely different things as "column", for example the name of a function or a calculation or a complete subquery.

In opposite to that "FROM_" uses the enumeration "EnmMyTables" because this function can only be used with real table names. To use a query instead there is an additional function "FROMQRY_" which uses the enum "EnmMyQueries" instead. In both functions you cannot use a string as parameter which is intentionally the case. The sense is that both functions tries to read out the datatypes of the specified query/table so that the correct syntax for comparisons will be generated.

Of course there is a "WHERE_" function for that purpose which looks like this:

    With CCSQL
                              .SELECT_ Fld_Table1(Tab_Table1.enm_ID)
                              .FROM_ enmMyTables_Table1
                              .WHERE_ Fld_Table1(Tab_Table1.enm_ID), EQUAL_TO, "1"
                              Debug.Print .SQLString
                          End With

Select all Open in new window

with the result:

SELECT [ID] FROM [Table1]  WHERE [ID] = 1

Select all Open in new window

"WHERE_" uses a string for the fieldname as first parameter, then you can select a comparison condition from an enum and as third parameter again a string with the value. The function automatically checks the datatype of the field and chooses the right syntax. Example:

    With CCSQL
                              .SELECT_ Fld_tblOrders_All
                              .FROM_ enmMyTables_tblOrders
                              .WHERE_ Fld_tblOrders(enm_OrderDate), GREATER_OR_EQUAL, "07.05.2009"	' Example for German date
                              Debug.Print .SQLString
                          End With

Select all Open in new window

Result:

SELECT [ID],[OrderAdditionalID],[OrderNo],[Position],[OrderDate],[PersonID] FROM [tblOrders]  WHERE [OrderDate] >= #05/07/2009 0:0:0#

Select all Open in new window

As you can see the date was automatically convertes to a universal format and enclosed in "#".

That's not all that the class can do for you. For example you would normally like to use additional conditions with WHERE. This is of course also possible here. For this purpose you will find the functions "AND_", "OR_", "NOT_". As example add this line in the code above after the "WHERE_":

        .AND_ Fld_tblOrders(enm_OrderAdditionalID), EQUAL_TO, "Test"

Select all Open in new window

Now the result is:

SELECT [ID],[OrderAdditionalID],[OrderNo],[Position],[OrderDate],[PersonID] FROM [tblOrders]  WHERE [OrderDate] >= #05/07/2009 0:0:0# AND [OrderAdditionalID] = 'Test'

Select all Open in new window

You can see that there were automatically single quotes added for the field "OrderAdditionalID" because this field is a textfield in the table.

But what if you want to add more complex conditions with additional brackets? This is also possible, for this case there are additional functions with the suffix "_Begin" and "_End". For example using "AND" there are the functions "AND_Begin" and "AND_End" and so on for "OR" and "NOT". So you can create a code like this and add nice optical formatting to the code:

    With CCSQL
                              .SELECT_ Fld_tblOrders_All
                              .FROM_ enmMyTables_tblOrders
                              .WHERE_ Fld_tblOrders(enm_OrderDate), GREATER_OR_EQUAL, "05/07/2009"
                              .AND_begin Fld_tblOrders(enm_OrderAdditionalID), EQUAL_TO, "Test"
                                    .OR_ Fld_tblOrders(enm_OrderAdditionalID), EQUAL_TO, "Test2"
                              .AND_end
                              Debug.Print .SQLString
                          End With

Select all Open in new window

Result:

SELECT [ID],[OrderAdditionalID],[OrderNo],[Position],[OrderDate],[PersonID] FROM [tblOrders]  WHERE [OrderDate] >= #05/07/2009 0:0:0# AND ([OrderAdditionalID] = 'Test' OR  [OrderAdditionalID] = 'Test2')

Select all Open in new window

Of course you can also add GROUP BY with the functions "GROUPBY_" and "HAVING_". "HAVING_" works exactly like "WHERE_" and you can also add additional conditions with "AND_" and so on like described above.

And there is a function "ORDERBY_" which can be used to set the ordering for one fieldname and an additional function "THENBY_" for any further fieldname. In the end you could write a query like this:

    With CCSQL
                              .SELECT_ "ID", "OrderAdditionalID"
                              .TestFieldExists = True
                              .UseSquareBrackets = True
                              .FROM_ enmMyTables_tblOrders
                              .WHERE_ "ID", GREATER_THAN, "2"
                                  .AND_ "OrderAdditionalID", LIKE_, "Blue*"
                                  .AND_begin "ID", NOT_EQUAL_TO, "4"
                                       .OR_ "OrderAdditionalID", NOT_EQUAL_TO, "Green"
                                  .AND_end
                              .GROUPBY_ "ID", "OrderAdditionalID"
                              .HAVING_ "ID", LOWER_THAN, "3"
                              .AND_ "OrderAdditionalID", IS_NOT_NULL, ""
                              .ORDERBY_ Fld_tblOrders(enm_OrderAdditionalID), enmOrderDESC
                              .THENBY_ Fld_tblOrders(enm_OrderDate), enmOrderDESC
                              .THENBY_ Fld_tblOrders(enm_OrderNo)
                              Debug.Print .SQLString
                              Debug.Print .GetErrorList
                          End With

Select all Open in new window

(As a variant you can see here that you can also use direct strings instead of the "Fld_.." functions of the generated module.) There is also an additional property named "TestFieldExists" which checks the data source if the fieldname exists and "UseSquareBrackets" with which you can decide if the output should contain square brackets (Default = True). At the end you can see the function "GetErrorList" which you can use to get a list of errors at the end which is generated by all these functions.

Beside "SELECT_" there are also "INSERT_INTO", "UPDATE_" and "DELETE_". Any of these four functions first deletes all other settings of the class which maybe were generated by former calls, for example the OrderBy columns or the condition or the error list.

Example for DELETE_:

    With CCSQL
                              .DELETE_
                              .UseSquareBrackets = True
                              .FROM_ enmMyTables_Member
                              .WHERE_ Fld_Member(Tab_Member.enm_Retirement), GREATER_OR_EQUAL, "09/30/2012"
                              Debug.Print .SQLString
                              Debug.Print .GetErrorList
                          End With

Select all Open in new window

Result:

DELETE * FROM [Member]  WHERE [Retirement] >= #09/30/2012 0:0:0#

Select all Open in new window

(DELETE doesn't work with queries so you would get an error using "FROMQRY_".)

Example for UPDATE_:

    With CCSQL
                              .UPDATE_ enmMyTables_Member
                              .UseSquareBrackets = False
                                  .SET_ Fld_Member(Tab_Member.enm_Retirement), "07/31/2012"
                                  .WHERE_ Fld_Member(Tab_Member.enm_ID), GREATER_THAN, "1"
                              Debug.Print .SQLString
                              Debug.Print .GetErrorList
                          End With

Select all Open in new window

Result:

UPDATE Member  SET Retirement = #7/31/2012#  WHERE ID > 1

Select all Open in new window

Example for INSERT_INTO:

    With CCSQL
                              .INSERT_INTO enmMyTables_Member
                              .UseSquareBrackets = False
                                  .SET_ Fld_Member(Tab_Member.enm_Retirement), "07/31/2012"
                              Debug.Print .SQLString
                              Debug.Print .GetErrorList
                          End With

Select all Open in new window

Result:

INSERT INTO Member (Retirement) VALUES (#7/31/2012#) 

Select all Open in new window

(In the last two examples you can also see the result of using "UseSquareBrackets = False".)

The mindful reader of the "INSERT_INTO" function will recognize that this function works exactly like the "UPDATE_" function by using the "SET_" function. The reason is that I found it more useful and easier to use fieldname and it's value side by side instead of using a fieldlist and a valuelist like in Access SQL.

In the original INSERT command you need to count the columns and see which fieldname is in the fieldname list to set the right value in the valuelist. SQL dialects like MySQL in opposite also allows to use the SET syntax for INSERT which makes it very much easier to assemble dynamic SQL commands using UPDATE or INSERT depending on what you want to do where you only need to change the command and not the rest of the code - this is often done in web databases with PHP.

Now you have the same possibility using VBA and these classes.

(If you want to use UPDATE and INSERT with queries you need to use the functions "INSERT_INTO_QRY" and "UPDATEQRY_" instead because of the difference in the used enum.)

An "INSERT...SELECT" is not implemented as function but you can also do that with this class. Here an easy code to duplicate a record:

    Dim strSelect As String
                          Dim strFields() As String
                          Dim varFields() As Variant
                          strFields = Split(Fld_tblOrders_All(Tab_tblOrders.enm_ID), ",")
                          varFields = ConvertToParamArray(strFields)
                          With CCSQL
                              .SELECT_ varFields
                              .FROM_ enmMyTables_tblOrders
                              .WHERE_ Fld_Table1(Tab_Table1.enm_ID), EQUAL_TO, "2"
                              strSelect = "INSERT INTO " & Tab_Name(enmMyTables_tblOrders) & _
                                                       " (" & Fld_tblOrders_All(Tab_tblOrders.enm_ID) & ") " & _
                                          .SQLString
                              Debug.Print strSelect
                          End With

Select all Open in new window

"Split" doesn't work with Variant arrays so you need to use a String array instead. "ParamArray" on the other hand doesn't work with Variant arrays where also any element of the array must be a Variant datatype. So for the purpose to convert a String array into a "ParamArray" compatible Variant array the above automatically generated module also contains a little function "ConvertToParamArray" which does this.

Inside the "SELECT_" function there is a test if the first element of the ParamArray is itself an array and if this is the case it will be "copied up". This is a trick which only works with Variants: you can set a Variant equal to an array and so the Variant variable is converted to a Variant array. Complicate to say it in words, in the "SELECT_" function this part of the code does this job:

        If IsArray(varField(0)) Then
                                  If UBound(varField) = 0 Then varField = varField(0)            ' Copy an array to "varField" so it can be used like a normal ParamArray
                              End If

Select all Open in new window

So if the first element of ParamArray is itself an array and ParamArray only consists of one element (the array) using "varField = varField(0)" will copy the array "up" and now the rest of the function can work as if the user of the function would have used comma separated values as parameters.

In the code example above the "INSERT INTO" is assembled manually but this is no problem as it only contains the command text and the brackets, the fieldlist is automatically generated by the "..._All" function and the ID column is excluded and so in the end it is compilersafe like with using the other functions. In the result you would get:

INSERT INTO [tblOrders] 
                            ([OrderAdditionalID],[OrderNo],[Position],[OrderDate],[PersonID]) 
                      SELECT [OrderAdditionalID],[OrderNo],[Position],[OrderDate],[PersonID] 
                        FROM [tblOrders]  WHERE [ID] = 2

Select all Open in new window

(The output is formatted for easier reading, SQLString returns a simple line without word wrap.)

The duplicate code is universal also for additional changes of the table as the "All" function is used. Only if the column name "ID" would be changed you would need to change the code here. That you would see if you simply compile the code.

Of course the modules and classes are documented, you can see additional remarks in the code. You can also extend the classes or the code generator if you have additional ideas or purposes. You could also, for example, create a simple query generator for the end users with these classes and modules.

Have fun in experimenting with it

Christian
CCSQL.zip