1

I am retrieving table names using ADOX, but any tables that are within a schema other than DBO are not listed.

Sub ListTablesADOX()
  Dim Conn As New ADODB.Connection

  'Open connection you want To get database objects
  Conn.Provider = "MSDASQL"
  Conn.Open "DSN=...;Database=...;", "UID", "PWD"

  'Create catalog object
  Dim Catalog As New ADOX.Catalog
  Set Catalog.ActiveConnection = Conn

  'List tables And columns
  Dim Table As ADOX.Table
  For Each Table In Catalog.Tables
      Debug.Print Table.Name
  Next
End Sub
Richard Briggs
  • 423
  • 4
  • 14
  • Please try to connect with `Conn.Open ("Provider=SQLNCLI10.1;Data Source=;Integrated Security=SSPI;Initial Catalog=")`. (or use other provider than SQLNCLI10.1). Maybe it's provider-specific issue? Or it'll behave differently with SSPI... – AdamL Feb 17 '14 at 12:29
  • Thanks, but it seems no matter how I connect, only objects in the default (dbo) schema are listed. Are you able to see objects in other schema's? – Richard Briggs Feb 17 '14 at 12:37
  • Yes, with above method it lists all schemas I see with Management Studio. – AdamL Feb 17 '14 at 13:03
  • Ah, so if you create a new schema and move a table into that schema, the table is still listed using the above code? – Richard Briggs Feb 17 '14 at 13:42
  • Looking at ado method OpenSchema, that looks like i can specify a schema name. – Richard Briggs Feb 17 '14 at 14:18

1 Answers1

1

I dropped using ADOX, re-coded all my get catalog info procedures using ADO OpenSchema methods instead, I couldn't get ADOX to anything beyond the dbo schema.

Richard Briggs
  • 423
  • 4
  • 14