Better method to stop bloating the FE Database

[Ian Mac]

All,

I have search facility within my DB, the problem is I am returning the results to a TEMP table on the Client in order to buffer the data and wherever possible not create to much traffic to the BE.

Here is the code I have:

Public Sub SearchRecord()

Dim db              As DAO.Database
Dim rst             As Recordset
Dim qdf             As QueryDef
Dim strSQL          As String
Dim strNINO         As String
Dim strChbNO        As String
Dim strPostcode     As String
Dim strSurname      As String

Call DeleteTEMPSearch

If getSurnameVal() = "" Then
strSurname = ""
Else
strSurname = "(((tblApplicant.App_Surname) Like getsurnameval() & '*') "
End If

If GetNINOval() = "" Then
strNINO = ""
Else
strNINO = "AND ((tblApplicant.Nino) = '" & GetNINOval() & "') "
End If

If getPostcodeVal() = "" Then
strPostcode = ""
Else
strPostcode = "AND ((tblAddress.Post_Code) = '" & getPostcodeVal() & "') "
End If

If getChbVal() = "" Then
strChbNO = ""
Else
strChbNO = "AND ((tblChbNo.ChbNo) = '" & getChbVal() & "')"
End If

Set db = CurrentDb

strSQL = ""
strSQL = strSQL & "INSERT INTO TEMP_tbl_Search "
strSQL = strSQL & "SELECT tblWorklist.RecordID, "
strSQL = strSQL & "tblApplicant.App_Forename, "
strSQL = strSQL & "tblApplicant.App_Surname, "
strSQL = strSQL & "tblApplicant.Nino, "
strSQL = strSQL & "tblAddress.Address_Line_1, "
strSQL = strSQL & "tblAddress.Post_Code "
strSQL = strSQL & "FROM ((tblWorklist INNER JOIN tblApplicant "
strSQL = strSQL & "ON tblWorklist.Applicant1Id = tblApplicant.ApplicantId) "
strSQL = strSQL & "INNER JOIN (tblAddress INNER JOIN tblAddressLink "
strSQL = strSQL & "ON tblAddress.AddressId = tblAddressLink.AddressId) "
strSQL = strSQL & "ON tblApplicant.ApplicantId = tblAddressLink.ApplicantId) "
strSQL = strSQL & "INNER JOIN (tblChbNo INNER JOIN tblChbChild "
strSQL = strSQL & "ON tblChbNo.ChbNoId = tblChbChild.ChbNoId) "
strSQL = strSQL & "ON tblWorklist.EntId = tblChbChild.Entid "
strSQL = strSQL & "WHERE ((tblApplicant.App_Surname Like '" & getSurnameVal() & "*') "
strSQL = strSQL & strNINO & strPostcode & strChbNO & ");"

Set qdf = db.CreateQueryDef("", strSQL)
qdf.Execute

qdf.close
Set db = Nothing

End Sub

It's a bit sloppy but it's a work in progress.
Basically it insert the results into the TEMP table which is the Row Source for a Listbox on my Search from. The user then selects the one they wish.

Can anyone offer a better method of populating the Row Source/listbox which not bloat the DB.

I hope I've expained well enough.
Any further info needed, please ask.

Cheers,