Redim an optional arguement?

[Vita]

This is probably a dumb question and I think I know the answer but I am curious if there is something I could not find.

First here is the chunk of code I am trying

Public Function TableLinkOkay(Optional strTableName As String) As Boolean
    Dim TLOqdf As DAO.QueryDef, TLOrs As DAO.Recordset, strTLOSQL As String, FullCycle As Boolean
    
    If strTableName = "" Then
        FullCycle = True
        Dim item As Variant
        ReDim strTableName(10) As String
        strTableName(0) = "Table0"
        strTableName(1) = "Table1"
        strTableName(2) = "Table2"
        strTableName(3) = "Table3"
        strTableName(4) = "Table4"
        strTableName(5) = "Table5"
        strTableName(6) = "Table6"
        strTableName(7) = "Table7"
        strTableName(8) = "Table8"
        strTableName(9) = "Table9"
        strTableName(10) = "Table10"

(I omitted the extra code as I don't see it relevant to the variable/array discussion and didn't want to cause extra confusion)

When I do ?TableLinkOkay() in the immediate window I get the error "Expected Array" which I believe is due to the argument not being declared/initialized.
I have also tried rewriting it to be

Dim strTableName(10) As String

Which then tells me there is a duplicate declaration in the current scope which I expected.

I know the easy fix here would be to just create a new variable instead of trying to reuse the optional one but for the sake of learning and curiosity is there anyway of doing what I am trying to accomplish?

The objective of the optional argument is if supplied it will only test that table, if ignored/not supplied then it will test all of the tables in the redim/fullcycle.

[Micron]

I believe is due to the argument not being declared/initialized

No, it's because you declared a variable as a string, then try to use it to create an array. No can do.
Without the rest of the code it isn't clear whether or not you even need the Boolean, but maybe forget that for now. Try declaring another name as the variable for the array and use that. Using the (0, 1, 2) method as you are you don't need to Redim at all. Just declare as Dim aryMyArray(10) and fill it one item at a time.

A better method for checking links is often just to loop over the non-system tables, or a list of tables in a table.

[Vita]

No, it's because you declared a variable as a string, then try to use it to create an array. No can do.
Without the rest of the code it isn't clear whether or not you even need the Boolean, but maybe forget that for now. Try declaring another name as the variable for the array and use that. Using the (0, 1, 2) method as you are you don't need to Redim at all. Just declare as Dim aryMyArray(10) and fill it one item at a time.

A better method for checking links is often just to loop over the non-system tables, or a list of tables in a table.

Oh, I see! I know I can just create a new array but out of curiosity is there anyway I can use the same one? I'm more asking for a way to make it work.
Maybe redim it as a integer prior to making it an array? I don't even know if it is possible to change a variables type using redim.

I do need the boolean. The function would take a string that is the name of the table you want to check the connection of by grabbing 1 record and then returning true(can connect) or false(couldn't connect).
I am using only passthroughs to get data from a SQL server and I want to use this function to check for the connection. I admit it is kinda pointless.

I don't think cycling through non-system tables would work since its only passthroughs? The list of tables might work.

I realize its pointless with modern day computers to worry about using too many variable but I still like to practice ways to keep my variable usage to a minimum.

[Micron]

I'm not sure you know the difference between Dim and Redim. Dim Dimensions a space in memory for a variable, which then holds something, be that a value or an object (including arrays). Redim resizes an array so there's really no connection between the two concepts. If you need a variable for string/number values and you need an array, there's no getting around using separate variables unless maybe you made the variable a variant, and that would not be good coding practice.

[Vita]

I'm not sure you know the difference between Dim and Redim. Dim Dimensions a space in memory for a variable, which then holds something, be that a value or an object (including arrays). Redim resizes an array so there's really no connection between the two concepts. If you need a variable for string/number values and you need an array, there's no getting around using separate variables unless maybe you made the variable a variant, and that would not be good coding practice.

ohhhh Okay thank you for clarifying that. Sounds like I will just go with the original idea and rename the variable then.