TableDef a user-defined type!?

[BigTeeJay]

I found some code in some of the postings on this site,
such as...

  Dim tdf as TableDef
  Dim fld as Field
  Dim prp as Property

  Set tdf=currentDB.TableDefs("OldTable")

And I was hoping to be able to use a slight variation to
get the names of the fields in a given table...

  Dim tdf as TableDef
  Dim fld as Field

  Set tdf = currentDB.TableDefs("SomeTable")

  For Each fld in tdf
    'add fld.name to string array
  next

My problem is that VBA in Access isntr ecognizing "TableDef"
as valid, it says "User-defined type not defined".

Are you using some addin that I must be missing? ADODB is
always available correct? I shouldnt have to add anything
else right? Any ideas?

Regards,
Travis Johnson

 

[BigTeeJay]

I found what my problem was, I guess I needed to add a ref to DAO 3.6 (which is bad, I dont know if the systems that my DB/app are going to run on will have the needed components/updates now).

Recommendations?
Travis

 

[930driver]

Access 2000 and later versions default to ADO, so you have to explicitly set a reference to DAO if you want it. Since to distribute an Access 2000 database your users will also have to have Access 2000 you should be OK. If they don't, you could get the developers version and distribute your database with Access Run-time.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[cmmrfrds]

You can access tables in ADO if you want. Here are a couple of examples that would get you started on using ADO for database DDL needs.

I have used this function for different needs so that is why there is exit function in different spots.
Function catalogInfo()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Set cg.ActiveConnection = CurrentProject.Connection

tb.Name = "Test"
tb.Columns.Append "col1", adInteger
tb.Columns.Append "col2", adVarWChar, 50
Debug.Print "table = "; cg.Tables("Test&quot.Name
cg.Tables.Append tb
Exit Function

Set cg.ActiveConnection = CurrentProject.Connection
Set tb = cg.Tables("dbo_categories&quot
Debug.Print "table name = "; tb.Name
Dim cl As Column
Dim pp As Property
Debug.Print "column = "; tb.Columns("Description&quot.Properties("default&quot.Value
Exit Function
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
''Debug.Print "type = "; cl.Type
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value

Next
Next

End Function

USING SCHEMA Views for access table information

Function TestTableColumn()

Dim cn As New Connection, cn2 As New Connection
Dim rs As Recordset, rs2 As Recordset
Dim connString As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\bigtuna\Databases\Bank.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
cn2.Open connString

Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "table&quot)

While Not rs.EOF
Debug.Print rs!table_name
Set rs2 = cn2.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "" & rs!table_name & "&quot)
While Not rs2.EOF
Debug.Print " " & rs2!column_name
rs2.MoveNext
Wend
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

End Function