<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
17,045 Points
10,445 Views
1 Endorsement
Last Modified:
Approved
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

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 

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]

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)

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]

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]

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]

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

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]

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

Open in new window


with the result:

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

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

Open in new window


Result:

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

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"

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'

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

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')

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

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

Open in new window


Result:

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

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

Open in new window


Result:

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

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

Open in new window


Result:

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

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

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

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

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
1
Comment
Author:Bitsqueezer
  • 5
  • 3
9 Comments
LVL 25

Author Comment

by:Bitsqueezer
Hi,

another expert from a German forum named Nouba found this little tool so interesting that he has made an Add-In for VBA from it now using VB6. He gave me the permission to publish it here - so here it is.

The attached file contains the compiled dll, a HTML help file, a little batch-installer and the complete source code.

To install it, copy the files from the "bin" folder to a place where it can stay and then start the batch file "register_LinQ4VBA.bat" (Windows Vista and higher: Start it using "Run as administrator").

The HTML help file is in German only but you can use the text of this article here as it is a translation of me and has mainly the same contents.

 - He has changed the naming of the modules a little bit, "c" and "m" instead of "cls" and "mod" in my case.
 - You don't need to export, delete and reimport the module anymore, this is done by the Add-In which you can call directly from the VBA editor. It simply recreates the module whenever needed with a simple click.
 - The target computer where your database should run don't need to have the Add-In installed, this is only for the developer computer.

Thanks to Nouba for this great Add-In!

Christian

EDIT: Unfortunately I cannot upload a binary dll and some of the resources like icon files are also not allowed. Here is the download link where you can find it (German forum):

"LINQ for VBA" AddIn

Also download the update later on the same page.
0
LVL 7

Expert Comment

by:msacc97
Nice article!
But AddIn link does not open..
0
LVL 25

Author Comment

by:Bitsqueezer
Hi,

maybe it's a problem of your DNS or slow connection? I can open the link without any problem. You will see a forum post with a little screenshot, the download link is at the lower right edge of that post.

Cheers,

Christian
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LVL 7

Expert Comment

by:msacc97
Strange..
I have the following message
Not sure what it means in German
No problems navigating Internet besides this one..

error
0
LVL 25

Author Comment

by:Bitsqueezer
Hi,

translated it means "phpBB: critical error, please send a message with IP address .... to admin@office-loesung.de. Page is currently not available: http..."

I know that this forum sometimes has a little bit problems so that the server is not available, but currently no problem. Here's the link as text:

http://www.office-loesung.de/fpost2403327.php#2403327

Maybe there's a problem with the "#2403327", try it without that or try to use the main page to see if you get in contact. Otherwise I would say it is a problem of the used DNS server, maybe the page is not reachable through this one.

Here's a list of recommended free DNS servers which you can use instead of the "Rogers Cable Comm" Internet provider you are using from Toronto. I cannot say anything about these DNS servers, I did not test them, use them at your own risk:

Chaos Computer Club:
•204.152.184.76 (f.6to4-servers.net, ISC, USA)
 •2001:4f8:0:2::14 (f.6to4-servers.net, IPv6, ISC)
 •194.150.168.168 (dns.as250.net; anycast DNS!)
 •213.73.91.35 (dnscache.berlin.ccc.de)
 •80.237.196.2
 •194.95.202.198
 
Germany Privacy Foundation
 •87.118.100.175
 •62.141.58.13
 •87.118.104.203
 •85.25.251.254
 •94.75.228.29

If that doesn't help I'm afraid I cannot help you as EE doesn't allow to upload the needed file types.

Cheers,

Christian
0
LVL 7

Expert Comment

by:msacc97
You was right, that is an Internet problem
Weird..
I was able to download the file from my different IP
Thank you!
0
LVL 1

Expert Comment

by:Joseph Krausz
Hi Guys

I am trying to download the add-in for some time, seems the site do not host the file anymore, can someone share the add-in or point where to download.

Regards

Joseph
0
LVL 25

Author Comment

by:Bitsqueezer
Hi Joseph,

you're right, the forum unfortunately is closed since a year or so and so the downloads are locked.

I've made my own download site in the meantime to offer my own downloads here instead, you can find this and a lot of other of my modules here:

Bitsqueezer's Access Downloads

Cheers,

Christian
0
LVL 25

Author Comment

by:Bitsqueezer
A little addition to the above mentioned addin: The author Nouba has also written a register command (must be started with "run as administrator") to register the dll as Access add-in.

You need the file "LinQ4VBA.reg", create a text file, name it like this and fill it with this contents:
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.0\Addins\LINQ4VBA.Connect]
"LoadBehavior"=dword:00000003
"Description"="VBE Code Helper for implementing a sort of LinQ in Access Databases"
"CommandLineSafe"=dword:00000000
"FriendlyName"="LINQ for VBA"

Open in new window


And to register, create another text file and name it "register_LinQ4VBA.bat" and fill it with the following contents:
regsvr32 "%~dp0\linq4vba.dll"
regedit -s "%~dp0\linq4vba.reg"
exit

Open in new window


Both files must be saved in the folder where you copied the dll file to and then start the "register_LinQ4VBA.bat" to register the Add-In.
0

Featured Post

The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Join & Write a Comment

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month