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</code>
<code>LCounter = LCounter + 1</code>


<code>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;")</code>

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

<code>
rst1.Edit
rst1!XFlag = 1
rst1.Update
rst1.MoveNext
rst1.Close

Set rst2 = CurrentDb.OpenRecordset("T008SQL")
With rst2
.AddNew
rst2!SQL = SQLString
rst2.Update
rst2.Close
End With
Wend


End Function