Passing tablename and fieldname as parameters? (2 Viewers)

[lumiere]

Is it possible to pass tablename or fieldname as parameters for a function or subroutine??

I am trying to make a form with text-boxes - txtInputTableName and txtInputTableField which will make a one dimensional array out of user specified field on any table.

Can someone help me regarding this. Thanks.

 

[theDBguy]

Hi. Without any more context, I can only say that it is possible to pass any string (table or field name) to a custom function.

If you can give us more details, we might be able to show you how.

 

[The_Doc_Man]

In general, you can pass an object of a known type into a subroutine "By Reference." Whether you can pass it by supplying only its name depends on scope (visibility) of the object with respect to the place where you want the code to work. For instance, if you have a subroutine in a general module, the only way you could pass a form's control by name is if you ALSO passed the form by name or object.

To know the right syntax for your case, as DBG suggests, you need to tell us just a smidge more about what you intended to do.

 

[lumiere]

Hi.
Thanks for your kind responses.

I tried to make a module with a function (or a subroutine) to copy data from any one or two fields of a table of user's choice to make it a single or two dimensional array. This form would contain a textbox or combobox to enter name of table and field to copy data from.

A button would be made to execute the code, which would pick tablename and fieldname from suitable controls using the function TableToArray, in this form. (However, I must mention that in the code below, I haven't incorporated the dimension of array. This is because I initially tried testing with 1D arrays but even then I an running into errors).

I also made another button on my main form which calls the subroutine ArrayToTable. The use of this subroutine is to use the array given out by earlier function and save the data in one or two columns of any table of user's choice.

The code used in the module is as below.

Option Compare Database
Option Explicit

Public Sub ArrayToTable(ArrayToExport As Variant, Dimensions As Integer, TableN As String, Optional FirstDLB As Control, Optional SecondDLB As Control)
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim j As Integer
    
    Set db = CurrentDb()
    Set td = db.TableDefs!TableN
    Set rs = td.OpenRecordset
    
    If Dimensions = 1 Then
        j = UBound(ArrayToExport) - LBound(ArrayToExport) + 1
        
        Do Until i >= j
            rs.AddNew
                rs!Column1 = ArrayToExport(i)
            rs.Update
            i = i + 1
        Loop
    Else
        If Dimensions = 2 Then
            Dim k As Integer
            For i = FirstDLB To UBound(ArrayToExport, 1)
                With rs
                    .AddNew
                For j = SecondDLB To UBound(ArrayToExport, 2)
                    .Fields(k) = ArrayToExport(i, j)
                    k = k + 1
                Next
                .Update
                k = 0
                End With
            Next
        End If
    End If
    
    rs.Close
    If IsObject(rs) Then Set rs = Nothing
    If IsObject(td) Then Set td = Nothing
    If IsObject(db) Then Set db = Nothing
End Sub


Public Function TableToArray(ArrayName() As Variant, ColumnT As String, SourceT As String)
    Dim rs As DAO.Recordset
    Dim intArraySize As Integer
    Dim iCounter As Integer
    Dim st, cs As String

    Set rs = CurrentDb.OpenRecordset(SourceT)
    If Not rs.EOF Then
        rs.MoveLast
        rs.MoveFirst

        intArraySize = rs.RecordCount
        iCounter = 0
        ReDim ArrayName(intArraySize)

        Do Until rs.EOF
            ArrayName(iCounter) = Nz(rs.Fields(ColumnT), 0)
            'myArray(iCounter) = rs.Fields(Column) <-- Original statement
            'However it creates an error if some records are empty in the field of the table
            
            iCounter = iCounter + 1
            rs.MoveNext
        Loop
    End If

rs.Close
If IsObject(rs) Then Set rs = Nothing
End Function

While running the code, error is seen in TableToArray function as Runtime error - 3078. Cant find the input table or column.

 

[arnelgp]

what is the exercise for?
you already have a recordset why use array?
array has limitation in size, if it exceed the limit your code will fail?

or you can just use an Insert query (from the logic of your code).

Insert Into table2 (field1) select field1 from table1;

 

[lumiere]

what is the exercise for?
you already have a recordset why use array?
array has limitation in size, if it exceed the limit your code will fail?

or you can just use an Insert query (from the logic of your code).

Insert Into table2 (field1) select field1 from table1;

I have tables with numeric data which is needed to be sorted in increasing or decreasing order, or in reverse order; tables with string which is to be sorted alphabetically. True, I can do it by SQL but was trying something new.
I tried the above code, it works if it is on the same form and not in separate module. However, then I used name of table and column of the source and destination of data. I also declared an array outside the subroutines and used that to move data. As I made it into a separate module it stopped working, so I thought I could use some help.
Thanks.

 

[arnelgp]

think of worst case, scenario.
do you think this will work with millions of record.
google RBAR.

 

[The_Doc_Man]

It would appear that you are trying to read a recordset into an array and/or write an array to a recordset or something like that, with the intent of manipulating the array once it is in memory. If this is an exercise, fine, we all like to experiment. But if this has some practical goal, tell us what problem you are trying to solve.

VBA is interpreted (or perhaps emulated), not compiled to machine-level code. You would have to first bring your table into memory using VBA. Then you would have to do your sorting in-memory, guided by VBA. Then write the result back to the table in yet another VBA loop. This is not nearly as efficient as some things you could use.

FIrst problem: As arnelgp is trying to tell you, big memory arrays can be a problem. Memory resident arrays, if they get big enough, will lead you to "out of resources" or "out of memory" or "exceeded virtual memory" errors (depending on exactly which methods you use.)

Trying to do something via VBA when you could do it via SQL is going the wrong direction on efficiency, not to mention that an in-memory sort on raw arrays is an n*(n-1) algorithm whereas if you had a recordset with the intended sort field already indexed, it would become a log<base-2> problem, which is far faster. AND better yet, SQL is implemented in machine code whereas VBA operations are not.

 

[arnelgp]

in addition, it might work for a single user.
for 2 or more users of your db, who is executing same code at same time, you'll get
various error messages since you need exclusive use of the table before
you can write to it.

 

[lumiere]

I plan to use queries and SQL in any project and prefer them over VBA.

However the real intent of the above exercise is pure academic. I am new to concept of arrays and am learning how to use them. I started from some YouTube videos and some codes from various sites. I downloaded a module from http://www.cpearson.com/excel/vbaarrays.htm. The codes I used (from above) worked fine but as there were multiple functions, I decided to make a module to import and export data to and from into arrays.
This resulted in some errors such - Can't find the input table or column, item not found in range etc..

 

[The_Doc_Man]

OK, then in light of your desire to experiment, the answer to your original question works like this...

The object model for a table is that the AllTables collection holds all tables. (Microsoft believes in simple names.) If you wish to pass a table by its name, then in the place where you use it you can reference AllTables(name) as though it were [name] and you would get similar results. Each table has a collection of Fields. If you pass the name of the field, you have to know the name of the table because Fields collections are specific to the table of reference. So this becomes AllTables(tablename).Fields(fieldname) to get to a field, where both tablename and fieldname can be passed by name to get the effect of [tablename].[fieldname] - and of course, from there you can delve into properties, because each field has properties that could be referenced indirectly this way.

So... YES, you can pass table names and field names. Forms have collections of Controls, so again you can pass control names - but they are specific to forms, which are in the AllForms collection. Hope you see the direction this is going. Also be it noted that if diddling with forms, you have to test whether the form is open first. If it is not, the form's contents, including the controls, are not accessible.

A word of warning... there is a group of tables, many of which start with MSys, that are the internal structural tables used by Access to define your schema, relationships, and many other factors. They are generically called "the system tables." By default, they do not show up in the navigation pane. DO NOT EVER write to one of those tables. You could potentially destroy the database. DO NOT EVER delete one of those tables. You WILL destroy the database.

There are also ways to pass the actual object (By Reference) to a subroutine. Not a name... but an instantiated object (technically, a pointer) variable. Access usually gives you several ways to get from point A to point B.

 

[jdraw]

lumiere,

You may like this article for ideas for academic pursuit. I'm sure many on this forum would like to see any activity and results you may want to share. You may develop a more positive view of vba.
Good luck.

 

[MajP]

Your code has some bigger issues, but it appears you have an undeclared variable
rs.Fields(Column)
You have no variable "Column" but do have "ColumnT"
Make sure all modules have "Option Explicit". You always want to declare all variables.
However I am not sure why that errors. If Column is an undeclared variable it should default to a variant 0. So it should be the first column.

 

[MajP]

Also when you loop are recordset you can also loop the fields if you want to get the value of each field. Easier than using a counter.

Dim Fld as dao.field

Do while Not RS.Eof
  for each Fld in rs.fields
      now you can loop each field in each record.
  next fld
  rs.movenext
loop

 

[MajP]

I see you are doing a 1 dimensional array for the column. I modified it a little, but had no issue with nulls. Not sure you want the sure to have to pass in an empty array in order to populate it.

Public Function TableToArray(FieldName As String, TableName As String) As Variant
    Dim rs As DAO.Recordset
    Dim iCounter As Integer
    Dim arr() As Variant
    Set rs = CurrentDb.OpenRecordset(TableName)
    If Not rs.EOF Then
        rs.MoveLast
        rs.MoveFirst
        ReDim arr(rs.RecordCount)
        Do Until rs.EOF
            arr(iCounter) = Nz(rs.Fields(FieldName), "")
            iCounter = iCounter + 1
            rs.MoveNext
        Loop
    End If
    TableToArray = arr
rs.Close
End Function

Public Sub TestTableToArray()
  Dim arr As Variant
  Dim i As Integer
  arr = TableToArray("City", "Employees")
  For i = 0 To UBound(arr)
    Debug.Print arr(i)
  Next i
End Sub