For multi-table joins, you have to nest the extra joins in brackets: SELECT … FROM ((origintable JOIN jointable1 ON …) JOIN jointable2 ON …) JOIN jointable3 ON … basically, for every extra table you join past the first, you need a bracket before the original ‘FROM’ table, and a closing bracket on the matching JOIN … Read more
You want to convert mdb to mysql (direct transfer to mysql or mysql dump)? Try a software called Access to MySQL. Access to MySQL is a small program that will convert Microsoft Access Databases to MySQL. Wizard interface. Transfer data directly from one server to another. Create a dump file. Select tables to transfer. Select … Read more
Summary This is a known bug caused by the Office updates released on November 12, 2019. The bug affects all versions of Access currently supported by Microsoft (from Access 2010 to 365). This bug has been fixed. If you use a C2R (Click-to-Run) version of Office, use “Update now”: Access 2010 C2R: Fixed in Build … Read more
Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION. In this case SELECT * FROM ( SELECT table1.field1 FROM table1 ORDER BY table1.field1 ) DUMMY_ALIAS1 UNION ALL SELECT * FROM ( SELECT table2.field1 FROM table2 ORDER BY table2.field1 ) DUMMY_ALIAS2
Length of an array: UBound(columns)-LBound(columns)+1 UBound alone is not the best method for getting the length of every array as arrays in VBA can start at different indexes, e.g Dim arr(2 to 10) UBound will return correct results only if the array is 1-based (starts indexing at 1 e.g. Dim arr(1 to 10). It will … Read more
If obj Is Nothing Then ‘ need to initialize obj: ‘ Set obj = … Else ‘ obj already set / initialized. ‘ End If Or, if you prefer it the other way around: If Not obj Is Nothing Then ‘ obj already set / initialized. ‘ Else ‘ need to initialize obj: ‘ Set … Read more
When people ask about a replacement for Access, a lot of them only think about the database, but what they are really asking about are all of the other features in Access. They usually don’t care what database Access is using. Some of the functionality provided by Access are: Forms, Query Building, Reports, Macros, Database … Read more
UCanAccess is a pure Java JDBC driver that allows us to read from and write to Access databases without using ODBC. It uses two other packages, Jackcess and HSQLDB, to perform these tasks. The following is a brief overview of how to get it set up. Option 1: Using Maven If your project uses … Read more
SUM is an aggregate function. It will calculate the total for each group. + is used for calculating two or more columns in a row. Consider this example, ID VALUE1 VALUE2 =================== 1 1 2 1 2 2 2 3 4 2 4 5 SELECT ID, SUM(VALUE1), SUM(VALUE2) FROM tableName GROUP BY ID will … Read more
We wrote our own script in VBScript, that uses the undocumented Application.SaveAsText() in Access to export all code, form, macro and report modules. Here it is, it should give you some pointers. (Beware: some of the messages are in german, but you can easily change that.) EDIT: To summarize various comments below: Our Project assumes … Read more