Is it Possible to implement Dynamic changes to a Table based on database's tablelist?

[RLindahl]

Greetings,

My apologies for the poor english in the title, 85 characters was insufficient to ask my question.

I have a table which I'm using for a custom switchboard. This table has three columns; ID, Name, and Argument.
The "Name" column holds the names of tables and reports. The "Argument" column is the name of the form or report to open. What I am trying to do is

1). Populate the "Name" Column with the list of tables and reports in my database automatically. I can use a Schema(ADO) to get the tables but I don't know how to get the reports.

2). I need the "Name" Column to also have the proper "Argument". If a table name changes, then using the TableSchema will give me a different list order(since the tables are listed alphabetically) .

Is this possible? Or am I better off making the switchboard table a static list and manually changing the "Name" column if a table/report's name is changed in the DB?

A side note:

The buttons on my switchboard dynamically read the "Name" and "Argument" fields of a record so changes will reflect automatically on the button.

Thank you for your Time and Assistance,

RL

[ADezii]

It is a simple matter to retrieve a list of all Reports in a Database and populate a Table with their Names using the AllReports Collection:

Dim obj As AccessObject
Dim dbs As Object
 
Set dbs = Application.CurrentProject
 
For Each obj In dbs.AllReports
  CurrentDb.Execute "INSERT INTO Table1 (Name) VALUES ('" & _
                     obj.Name & "')", dbFailOnError
Next obj

[NeoPa]

CurrentProject( ).AllReports() will list all the Report objects and CurrentDb().Tab ledefs() will list all the Tables (TableDef objects). I don't see from your explanation (which is quality IMO) how you plan to tie the [Argument] value to a [Name] automatically. It's all very well thinking of how to do something in code, but first you need a clear idea of exactly what it is you intend to achieve. That part seems to be missing.

PS. Thanks for the effort to make the title make sense. It wasn't easy in the circumstances, but you did as good a job as possible.

[RLindahl]

It is a simple matter to retrieve a list of all Reports in a Database and populate a Table with their Names using the AllReports Collection:

Dim obj As AccessObject
Dim dbs As Object
 
Set dbs = Application.CurrentProject
 
For Each obj In dbs.AllReports
  CurrentDb.Execute "INSERT INTO Table1 (Name) VALUES ('" & _
                     obj.Name & "')", dbFailOnError
Next obj

Thank you for the reply. Is there a way to list all Reports using ADO(or DAO for that matter)? I am already using ADO for the tables which is why I am wondering. If not I guess I will just rework things.

Thank you for your time and assistance.

RL

[RLindahl]

CurrentProject( ).AllReports() will list all the Report objects and CurrentDb().Tab ledefs() will list all the Tables (TableDef objects). I don't see from your explanation (which is quality IMO) how you plan to tie the [Argument] value to a [Name] automatically. It's all very well thinking of how to do something in code, but first you need a clear idea of exactly what it is you intend to achieve. That part seems to be missing.

PS. Thanks for the effort to make the title make sense. It wasn't easy in the circumstances, but you did as good a job as possible.

Thank you for your Reply. I tried my best to make the question and post as concise as I could. I appreciate the assistance I have received and don't wish to waste people's time. :)

The reports have the same [Name]s as their [Argument]s so I should be able to just add the [Argument] at the same time as the [Name]. As for the table [Name]s and the [Argument]s I do not know. I am not sure it is possible. I have been racking my brain the past few days with this.

I am thinking that I would have to somehow store the table name and the form name then when the table name changes transfer the form name from the old connection to the new one. Perhaps utilizing a string concatenated from Name and Argument then take the Argument part to concatenate to the new Name. How to figure out when the name changes would be another issue.

Even if I can find a way to tie them together, changing the table name messes with the table's form. So I would also have to find a way to make the forms use the updated name too.

In your opinion is this feasible?

Thank you for your time and assistance,

RL

[NeoPa]

RLindahl:
In your opinion, is this feasible?

Not from what you've said so far no. Until you have a clear logical understanding of how things fit together it is not possible to put that understanding into code. There appears to be no clear logic either for matching table names to arguments or for matching old table names to new table names whenever they are changed. Without either, I fear there is no logical path even to try to follow :-(

[RLindahl]

Not from what you've said so far no. Until you have a clear logical understanding of how things fit together it is not possible to put that understanding into code. There appears to be no clear logic either for matching table names to arguments or for matching old table names to new table names whenever they are changed. Without either, I fear there is no logical path even to try to follow :-(

Well at least I learned something new even if I could not do what I had hoped. Thank you very much for the assistance and insights.

RL

[NeoPa]

I know I can speak for ADezii when I say it was our pleasure :-)

[ADezii]

Is there a way to list all Reports using ADO(or DAO for that matter)?

You can actually accomplish this using no External References whatsoever:

Dim intCtr As Integer

With CurrentDb
  For intCtr = 0 To (.Containers("Reports").Documents.Count - 1)
    Debug.Print "Report#" & CStr(intCtr + 1) & ": " & _
                 .Containers("Reports").Documents(intCtr).Name
  Next
End With

OUTPUT:

Report#1: rpt2weeks
Report#2: rptCirclesDemo
Report#3: rptCirclesDemo_Good
Report#4: rptOrderDetails

[NeoPa]

Sorry ADezii, but CurrentDb(), though it is available from Access.Applicat ion, is a method that returns a Database object defined by the DAO library. Thus every object within there is also DAO relative.

I suspect you've tested this, and it probably does work without the reference (as many object references can), but as it uses DAO objects so heavily the reference, though maybe not absolutely necessary, would certainly be desirable.