-1

In Windows 10 and MS Access 2010, I am running a vba function that executes a SQL query extracting records from a linked a table that is linked to Outlook. The query looks like this:

select * from  where LinkedTable.Field1 like '*string1*' and (LinkedTable.Field2 like 'string2*' OR LinkedTable.Field2 like 'string3*' OR LinkedTable.Field2 like 'string4*' OR LinkedTable.Field2 like 'string5*' OR LinkedTable.Field2 like 'string6*' OR LinkedTable.Field2 like string7*')

Before executing the query, I am refreshing the link to the table.

Access is crashing when running the function. Debugging leads to identifying the point of executing the query is when Access is crashing: Set rs2 = db.OpenRecordset(strQ)

Here is the code:

    Sub xyz()
    Dim db As DAO.Database
    Dim rs2 As DAO.Recordset 'this the sql query table
    dim rs1 as DAO.Recordset
set rs1=db.openrecordset("anothertable")
rs1.movefirst
do while not rs1.eof 

    strQ ="select * from  LinkedTable where LinkedTable.Field1 like '*string1*' and (LinkedTable.Field2 like 'string2*' OR LinkedTable.Field2 like 'string3*' OR LinkedTable.Field2 like 'string4*' OR LinkedTable.Field2 like 'string5*' OR LinkedTable.Field2 like 'string6*' OR LinkedTable.Field2 like string7*')"

    db.TableDefs(LinkedTable).RefreshLink
    Set rs2 = db.OpenRecordset(strQ)
    .

rs2.close
set rs2=nothing
rs1.movenext
loop
    .
    .
    En

d sub

I noticed that the memory usage goes up every time the statement Set rs2 = db.OpenRecordset(strQ)

is executed even though I am cleaning up. It is not released after cleaning up. What potential memory leak can this statement cause?

Thank you.

Dave Branson
  • 5
  • 1
  • 4

1 Answers1

0

The issue may be that your SQL statement is enclosed in 'smart quotes' or Unicode characters 0x201C & 0x201D:

”select * from ... string7*')”

As opposed to the standard double-quote, which is Unicode/ASCII character 0x0022:

"select * from ... string7*')"

You are also missing the data source for your select query:

select * from ??? where ...
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • Actually, the quotes and the From are not the issues because I have them correct in the code. I missed them in the excerpt. The function work sometimes but crashes at one point. – Dave Branson Jan 10 '19 at 23:15