Month: June 2017

Clearly there is a problem with generating 66,000 queries and ramming each of them into the Query Database Window. Yes you got it, an MS Access database can only hold circa 32,000 objects (32,768 to be exact). I had been writing the query definitions to the system query definition table and this was making an elegant but pointless alphabetically ordered telephone directory out of the query database window before bombing out at the database limit. Defining programmatically more and more queries to be written to the query definition window was a revolution that ended as quickly as it had begun. A maximum limit I had previously never hit in all of the databases I had ever created, I hit in 1 hour. But how to run query lists longer than 32,000? Do I really need to break everything into separate databases with each complying with the 32,000 object limit? I felt there must be a better solution.

Then it hit me – I shouldn’t write the queries to the database window. Keep the queries in a table and call the queries from a function. That way the queries aren’t considered as objects in your natural sense to MSAccess but are run as queries when triggered from VBA. That way the limit on objects in a single database is the limit of data I can hold in a table. By linking to other tables that limit may even approach 2GB. That’s enough queries to keep me going for quite some time.

This is what I came up with

Be warned running thousands of queries takes time you might need to run this overnight or over several days hence why I have included a start datetime and end datetime to be shown in the message box on completion it is interesting to see how long 100s or 1000s of queries take to run. My queries can now potentially perform trillions of calculations all unattended by me.

Now I just want to run lots and lots of queries!!!

Public Function RunQueriesFromTable(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL = rstZ!SQL
DoCmd.RunSQL strSQL
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL update queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

UK Postcode extractor

Function GetPostCode(Optional AddressText As Variant) As String
Dim AddrTextLength As Integer, TempText As String, TempPicture As String
Dim PostCodePics(10) As String, PictureItemNum As Integer
Dim n As Integer, x As Integer

GetPostCode = "" ' default response if no postcode detected
If IsNull(AddressText) Then
Exit Function
End If

PostCodePics(1) = "XXNSNXX" ' alternative formats of postcodes
PostCodePics(2) = "XXNNSNXX" ' where X = alpha, S = space
PostCodePics(3) = "XNNSNXX" ' and N = numeric
PostCodePics(4) = "XNSNXX"
PostCodePics(5) = "XXNNXX"
PostCodePics(6) = "XXNNNXX"
PostCodePics(7) = "XNNNXX"
PostCodePics(8) = "XNNXX"
PostCodePics(9) = "XXNXNXX"
PostCodePics(10) = "XXNXSNXX"

AddrTextLength = Len(AddressText)

If AddrTextLength < 5 Then
Exit Function
End If
If AddrTextLength <= 9 Then
TempText = Trim(AddressText)
Else
TempText = Trim(Right(AddressText, 9))
End If

PictureItemNum = 0
TempPicture = "" ' build a picture of the format of current text
For n = 1 To Len(TempText) ' detect the type of each character
x = InStr(1, "1234567890 ", Mid(TempText, n, 1))
If x > 0 And x < 11 Then TempPicture = TempPicture & "N"
If x = 11 Then TempPicture = TempPicture & "S"
If x = 0 Then TempPicture = TempPicture & "X"
Next

For n = 1 To 10 ' compare the format of the current text
x = Len(PostCodePics(n)) ' against each of the post code pictures
If Len(TempPicture) >= x Then
If Right(TempPicture, x) = PostCodePics(n) Then
PictureItemNum = n
GetPostCode = UCase(Right(TempText, x))
Exit For
End If
End If
Next

If PictureItemNum > 4 And PictureItemNum < 10 Then ' insert space in the middle if not present
GetPostCode = Left(GetPostCode, Len(GetPostCode) - 3) & " " & Right(GetPostCode, 3)
End If

End Function

Here are a series of Functions that can be used to help in matching addresses between a dataset that is good (eg Assessors Street File) and a dataset that could be improved – eg a Customer Relationship Management System.

ADD and DROP Fields

Function AddCharColumn(TblName As String, FieldName As String)

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " CHAR(100);"

End Function

Function AddIntegerColumn(TblName As String, FieldName As String)

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " INTEGER;"

End Function

Function AddDoubleColumn(TblName As String, FieldName As String)

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " Double;"

End Function

Function DropColumn(TblName As String, FieldName As String) As Variant

DoCmd.RunSQL "ALTER TABLE [" & TblName & "] DROP COLUMN " & FieldName & ";"

End Function