How about this way:
Dim i As Integer
Dim strSQl As String
Dim rsTables As DAO.Recordset
strSQl = "Select Name from msysObjects "
strSQl = strSQl & "Where (((iif(Left([Name], 4) = 'msys',0,1))=1) and
((Type)=1));"
Set rsTables = CurrentDb.OpenRecordset(strSQl)
Dim MyArray() As String
While Not rsTables.EOF
i = i + 1
ReDim Preserve MyArray(i)
MyArray(i) = rsTables!Name
rsTables.MoveNext
Wend
rsTables.Close
Set rsTables = Nothing
Some tips here:
1. I don't know why, but While...Wend loops are faster than Do...Loop.
2. i starts out at zero, so increment it at the beginning of the loop.
3. ReDim will resize your array, but destroys all of its data unless you
also use the Preserve keyword.
4. You don't need a strTableName variable, because you can just copy the
value from the recordset right into your array slot.
5. The chemistry between i and MyArray is powerful. The freshly incremented
i and the freshly resized array mean that i will always point at the new
last item of the array.
6. Never ever let the interpreter be in charge of releasing your objects.
Any time you have to use the Set keyword to get an object, ALWAYS set that
object to Nothing when you're done with it. It's the call to "Set xxx =
Nothing" that tells the interpreter to call the IUnknown::Release() function
deep in the bowels of the COM plumbing. If you don't set your objects to
Nothing when you're done, you're almost guaranteeing memory leaks.
7. Just a matter of personal taste, but assigning SQL statements like the
one below have always helped me in the debugger. If I have the SQL already
formatted, it's easier to look at in the immediate window, and the query
engine doesn't care:
sql = "SELECT Name " & vbCrLf & _
"FROM MSysObjects " & vbCrLf & _
"WHERE Left$([Name], 4) <> 'MSys' " & vbCrLf & _
" AND Type = 1;"
8. The Left() function returns a Variant, not a String. The Left$()
function (with a dollar sign) returns a String, and is faster.
9. Maybe I'm just too obsessive-compulsive, but I like to declare variables
with Tabs between the variable name and the As keyword, so the data types
all line up:
Dim i As Integer
Dim sql As String
Dim rsTables As DAO.Recordset
--
Peace & happy computing,
Mike Labosh, MCSD
"I have no choice but to believe in free will."
