table

[marcsirois]

I've been working with MS Access for 2 days now, so please bear with me
if I don't know all the lingo and technical terms.

I was assigned to work on an existing Access database, which contains
about a dozen Linked ODBC Tables (from a SQL Server database), and one
"regular" table. By regular I mean it's native to Access, and not tied
to any outside sources - let's call this table "MainTable". The Access
database contains forms, reports and queries, all based on "MainTable".

Obviously, "MainTable" is derived from the linked ODBC tables right?
Right??? But how the heck can I see the SQL statement used to populate
"MainTable"?? At some point, someone had to write some sort of a SQL
statement to query the data from the sql server tables and throw it in
the "MainTable". Is there something I'm not understanding here? I
just want to see where the data from MainTable comes from. Also,
provided the data does come from the SQL Server database, is there a
real-time data update to Maintable whenever a modification to the SQL
Server tables is made?

Basically, I want to see the SQL Statement used to populate MainTable.
When I have this, I can tweak the sql statement to my liking, and
modify the query to refine the data.

Please help me!

Thanks,
Marc

[Rich P]

If you have ODBC linked tables you can use the DAO tabledef object to
check the detail of the connection like this:

Dim DB As DAO.Database, tdf As DAO.TableDef
Set DB = CurrentDB
Set tdf = DB.TableDefs("linkTbl1")
Debug.Print tdf.Connect

this returns something like:
ODBC;DSN=someDSN;APP=Microsoft Office
2003;WSID=workstationname;Database=someSqlSvrDB;Tr usted_Connection=Yes

The source table on the sqlSvr could be either a regular table (there is
no sql here) or a view (which would be based on a regular table), Only
forms and reports can be based on stored procedures.

To change anything underlying the linked tables you would have to go to
the sql server. You could use Enterprise Manager or Query Analyzer
(sql2000) or Management Console (sql2005).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

"marcsirois" <ma********@gmail.comwrote

and one "regular" table. By regular I mean it's
native to Access, and not tied to any outside
sources - let's call this table "MainTable". The
Access database contains forms, reports and
queries, all based on "MainTable".

Obviously, "MainTable" is derived from the
linked ODBC tables right? Right???

There's nothing in what you have written so far to support this conclusion.
It is not necessarily so, and would be (at least) an "unusual" way to design
the datastore.

But how the heck can I see the SQL statement
used to populate "MainTable"?? At some point,
someone had to write some sort of a SQL state-
ment to query the data from the sql server tables
and throw it in the "MainTable".

It's not necessarily true that the MainTable was populated from SQL Server
tables; therefore, it's not necessarily true that you could see an SQL
Statement or Query. Even if it were populated from SQL Server tables, it
could have been accomplished manually.

Is there something I'm not understanding here?

If that table's Source was an SQL Server table, the normal approach would be
to link a TableDef to the SQL Server table, or perhaps create a Query using
one or multiple linked tables as data sources for the Query.

I just want to see where the data from
MainTable comes from. Also, provided
the data does come from the SQL Server
database, is there a real-time data update
to Maintable whenever a modification to
the SQL Server tables is made?

No, there is no provision for such an update. That is why such tables are
usually linked directly to the SQL Server tables.

Basically, I want to see the SQL Statement
used to populate MainTable. When I have this,
I can tweak the sql statement to my liking, and
modify the query to refine the data.

I once worked with an old-school marketing rep who had a saying "No matter
how much you want to sell purple dogfood, if the dogs won't eat purple
dogfood, no one's going to buy it." His point was, of course, that your
wanting something, no matter how much you want it, isn't necessarily going
to make it so.

Fact is, you are making an assumption which may or may not be true -- we
don't know; we can't see your database, and if we could and the MainTable
were derived from SQL tables, we still might well not be able to discover
the SQL because it isn't necessarily part of the database you have in hand.

Please help me!

The help you want may not be possible. Certainly we do not have enough
information to be of assistance.

Larry Linson
Microsoft Access MVP