Help Displaying Tables in DataSheet Format

[Aladdin]

I have an MS Access form on which I have a listbox listing tables in
that database. I want to be able to click on any of those tables and
view its contents on the same form using subforms or any grid control.

I tried many grid controls (DBGrid, DataGrid, MSFlexGrid), the ADO
Data Control and everything I can think of, with no success. Here are
the contraints I faced:

(1) Populating any of the grid controls manually is too slow for my
application and takes a lot of effort to allow the user to edit the
table contents in the grid.
(2) Using subforms assumes I already know the table structure, which I
don't.
(3) Using ADODC won't let me connect to the same mdb file I am working
on.

Can you please help me find the most efficient (least overhead to
program and use) way to view the table contents in Dataseheet format
on the same form in MS Access ?

Your advise is greatly appreciated.

[MacDermott]

Couple of things you might consider:
1. Access versions 2000 and later let you use ADO to connect to the current
database using the CurrentProject.Connection object.

2. What is the motivation behind insisting that the tables be displayed
inside the form?
It would be quite simple to open the table in its own window (inside the
Access window, of course) using DoCmd.OpenTable.

3. Most programmers do not allow their users to work directly in the
tables.
You get much finer control if users work in forms.

HTH
- Turtle

"Aladdin" <an*****@yahoo.com> wrote in message
news:20**************************@posting.google.c om...

I have an MS Access form on which I have a listbox listing tables in
that database. I want to be able to click on any of those tables and
view its contents on the same form using subforms or any grid control.

I tried many grid controls (DBGrid, DataGrid, MSFlexGrid), the ADO
Data Control and everything I can think of, with no success. Here are
the contraints I faced:

(1) Populating any of the grid controls manually is too slow for my
application and takes a lot of effort to allow the user to edit the
table contents in the grid.
(2) Using subforms assumes I already know the table structure, which I
don't.
(3) Using ADODC won't let me connect to the same mdb file I am working
on.

Can you please help me find the most efficient (least overhead to
program and use) way to view the table contents in Dataseheet format
on the same form in MS Access ?

Your advise is greatly appreciated.

[Aladdin Nassar]

Thank you for your reply, MacDermott. Let me answer your questions to
clarify:

(1) I am using Access 2002 & 2003. I tried using
CurrentProjeect.Connection to the same mdb file I am developing in. It
refused connection saying the file was locked.

(2) I need to show the table's contents on the same form. Each record
has an address, and as the user traverses the table, they get to see the
map associated with that address, make any edits if necessary and
save/cancel before moving to the next record. Ideally, I would like to
use a subform that I can switch between single and mulitple record view
(Single vs. Dataseheet), but the problem is that the form assumes I know
the table's structure apriori. Opening multiple modal/non-modal forms
would confuse my users, unless you tell me how to make them look like
one form.

(3) The reason why I decided to use MS Access' runtime for development
instead pure VB in the first place, is the import/export functionality
that allows the users to import/export tables. That is why I import
tables into the local (same) mdb file. I don't need any system tables,
and even if I did, I can easily hide it from the user. That way the
user can still use the power of MS Access plus the added functionality
of mapping addresses no matter where they are stored.

I would really appreciate if you can direct me to the easiest way to
either get a subform to open any table in DataSheet format irrespective
of its schema, or any other way that allows read/write data-binding to
the underlying table, given the above information.

Aladdin Nassar

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

A few more thoughts:
(1) Is your database perhaps open in exclusive mode? (If it is open in
shared mode, there will be an .ldb file of the same name in the same folder
with your mdb; in exclusive mode, there is no .ldb file.) In exclusive
mode, I would not expect other use of the same connection to be permitted.

(2) Sorry to be so dense, but I still don't have a very good picture of what
you're trying to accomplish.
It sounds as if you are importing a variety of tables, with a variety of
table structures.
However, each table has address and map information.
I don't think you're going to be able to display maps (some sort of graphic,
presumably) in datasheet view.
Unless you have some way of identifying the name of the field containing the
graphic, you'll have a hard time displaying this field on a generic form,
either.

Until you mentioned the maps, I had this in mind:
Having selected a table name into a variable MyTable,
DoCmd.OpenTable "MyTable"
DoCmd.Maximize
If you're already working in maximized mode, the second line won't be
necessary.
Your table should display, taking up the entire screen.
The user can work in it, then close it when he's finished, returning to your
screen.
Of course a sophisticated user can get into mischief with this design, but I
don't see how it would be confusing for the beginning user you describe.

If you have a set of tables which won't change, you could create a separate
form for each one, then display the appropriate one in a single subform
control on your form, by changing the subform control's ControlSource
property.
But if you expect to be importing more forms, with as yet undefined table
structures, this won't help, either.
Or at least it won't solve the entire problem.

Switching between datasheet and single form view is not too difficult, I
think, using DoCmd.RunCommand acCmdDataSheetView and DoCmd.RunCommand
acCmdFormView.

(3) I'm not sure I follow your logic in rejecting VB, or selecting Access,
but I do hope that the above is of some use to you.
I might be able to advise you better if I had a clearer understanding of
where your tables are coming from and what use you want to make of their
data.

HTH
- Turtle

"Aladdin Nassar" <an*****@yahoo.com> wrote in message
news:40*********************@news.frii.net...

Thank you for your reply, MacDermott. Let me answer your questions to
clarify:

(1) I am using Access 2002 & 2003. I tried using
CurrentProjeect.Connection to the same mdb file I am developing in. It
refused connection saying the file was locked.

(2) I need to show the table's contents on the same form. Each record
has an address, and as the user traverses the table, they get to see the
map associated with that address, make any edits if necessary and
save/cancel before moving to the next record. Ideally, I would like to
use a subform that I can switch between single and mulitple record view
(Single vs. Dataseheet), but the problem is that the form assumes I know
the table's structure apriori. Opening multiple modal/non-modal forms
would confuse my users, unless you tell me how to make them look like
one form.

(3) The reason why I decided to use MS Access' runtime for development
instead pure VB in the first place, is the import/export functionality
that allows the users to import/export tables. That is why I import
tables into the local (same) mdb file. I don't need any system tables,
and even if I did, I can easily hide it from the user. That way the
user can still use the power of MS Access plus the added functionality
of mapping addresses no matter where they are stored.

I would really appreciate if you can direct me to the easiest way to
either get a subform to open any table in DataSheet format irrespective
of its schema, or any other way that allows read/write data-binding to
the underlying table, given the above information.

Aladdin Nassar

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mike Storr]

On 8 Feb 2004 20:14:57 -0800, Aladdin wrote:

I have an MS Access form on which I have a listbox listing tables in
that database. I want to be able to click on any of those tables and
view its contents on the same form using subforms or any grid control.

I tried many grid controls (DBGrid, DataGrid, MSFlexGrid), the ADO
Data Control and everything I can think of, with no success. Here are
the contraints I faced:

(1) Populating any of the grid controls manually is too slow for my
application and takes a lot of effort to allow the user to edit the
table contents in the grid.
(2) Using subforms assumes I already know the table structure, which I
don't.
(3) Using ADODC won't let me connect to the same mdb file I am working
on.

Can you please help me find the most efficient (least overhead to
program and use) way to view the table contents in Dataseheet format
on the same form in MS Access ?

Your advise is greatly appreciated.

Not sure if this works for your situation, but adding a subform control
onto the form, then when you want to show the table, set its SourceObject
to the database.TableDefs(TableName).Name
Worked for me on tables inside the same db, haven;t tried it on external
ones. It didn't even try to create a form out of it.

--
Mike Storr
veraccess.com

[Aladdin Nassar]

This is very close to what I am looking for - to use the SourceObject or
similar attribute to point to a different table on demand.

I tried what you propsed and failed. Here is what I did, please tell me
what am I going wrong:

(1) Referenced in MS DAO 3.6 Object library.
(2) Added a subform on my main form pointing to any dummy table.
(3) In VBA, I added the following code:
'----------- Begin Code --------------------
Dim db as DAO.Database
Set db = Currentdb
Me.subform.SourceObject = db.TableDefs("Table1").Name
Me.subform.SourceObject = db.TableDefs("Table2").Name
'----------- End Code ----------------------

Let me restate what I am trying to do in case others are reading this
post for the first time:

(1) I am developing a program in MS Access that allows users to import
addresses from text or any other files, process those addresses in the
local MDB file, then export them those addresses after processing them.

It does not matter what the processing is, because it seems to take
responders off a tangent. But if it helps, the processing is geocoding,
where I read it an address, and I generate a longitude/latitude for each
address in new/existing columns. Note that the column names imported
are not required to have exact column names and hence their is some
column mapping involved in the program. The processing needs to
find/map at least 4 columns for input: Address, City, State, and Zip
Code. And it writes to 2 new/existing columns called Longitude and
Latitude and other columns if needed.

(2) I need the user to visually see the table they just imported into
the local MDB file in DataSheet view IN A SMALL SUBFORM OR WINDOW ON THE
SAME FORM. Note the capital letters for emphasis. The reason for that
is that I need users to traverse their table one record at a time and
make changes as needed while SEEING the rest of the main form. The data
has to be bound to the rest of the form.

(3) My problem is as follows:
(a) I tried using subforms, but they have a fundamental flaw in
assuming that I know the table's columns apriori, i.e., I cannot assign
a subform to just any table the user can import. I would love to have
something like the RowSource attribute for listboxes that I can assign
programmatically.
(b) I tried using all kinds of grid objects like MSFlexGrid,
MSHFlexGrid, DataGrid, DBGrid and all of them have one or more
limitations that don't work for me. If you bind them to the data they
become extremely slow. And if unbound, you have to work out the
logistics of writing back the user edits to the underlying tables.
Also, I had a very hard time binding those grid objects to the
underlying tables in the same local MDB file using the ADODC control.

So, my question is very simple:

How do I get subforms or similar functionality to display ANY local
table imported by my users in DataSheet view IRRESPECTIVE OF THEIR TABLE
STRUCTURE OR COLUMN NAMES ?

Regards,

Aladdin Nassar
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mike Storr]

On 11 Feb 2004 18:48:59 GMT, Aladdin Nassar wrote:

This is very close to what I am looking for - to use the SourceObject or
similar attribute to point to a different table on demand.

I tried what you propsed and failed. Here is what I did, please tell me
what am I going wrong:

(1) Referenced in MS DAO 3.6 Object library.
(2) Added a subform on my main form pointing to any dummy table.
(3) In VBA, I added the following code:
'----------- Begin Code --------------------
Dim db as DAO.Database
Set db = Currentdb
Me.subform.SourceObject = db.TableDefs("Table1").Name
Me.subform.SourceObject = db.TableDefs("Table2").Name
'----------- End Code ----------------------

Let me restate what I am trying to do in case others are reading this
post for the first time:

(1) I am developing a program in MS Access that allows users to import
addresses from text or any other files, process those addresses in the
local MDB file, then export them those addresses after processing them.

It does not matter what the processing is, because it seems to take
responders off a tangent. But if it helps, the processing is geocoding,
where I read it an address, and I generate a longitude/latitude for each
address in new/existing columns. Note that the column names imported
are not required to have exact column names and hence their is some
column mapping involved in the program. The processing needs to
find/map at least 4 columns for input: Address, City, State, and Zip
Code. And it writes to 2 new/existing columns called Longitude and
Latitude and other columns if needed.

(2) I need the user to visually see the table they just imported into
the local MDB file in DataSheet view IN A SMALL SUBFORM OR WINDOW ON THE
SAME FORM. Note the capital letters for emphasis. The reason for that
is that I need users to traverse their table one record at a time and
make changes as needed while SEEING the rest of the main form. The data
has to be bound to the rest of the form.

(3) My problem is as follows:
(a) I tried using subforms, but they have a fundamental flaw in
assuming that I know the table's columns apriori, i.e., I cannot assign
a subform to just any table the user can import. I would love to have
something like the RowSource attribute for listboxes that I can assign
programmatically.
(b) I tried using all kinds of grid objects like MSFlexGrid,
MSHFlexGrid, DataGrid, DBGrid and all of them have one or more
limitations that don't work for me. If you bind them to the data they
become extremely slow. And if unbound, you have to work out the
logistics of writing back the user edits to the underlying tables.
Also, I had a very hard time binding those grid objects to the
underlying tables in the same local MDB file using the ADODC control.

So, my question is very simple:

How do I get subforms or similar functionality to display ANY local
table imported by my users in DataSheet view IRRESPECTIVE OF THEIR TABLE
STRUCTURE OR COLUMN NAMES ?

Regards,

Aladdin Nassar
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I may need to retract my last message, I can't duplicate what I last posted
although I'm positive it worked. It put a continous form inside the control
and even used that cheezy clouds and sky background that the AutoForm
wizard uses. I can't figure out why it won't work now. It's bugging me.
--
Mike Storr
veraccess.com

[Aladdin Nassar]

Does anyone out there know how to make a subform open any table
"on-the-fly" without knowing its structure ? Or is there any other way
to accomplish that same functionality without opening another yet form
using the DoCmd ?

Please help.

Aladdin Nassar

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bas Cost Budde]

Aladdin Nassar wrote:

Does anyone out there know how to make a subform open any table
"on-the-fly" without knowing its structure ? Or is there any other way
to accomplish that same functionality without opening another yet form
using the DoCmd ?

I can imagine this approach:

Upon some event, the table is examined, a form opened in design view and
for every field in the table a control is added to the form. The form
gets closed/saved. The subform's SourceObject property is now set to
this "new" form.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[Mike Storr]

On Thu, 12 Feb 2004 21:53:00 +0100, Bas Cost Budde wrote:

Aladdin Nassar wrote:

Does anyone out there know how to make a subform open any table
"on-the-fly" without knowing its structure ? Or is there any other way
to accomplish that same functionality without opening another yet form
using the DoCmd ?

I can imagine this approach:

Upon some event, the table is examined, a form opened in design view and
for every field in the table a control is added to the form. The form
gets closed/saved. The subform's SourceObject property is now set to
this "new" form.

This could become time consuming as the controls on the form would have to
be revmoved before the new ones could be created.
Would opening the table as read-only suffice? It's not a form, and it's not
within a subform control, but would be faster and simpler.

--
Mike Storr
veraccess.com

[Aladdin Nassar]

What did you have in mind regarding the read-only table ?

I tried using grid controls like MSFlexGrid, where I populate the grid,
one cell at a time, but it had a few problems: (a) the grid got very
slow for big tables, (b) I had to program the page navigation and
writing edits back to the underlying table manually.

So, the question still remains:

How do I get the user to view/edit/navigate the table they just imported
in tabular (DataSheet) view, as a small window on the main form, without
knowing the table's structure apriori (since they can import any file),
and while also seeing the rest of the form ?

Please note that:

- Opening the table in a separte window using the DoCmd, won't work,
unless it can be "attached" to the main form in such a way to make it
look like a window, i.e., the user still needs to see the main form
while navigating and editing the table.

- All tables are local (imported into the the local MDB file) but they
can have different totally different columns, which precludes the use of
static subforms.

- I am hoping to use a databound control so that I don't have to
populate the grid, page and record navigate, and write back edits on my
own. Ideally it is best to use a control that I can just point to a
local table using the RowSource property similar to the
listbox.RowSource = "TableName"

Please advise as I am still at loss.

Regards,

Aladdin Nassar
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bas Cost Budde]

Mike Storr wrote:

Upon some event, the table is examined, a form opened in design view and
for every field in the table a control is added to the form. The form
gets closed/saved. The subform's SourceObject property is now set to
this "new" form.

This could become time consuming as the controls on the form would have to
be removed before the new ones could be created.

Agreed. Maybe it is better to have controls for the maximum column count
case, and hide the ones you don't need for this table; and only alter
the controlsource properties.

Aladin, you like to try that approach?
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[MacDermott]

I've just tried out Mike Storr's method -
or something very close to it,
with good results.

I've got a subform control named MySub, and a combobox named MyCombo.
In the AfterUpdate of the combo, I have this:
MySub.SourceObject="Table." & MyCombo

I think this is what Mr. Nassar has been requesting.

HTH
- Turtle

"Aladdin" <an*****@yahoo.com> wrote in message
news:20**************************@posting.google.c om...

I have an MS Access form on which I have a listbox listing tables in
that database. I want to be able to click on any of those tables and
view its contents on the same form using subforms or any grid control.

I tried many grid controls (DBGrid, DataGrid, MSFlexGrid), the ADO
Data Control and everything I can think of, with no success. Here are
the contraints I faced:

(1) Populating any of the grid controls manually is too slow for my
application and takes a lot of effort to allow the user to edit the
table contents in the grid.
(2) Using subforms assumes I already know the table structure, which I
don't.
(3) Using ADODC won't let me connect to the same mdb file I am working
on.

Can you please help me find the most efficient (least overhead to
program and use) way to view the table contents in Dataseheet format
on the same form in MS Access ?

Your advise is greatly appreciated.

[Mike Storr]

DoCmd.OpenTable tblbob, acViewNormal, acReadOnly

This will open a table window that is locked for editing and adding, however
you will have little control over it while it is open. Bas's last suggestion
would be nicer, provided you know in advance all the tables that can be
displayed. Hide the unused columns by using Me!controlName.ColumnHidden =
True.
Mike Storr
www.veraccess.com
"Aladdin Nassar" <an*****@yahoo.com> wrote in message
news:40*********************@news.frii.net...

What did you have in mind regarding the read-only table ?

I tried using grid controls like MSFlexGrid, where I populate the grid,
one cell at a time, but it had a few problems: (a) the grid got very
slow for big tables, (b) I had to program the page navigation and
writing edits back to the underlying table manually.