looping on database to get fields and tables
thread
how can i get fields and tables by using ADO or DAO
is it posible by recordset and adodb connection
i'm talking about looping on database and not on a table that known
from the start
thats from advance
ralph
wrote:
What you are talking about comes under the general heading of "data
dictionary" services. Each database will provide its own services for
obtaining that information. So to adequately answer your question we
would need to know what database you are working with.
In general the ADO access library has little built-in DDL/DML
services. You can get some of these services using the ADO extension
library - ADOX. DAO on the other hand does, but is dependent on that
functionality being provided by the specific database ODBC driver.
But this is not chisel in stone. For example, if using SQL Server you
can use ADO with SQL to query system tables, the ADOX is not needed.
However, if using a Jet-formatted database then you will need to use
ADOX with ADO. DAO can be used to manage both.
-ralph
MikeD
"thread" <yani...@gmail.com> wrote in message
news:cd756c8a-4422-4def...@x11g2000yqc.googlegroups.com...
You can use ADO for this. It will work for both SQL Server and Access
databases. I would assume it would also work with any "database" to which
you connect using the Jet Engine (Excel files, flat files, etc.). I believe
Ralph is incorrect about this. I know this will work for Access because
I've done it. I can also verify that this works with SQL Server CE.
To get tables into a recordset, you use ADO's OpenSchema method and specify
the adSchemaTables parameter.
Dim rsSchema As Recordset
Set rsSchema = oConn.OpenSchema(adSchemaTables)
To get tables AND fields, again use OpenSchema but specify adSchemaColumns:
Set rsFields = oConn.OpenSchema(adSchemaColumns)
For more information about the resultset returned by each of these, see
ADO's help (it should be in MSDN Library, but it'll be for an older version
of ADO, possibly 2.1). Or, you can just add the recordset object variable
to VB's Watch window.
--
Mike
ralph
wrote:
> ... I believe Ralph is incorrect about this.
Not necessarily incorrect but damn unenlightening. <g>
The OP was asking about "data dictionary" services. I should have
stopped right there. but I side-tracked into DDL because I got hung-up
on ADO and DAO and their differences - DAO provides DDL services, ADO
does not.
Might be interesting to know, but not germane to the OP's question.
Had I read my response before clicking Enter I might have been of some
help as well as saved myself some embarressment.
ADO does supply a method to access a database's dictionary - and it is
OpenSchema.
-ralph