Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Question with Openschema? 2
- Thread starter tansc81
- Start date
dougcranston
Technical User
tansc81,
No straight answer for you, however, I did do a quick search on Google and Devguru and hope the following helps you answer your question.
The few times I have researched ADO OpenSchema, I have not seen the Array(Empty, Empty, relation)
Suggest you first look and digest info at Devguru and then maybe the others provided below.
Set rsobject = connectionobject.OpenSchema (QueryType, Criteria, SchemaID)
Sample code "ADO Schemas to list tables & fields "
HOWTO: Use the ADO OpenSchema Method in Visual Basic
INFO: Programmer's Guide to Using ADO in Visual Basic
Hope this helps.
DougCranston
No straight answer for you, however, I did do a quick search on Google and Devguru and hope the following helps you answer your question.
The few times I have researched ADO OpenSchema, I have not seen the Array(Empty, Empty, relation)
Suggest you first look and digest info at Devguru and then maybe the others provided below.
Set rsobject = connectionobject.OpenSchema (QueryType, Criteria, SchemaID)
Sample code "ADO Schemas to list tables & fields "
HOWTO: Use the ADO OpenSchema Method in Visual Basic
INFO: Programmer's Guide to Using ADO in Visual Basic
Hope this helps.
DougCranston
-
2
- #3
Those are used for restrictions, or criteria set on the first parameter.
In your example, you have defined the query type as adSchemaColumns.
adSchemaColumns has these elements in the restriction columns list:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
It has 4 elements. Each element in the lists corresponds to an element in the array.
So, if you do not want any restrictions placed on any of these elements, you could leave the argument blank:
OpenSchema(adSchemaColumns)
Or use:
OpenSchema(adSchemaColumns, Array(Empty, Empty, Empty, Empty))
Let's say you wanted to place a restriction on one of the elements, say TABLE_NAME,
A certain database in the catalog is called XYZ, and there are 5 tables in the database, one of which is called MyTable.
If you want all records returned only info on the "MyTable" table, instaed of ALL 5 tables, you would use a restriction for the 3rd element:
OpenSchema(adSchemaColumns, Array(Empty, Empty, "MyTable", Empty))
Now you can only view the table "MyTable" and it's column, index, key structure, but not the other 4 tables in the database.
So, how to use this example:
Say you want to find out if a certain table in a database exists.
You may do something like this:
Public Function TableExists(conn As ADODB.Connection, ByVal sTableToFind As String) As Boolean
Dim rsADO As ADODB.Recordset
sTableToFind = UCase$(sTableToFind)
Set rsADO = conn.OpenSchema(adSchemaTables)
With rsADO
Do Until .EOF
If UCase$(.Fields("TABLE_NAME"
.Value) = sTableToFind Then
TableExists = True
Exit Do
End If
.MoveNext
Loop
.Close
End With
Set rsADO = Nothing
End Function
This works fine except it has one drawback:
To find just one table it needs to loop through all tables, making it lesss efficient, and very ineffecient if there are dozens of tables and hundreds, or even thousands, of fields in the database.
Not only does it take longer to get the structure in order to find out if one little table exists or not, the recordset is larger, wasting resources.
Let's use the same example but with a restriction on the TABLE_NAME.
Public Function TableExists(conn As ADODB.Connection, ByVal sTableToFind As String) As Boolean
Dim rsADO As ADODB.Recordset
Set rsADO = conn.OpenSchema(adSchemaTables, Array(Empty, Empty, sTableToFind, Empty))
TableExists = Not (rsADO.EOF And rsADO.BOF)
rsADO.Close
Set rsADO = Nothing
End Function
Now, not only is the proceedure alot smaller, and we do not need to pay attention to the CASE of the table name, but it will run alot faster.
It is alot more effecient.
In your example, you have defined the query type as adSchemaColumns.
adSchemaColumns has these elements in the restriction columns list:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
It has 4 elements. Each element in the lists corresponds to an element in the array.
So, if you do not want any restrictions placed on any of these elements, you could leave the argument blank:
OpenSchema(adSchemaColumns)
Or use:
OpenSchema(adSchemaColumns, Array(Empty, Empty, Empty, Empty))
Let's say you wanted to place a restriction on one of the elements, say TABLE_NAME,
A certain database in the catalog is called XYZ, and there are 5 tables in the database, one of which is called MyTable.
If you want all records returned only info on the "MyTable" table, instaed of ALL 5 tables, you would use a restriction for the 3rd element:
OpenSchema(adSchemaColumns, Array(Empty, Empty, "MyTable", Empty))
Now you can only view the table "MyTable" and it's column, index, key structure, but not the other 4 tables in the database.
So, how to use this example:
Say you want to find out if a certain table in a database exists.
You may do something like this:
Public Function TableExists(conn As ADODB.Connection, ByVal sTableToFind As String) As Boolean
Dim rsADO As ADODB.Recordset
sTableToFind = UCase$(sTableToFind)
Set rsADO = conn.OpenSchema(adSchemaTables)
With rsADO
Do Until .EOF
If UCase$(.Fields("TABLE_NAME"
.Value) = sTableToFind ThenTableExists = True
Exit Do
End If
.MoveNext
Loop
.Close
End With
Set rsADO = Nothing
End Function
This works fine except it has one drawback:
To find just one table it needs to loop through all tables, making it lesss efficient, and very ineffecient if there are dozens of tables and hundreds, or even thousands, of fields in the database.
Not only does it take longer to get the structure in order to find out if one little table exists or not, the recordset is larger, wasting resources.
Let's use the same example but with a restriction on the TABLE_NAME.
Public Function TableExists(conn As ADODB.Connection, ByVal sTableToFind As String) As Boolean
Dim rsADO As ADODB.Recordset
Set rsADO = conn.OpenSchema(adSchemaTables, Array(Empty, Empty, sTableToFind, Empty))
TableExists = Not (rsADO.EOF And rsADO.BOF)
rsADO.Close
Set rsADO = Nothing
End Function
Now, not only is the proceedure alot smaller, and we do not need to pay attention to the CASE of the table name, but it will run alot faster.
It is alot more effecient.
>for a particluar column of a relation
Oh, and to answer your specific question, you only need to place an additional restriction on the COLUMN_NAME item.
But, you could use the previous two examples as well by searching the Table, then the column.
However, why the additional work?
Oh, and to answer your specific question, you only need to place an additional restriction on the COLUMN_NAME item.
But, you could use the previous two examples as well by searching the Table, then the column.
However, why the additional work?
- Thread starter
- #7
CCLint,
In your given example of searching the adschematables for MyTable, can we have a search criteria entered?
In my case, I want to use the Table_Type property and return all the table names which are either LINK or TABLE.
How can I do that?
Thanks
In your given example of searching the adschematables for MyTable, can we have a search criteria entered?
In my case, I want to use the Table_Type property and return all the table names which are either LINK or TABLE.
How can I do that?
Thanks
You would need to pass TABLE or LINK as the last argument...but not both.
You can leave the argument Empty, then use the Filter Method:
rsado.Filter="TABLE_TYPE = 'LINK' OR TABLE_TYPE = 'TABLE'"
You have 5 Types avalable for a JET MDB:
ACCESS TABLE = MS Access system table
LINK = A Linked table to a non-ODBC data source
PASS-THROUGH = A Linked table to an ODBC data source
SYSTEM TABLE= A Jet system table
TABLE = your basic table
VIEW = A JET Query
You can leave the argument Empty, then use the Filter Method:
rsado.Filter="TABLE_TYPE = 'LINK' OR TABLE_TYPE = 'TABLE'"
You have 5 Types avalable for a JET MDB:
ACCESS TABLE = MS Access system table
LINK = A Linked table to a non-ODBC data source
PASS-THROUGH = A Linked table to an ODBC data source
SYSTEM TABLE= A Jet system table
TABLE = your basic table
VIEW = A JET Query
- Status
Similar threads
- Locked
- Question
