Access 2013: Enter Parameter Value override

[DKaur]

Hello all,

I am extremely new to VBA for Access. I have a series of tables (one for each year), all of which I want to query in the same way and then combine all the queries into one table using a union query. I have constructed some code which will do exactly this which is great. The problem I have is that the older years tables don't have all the fields that the later years do. Therefore when I run the code I get roughly 30 'Enter Parameter Value' prompt boxes. I need to enter "null" into all of them. Is there any way I can incorporate some override into my code which will automatically enter "null" into the box? I don't want to disable it since I don't want blank cells.

Here is a simplified version of my code:

Sub Loop_Criteria_UnionQUERY()
'define names for the database, query table and the SQL string
Dim db As Database
Dim qdf As QueryDef
Dim def As TableDef
Dim strName As String
Dim strSQLcriteriaquery As String
Dim strUnionQuery As String

'look into the current database
Set db = CurrentDb

'Loop through all tables in the file.
For Each def In db.TableDefs
'This if statement ensures the inbuilt tables are not affected.
If Left(def.Name, 4) <> "MSys" Then
strName = def.Name & "Query"
strSQLcriteriaquery = "SELECT [" & def.Name & "].[SEX], [" & def.Name & "].[AGE] WHERE ((([" & def.Name & "].[SEX])='M'));"
strUnionQuery = strUnionQuery & Left(strSQLcriteriaquery, (Len(strSQLcriteriaquery) - 1)) & " UNION ALL "
'Create a new query: CreateQueryDef("name to assign to the query table", SQLstringname)
Set qdf = db.CreateQueryDef(strName, strSQLcriteriaquery)
'Open the new query
DoCmd.OpenQuery strName
End If
Next def

strUnionQuery = Left(strUnionQuery, (Len(strUnionQuery) - 10)) & ";"
Set qdf = db.CreateQueryDef("UnionQuery", strUnionQuery)
DoCmd.OpenQuery "UnionQuery"

End Sub

Any help appreciated.

Many thanks,
D

 

[Joe4]

It actually sounds like a design problem, that you should probably have a single table instead of multiple similar tables. If you can change that, that is the way to go. But I am guessing maybe that is not an option for you.

If it is not, I would go about this a bit differently. I would create a static Union Query that combines all your tables and fields together (not in VBA). You wouldn't have any Criteria (WHERE clause) on this query, unless you know there is some condition that you will always use and never change.

To address tables which don't have all the fields, just add static placeholders right in your Union query. For example, let's say that we have two tables with the following fields:

TableA
-Name
-Nickname
-Age

TableB
-Name

So, our Union Query would look like this:

SELECT [TableA].[Name], [TableA].[Nickname], [TableA].[Age]
FROM TableA
UNION
SELECT [TableB].[Name], "" as [Nickname], 0 as [Age]
FROM TableB;

Now that you have your static UNION query built, you would just use VBA to create a new query using the static Union Query as its source, adding any Criteria you want, i.e.

Dim mySQL as String

mySQL = "SELECT [UnionQuery].* FROM [UnionQuery WHERE ..."

 

[DKaur]

You are right in thinking I can't have a single table rather than multiple ones. However I just did a union on all the tables, like you suggested, and it has taken 10 minutes. The result is a table with 15 million records. Do you think access can handle queries on this? Also, we get new years' data in new tables, would it be able to handle significantly more than 15 million records as the database gets larger?

Secondly, if I didn't want to union all the tables, do you think there exists a way to override the 'enter parameter value' on my code above? I've given it even more thought and could possible have an if statement with two differing SQL strings, since there are only two different types of structures to the tables, one without three particular fields and one with all the fields. Maybe and if statement to test one of the fields exist?

Thanks for all the help.

 

[Joe4]

What are you using to house all of this data? I hope it isn't Access. That amount of data will kill your performance. I hope it is something like SQL or Oracle (you can still use Access as a front-end).

With that amount of data, instead of trying to create the Union Queries in Access, I would probably recommend creating a view (query) directly on the database (i.e. SQL). You could then use a Pass-Through Query in Access to pass the SQL code of the query with criteria you want to SQL, and only return the records you need. That would give you much better performance.

 

[DKaur]

Unfortunately it is Access. I am very new to this and don't have 'SQL server' or Oracle. So a view query is an 'SQL server' thing, or do-able through Access?

 

[Joe4]

If you are talking about tens of millions of records, I predict you may run into issues if all your data is also stored in Access. Access has a 2 GB size limit. How big is your database now? Even if you keep it under 2 GB, having tens of millions of rows of data will certainly affect performance (you may find it slow).

 

[DKaur]

That explains a lot, yes - my database is just under 2GB. And so, some of my macros have stopped working with runtime error 3049, which I think is being caused by this 2GB limit.

 

[Joe4]

Yep, that would do it.

With large databases, it is also wise to Compact and Repair the database regularly (under "Manage"). This will get rid of the bloat and keep the size as lean as it can be.

It sounds like you may need to start investigating other back-end options to hold your data. You may want to look at something like "MySQL", which I believe is free. Not quite as robust as SQL, but more robust than Access.

Essentially, in all these scenarios, you would use something like SQL, Oracle, or MySQL to hold the data, and then link the data to Access tables. So then the data is not actually stored in Access, which keeps the size down. You can then write queries against these tables in Access just like you would for any other linked Access data table.

 

[DKaur]

I've been trying to Compact and Repair, but it keeps getting an error, describing the 2GB limit situation. I shall explore other options to hold data, thanks for all your help.