Creating combobox in Linked Table through VBA

[Brad]

I frequently create applications in Access with SQL Server or Oracle as the
backend and using linked tables either with a DSN or DSN-Less connection.
When I ship the application I have a procedure that creates the linked
tables after gathering the login credentials from the end-user. I have an
Access table containing all of the table names that are to be linked.

This works fine and all of the column definitions are read from the backend
database. I also pre-set the column widths by looping through the fields
and setting the width to the value stored in an Access table I ship
containing all of the column names and their widths (twips).

Now I want to create many comboboxes in the linked tdf and I'm thinking that
I need to ship an Access table containing the settings for each combobox
that are to be created. I believe I need to specify each of the properties
below.

TableName = Something
ColumnName = Something
DisplayControl = ComboBox
RowSourceType = Table/Query
RowSource = Select … from tablename where ...
BoundColumn = 1
ColumnCount = 4
ColumnHeads = TRUE
ListRows = 15
ListWidth = 7560twip
LimitToList = TRUE
ColumnWidths = 1440;3600;1080;1440

My question is wondering whether I'm going down the wrong path and also
wondering whether someone has already done this. It appears that the field
properties must be created since they do not exist for a regular text field.

The reason for shipping an unlinked application is because each end-user has
different connection settings and I cannot ship an mdb with the links
already created.

Brad

 

[John W. Vinson]

My question is wondering whether I'm going down the wrong path

I'd say yes, you are.

Table datasheets have VERY limited flexibility, offering both too much power
(changing column widths even to zero) and too little (restricted choice of
controls, no usable events) to the user.

Users should, in general, never even SEE a table datasheet.

Include Forms in your application as the tools of choice for users to interact
with the data.

John W. Vinson [MVP]

 

[Brad]

I absolutely will create forms for end users. My thinking was that by
setting combo boxes in the table, then dropping the fields onto a form
automatically generates a combo box. If I leave them as a plain text field
in the table, then everytime I use that field in a form, I have to take the
additional steps to convert the fields to a combo box.

Brad

My question is wondering whether I'm going down the wrong path

I'd say yes, you are.

Table datasheets have VERY limited flexibility, offering both too much power
(changing column widths even to zero) and too little (restricted choice of
controls, no usable events) to the user.

Users should, in general, never even SEE a table datasheet.

Include Forms in your application as the tools of choice for users to
interact
with the data.

John W. Vinson [MVP]

 

[Douglas J. Steele]

See http://www.mvps.org/access/lookupfields.htm at "The Access Web" for some
of the many reasons why most of us feel it's a mistake to put combo boxes in
tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[John W. Vinson]

everytime I use that field in a form, I have to take the
additional steps to convert the fields to a combo box.

I will admit it is *one mouseclick more* (you can click the Combo Box tool on
the toolbox and then drag the field from the field list to create a combo
box).

That's a one-time operation and IMO well worth the price.


John W. Vinson [MVP]