How to use VBA to open any table in a single form

[SteveStew44]

Hello AW!

I am using Access 2003. I would like to be able to open a versatile form that can open any selected table in data sheet form. Currently I am using Command Button with the names of the tables as the trigger. I need some help with the VBA that would change the form's Record Source to a selected table and add all the fields of that table to the form on open. I figured that I can use the OpenArgs to get the form Record Source to be assigned, but how do I assign all the fields of the table to be dropped into the form? Any guidance would be appreciated.

Thanks!
Steve

 

[MarkK]

That's not really how you use Access. Obviously you can do that if you feel like it, or if you see some educational value in writing the code, but I would design a form for each table.

 

[SteveStew44]

Hi Mark,

Thanks for the reply. I have a distributable db with over 100 tables I need to access through the User's runtime version. I would like to be able to edit their table data with a Find and Replace feature, but do not have access to their tables unless I create 100 forms as you suggest to open each table in data sheet view. Any suggestions how I can get a form to open and insert all the fields in the form?

Thanks!
Steve

 

[smig]

Your design is wrong
You should split your db to fe/be. Your fe can be converted to mde running under the run time. Your be should be only password protected only.
Your users should never go directly to tables. Only the programmer should and he must have a full version of access.

 

[MarkK]

Yeah, put a bunch of hidden textboxes on a form. Name them Text0, Text1, Text2 in sequence. Pass in SQL select query in open args. Open a recordset, enumerate fields, programmatically unhide and set the ControlSource of each TextBox. Assign the recordset to the Recordset property of the form. Show the form in Datasheet view.

But 100 tables is almost certainly a design flaw. Almost certainly many of those table could be merged, and a single field used to tell them apart using queries.

Hope this helps,

 

[SteveStew44]

Hi Smig,

Thanks for the reply. I have a fe/be. The User does not have access to the tables. I want to Edit the tables through an admin section where only I can Edit. My question I am seeking an answer to is:

Is there a way through VBA to open a form that takes a selected table and inserts all the fields from that table into the form so it opens in datasheet view?

Thanks!
Steve

 

[SteveStew44]

Hi Mark,

Thanks for the reply. That is a great idea! I will try it. Btw, all the replies were criticizing the design of the db, so I beleive I was not clear on my question or the description of the problem. I apologize for any confusion, lol. Incidentally, the db genuinely uses over 100 tables and it has sold in the multi-millions of dollars and has personally netted me in the seven figure range. I don't say that to boast, but to let people know that there are many different ways to make a db, but as long as the User is happy, then mission accomplished!

Cheers!
Steve

 

[MarkK]

In most cases people who have 100 tables and want to do what you asked about, it would be a design problem. There are exceptions to every rule. Glad you found what you were looking for.

 

[Galaxiom]

Incidentally, the db genuinely uses over 100 tables and it has sold in the multi-millions of dollars and has personally netted me in the seven figure range.

I have to work with poorly designed commercially successful databases every day.

I wish eternal damnation upon their developers.

 

[SteveStew44]

Thanks again Mark!

You see, I feel so lame, I just learned that I do not even need a form to open up any table, ha ha! This will do it:

DoCmd.OpenTable TableName:=strTableName, View:=acViewNormal, datamode:=acEdit

Thanks to all who responded.

Cheers!
Steve

 

[SteveStew44]

Hi Galaxiom,

Yes, you are correct! I believe that my Db is far from great design, mostly because I am not a professional programmer. I am a businessman who has hired 3 brilliant programmers to build what I visioned. Fortunately, my programmers did not have the understanding of what the market needs, so we need each other.

I truly believe I am screwing up their design since I started tinkering with it, but I have enjoyed the process of learning a little about programming, I can't help myself, I love learning!

Btw, maybe the poorly designed Db's are needed because they create work for the higher level programmers? Damning the hand that feeds, seems a little harsh.

All the best to you,
Steve