Hi!

How can i get the name of all obejcts (queries, tables) in an existing
Access-Connection
and their structure (for tables the fiieldnames and datatypes, for queries
the SQL-statement).

I want to do this via ActiveX-Script.

Thanks!

greets

chris

You can do this with ADOX. Try something like-

Dim oCatalog
Set oCatalog = CreateObject("ADOX.Catalog")
For Each oTable In oCatalog.Tables
oTable..
Next



--
Darren Green
http://www.sqldts.com

"Christian Weber" <christian.weber (AT) siemens (DOT) com> wrote

You are going to have to go into DAO and query the system catalogs from
there AFAIK (tabledefs). You can reference the DataSource property of your
Access connection to give you then name of the Access DB but that is about
all you can do with it for this.



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Christian Weber" <christian.weber (AT) siemens (DOT) com> wrote

Thanks!

How do i assign the "CreateObject("ADOX.Catalog")" to the connection?

greets

chris

"Darren Green" <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> schrieb im
Newsbeitrag news:uS5ygtAYDHA.1056 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

In article <bh87aq$ll4$1 (AT) news (DOT) mch.sbs.de>, Christian Weber
<christian.weber (AT) siemens (DOT) com> writes
Sorry, should have said, you can't use the connection directly, but you
can read off the properties from it and use it within the Catalog open
method. Here we grab a connection object and read off the filename from
it for use in the ADOX method.

Set oConn = DTSGlobalVariables.Parent.Connections("Conn name")

oCatalog.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
oConn.DataSource

MSDN provides a full ADOX reference, so have a look at that for more
detail of the objects and methods.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

In article <bha5d2$8gb$1 (AT) news (DOT) mch.sbs.de>, Christian Weber
<christian.weber (AT) siemens (DOT) com> writes
There is no open method I was guessing. I have rarely used ADOX, but as
I said it is fully documented in MSDN. After a quick browse of the
relevant topics I came up with this-

Option Explicit

Function Main()

' Declare variables
Dim oPkg
Dim oConn
Dim sConnString
Dim oCatalog
Dim oTable

' Get reference to current package
Set oPkg = DTSGlobalVariables.Parent
' Get reference to Access connection, by name
Set oConn = oPkg.Connections("Microsoft Access")

' Build Access MDB connection string using DTS connection
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
_
oConn.DataSource

' Create Catalog Object
Set oCatalog = CreateObject("ADOX.Catalog")

' Open Access MDB
oCatalog.ActiveConnection = sConnString

' Enumerate tables in catalog
For Each oTable In oCatalog.Tables
' Filter on user tables only
If oTable.Type = "TABLE" Then
MsgBox "Table Name: " & oTable.Name
End If
Next

Set oTable = Nothing
Set oCatalog = Nothing
Set oConn = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com