DAO TableDefs to ADO ... ..

[LittleSmudge]

The following is Access DAO

Dim db As Database
Dim tbf As TableDef
Set db = CurrentDb
For Each tbf In db.TableDefs

What does this translate to in ADO ?




The nearest thing I've come across in searching involves

Dim cnn As ADOX.Connection

But then I can't find which Reference library I need to link in that recognises ADOX


G LS

 

[DougP]

you need "Microsft ActiveX Data Objects 2.5"

also look at this code to get you started in ADO


Dim Conn2 As ADODB.Connection
Dim Rs1 As ADODB.Recordset

Dim SQLCode As String

Set Conn2 = CurrentProject.Connection ' <<<<Note same as CurrentDb

Set Rs1 = New ADODB.Recordset

SQLCode = &quot;SELECT * FROM [STOCK CODE LOOKUP] WHERE STOCK_CODE = '&quot; & Me![Part Number] & &quot;';&quot;
Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic

' Note: do normal rs1! or rs1. stuff from now on
'like Rs1!fieldname
' Rs1.addnew


' close it this way
Set rs1 = nothing
Set Conn2 = nothing DougP, MCP

 

[cmmrfrds]

Here is some code that adds a table in ADO and then modifies a column name.

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
Dim cl As ADOX.Column
Set cg.ActiveConnection = CurrentProject.Connection

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

'-rename a column
Set tb = cg(&quot;test&quot
Set cl = tb(&quot;col2&quot
cl.Name = &quot;col2aa&quot;

End Function


A couple of links

http://www.gab2001uk.com/visualbasic/sampledatabase/

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

ADO also has the Schema way of accessing information on objects must like using the Schema in SQL Server. An example of finding tables using Schema.

Function CheckForTables()

Dim cn As New Connection
Dim rs As Recordset, connString As String
connString = &quot;provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\bigtuna\Databases\MotorRepairDB.mdb;&quot; & _
&quot;Persist Security Info=False&quot;
cn.ConnectionString = connString
cn.Open connString
Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, &quot;table&quot)

While Not rs.EOF
Debug.Print rs!table_name
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

End Function

Find tables in MDB(or SQL Server) without using Schema
Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
Debug.Print &quot;table name = &quot;; &quot;-------&quot;; tb.Name; &quot;--------&quot;; tb.Type
Next