Problem With SQL Obtaining MsysObjects

Hello,

I've got a workaround for the SQL of obtaining a count of tables matching a certain name-style. Ideally, I want to use the Like function (sql2) as it codes more simply, but found that the query doesn't work. As a workaround, I rewrote with a string comparison using the Left function sql1.

My question is: What is wrong with sql2?

When I paste the SQL into the Query Designer it works, yet when I debug in module code form, it fails.

Any ideas?

Regards.



Private Function getTableName(strName As String) As String

'---------------------------------------------------------------------
' Purpose
' This function is used to create a new table name. First it looks up
' all the table names in the database matching the passed in variable
' name, and then returns an incremented version of the name.
'---------------------------------------------------------------------
' Returns
' Table name as string.
'---------------------------------------------------------------------

Dim sql1 As String, sql2 As String, rst As New ADODB.Recordset, n As Byte

' Get number of characters for passed-in variable.
n = Len(strName)


sql1 = "SELECT Name FROM MsysObjects WHERE left(Name," & n & ")=left('" & strName & _
"'," & n & ") AND (Type=1)"

sql2 = "SELECT Name FROM MsysObjects WHERE (Name Like '" & strName & "*') AND (Type=1)"

rst.Open sql2, CurrentProject.Connection, adOpenKeyset

Debug.Print sql2
Debug.Print rst.RecordCount

If rst.RecordCount >=1 Then
getTableName = strName & rst.RecordCount + 1
Else
getTableName = strName
End If

rst.Close
Set rst = Nothing

End Function