MS Access VBA Function (Part 4) – Write queries to a table

This is really great for address matching – take a clean source of information and create a set of update queries looking for strings that will be run on a table with less than clean data.

Public Function CreateTableofSQL()

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As DAO.Database
Dim SQLString As String
Dim LCounter As Long
Set db = CurrentDb

LCounter = 1
While LCounter < 3000

LCounter = LCounter + 1

Set rst1 = CurrentDb.OpenRecordset("SELECT TestTest.XStreetname, TestTest.XFlag, TestTest.Length, TestTest.XStreetname2, TestTest.XFlag FROM TestTest WHERE (((TestTest.XFlag) Is Null Or (TestTest.XFlag) = 0)) ORDER BY TestTest.Length, TestTest.XStreetname2;")

SQLString = "UPDATE T002BCAPR SET T002BCAPR.XStreetNameQuery = '" & rst1!XStreetname2 & "' WHERE (((T002BCAPR.LOCADDRESS1) LIKE '*" & rst1!XStreetname2 & "*'));"

rst1!XFlag = 1

Set rst2 = CurrentDb.OpenRecordset("T008SQL")
With rst2
rst2!SQL = SQLString
End With

End Function

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in Address Matching, MS Access, VBA Code MS Access. Bookmark the permalink.