cn is the ADO connection object ... I usually declare it as
VB Code:
Public cn As New ADODB.Connection
OpenSchema() method of the connection object returns a recordset whose fields depends on the arguments supplied. First argument, adSchemaColumns tells OpenSchema that we want to get schema information of columns, schema info for all columns is returned if none is specified in second argument... Second argument is an array that corresponds to adSchemaColumns fields TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. In the array, we didn't specify TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME or we passed Empty... we specified column name for COLUMN_NAME in Array().
Hence OpenSchema returns all schema records/info for column we specified with sColName variable. We need to assign to a reference the recordset returned by the connection object, hence Set rs = cn.... we can then manipulate the recordset in succeeding lines.
It returns schema information, that's all you need to know... its similar to what Rhino posted in that sense... but unlike Rhino's code where you have to know the name of the system table in the database that contains the schema information for the column, the OpenSchema method of the ado connection object spares you from that detail... you simply tell it what schema information you want based on the first argument, the second argument is only for additional criteria such as what we did specifying a column name for the adSchemaColumsn argument, we didn't need to know in which table the database stores the schema information.
... but unlike Rhino's code where you have to know the name of the system table in the database that contains the schema information for the column,...
So what's the big deal - it's your database name:
VB Code:
select table_name
from information_schema.columns
where column_name = 'user_id'
and table_catalog = 'database_name'
That simplest sql you can possible have (in this case) that you can execute via ADO will return all tables containing fields (or columns) named USER_ID.
So, what else would you need???????
Just FYI: information_schema.columns is a system view from master db was designed for this particular reason - get you quick info on specific column, table, etc...
What are you talking about?!
That is a system view and I did mention it - it belongs to a master database. So, if you won't specify the table_catalog (just like I originally posted) you will get list of all tables from the entire server from all databases that have that field you're interested.
To search specific database simply specify the table_catalog.
Common really... If you have something more constructive to say then I will be very happy to learn.
What are you talking about?!
That is a system view and I did mention it - it belongs to a master database. So, if you won't specify the table_catalog (just like I originally posted) you will get list of all tables from the entire server from all databases that have that field you're interested.
To search specific database simply specify the table_catalog.
Common really... If you have something more constructive to say then I will be very happy to learn.
Regards.
Please make this work for me (cant find similar system table) so I can learn... I'm very happy to do so.
And I never said you were wrong since he was using sql server, I simply pointed out the method is independent of the database type and knowledge of the system tables. I mentioned your method, using select, since he asked for an explanation... I thought it would be easier for him to understand the creation by openschema() of a recordset object if he can tie it up to your select query.
Last edited by leinad31; Nov 4th, 2006 at 08:12 AM.
I didn't need to know what database he was using... that was the other point I was trying to make.
OH WELL... This has dragged on to long... xor83, please don't consider the openschema method and use the select query instead so that RhinoBull can have his way and this thread can be put to rest.
... xor83, please don't consider the openschema method and use the select query instead so that RhinoBull can have his way and this thread can be put to rest.
@leinad31:
I'm sorry that you don't realize that there are other than MS Access database engines exist and each of them offers unique functionality. You have to learn how to take the most of it.
@xor83:
do youself a big favor and try not to limit your development to MS Access type thing...
@leinad31:
I'm sorry that you don't realize that there are other than MS Access database engines exist and each of them offers unique functionality. You have to learn how to take the most of it.
Just because I'm not much into proprietary approaches doesn't mean I am not familiar with other database formats... but who am I to get into a debate with a know it all?
LOL... How is it "proprietary" if it's been used by millions and you also have a source? MS SQL Server has been arround since 1989 and originally derived from SYBASE SQL Server. MS SQL, Oracle, SYBASE are pretty much industry's standards and in today's world you must know at least one (better two as most corps are using multiple) of those engines to survive in this business.
And I never said you're not familiar - you just seemed to play "stubborn" a bit.
Last edited by RhinoBull; Nov 4th, 2006 at 12:13 PM.
LOL... How is it "proprietary" if it's been used by millions and you also have a source? MS SQL Server has been arround since 1989 and originally derived from SYBASE SQL Server. MS SQL, Oracle, SYBASE are pretty much industry's standards and in today's world you must know at least one (better two as most corps are using multiple) of those engines to survive in this business.
And I never said you're not familiar - you just seemed to play "stubborn" a bit.
You can do their marketing for them.. no prob...
but in the end, I don't bother with such details on accessing the schema progmatically, you make it sound like its done on an hourly basis and everyone has to know that query by heart.
Molehills into mountains...
Last edited by leinad31; Nov 4th, 2006 at 12:26 PM.
... you make it sound like its done on an hourly basis and everyone has to know that query by heart...
You have to if you work with the product on a "daily" basis - it's the same as remembering the Mid$, Left$, Right$, InStr()...
You either know the product or you learn if you don't .
And comparing frequency of use of Mid$, Left$, Right$, InStr to frequency of querying the schema (which was the issue of the pointless debate you started) is silly.
Sorry, but you have to learn how to keep your temper and be civilized. RB
comment on a negative rep point. Wow, only he has the right to post sarcasm... self righteous til the very end.
Your were no better after post #13, you avoided this
And I never said you were wrong since he was using sql server, I simply pointed out the method is independent of the database type and knowledge of the system tables. I mentioned your method, using select, since he asked for an explanation... I thought it would be easier for him to understand the creation by openschema() of a recordset object if he can tie it up to your select query.
to make a fuss out of access.
Har har har mountains out of molehills.
Last edited by leinad31; Nov 5th, 2006 at 02:57 AM.
[RESOLVED] Searching field
Reply With Quote
Originally Posted by leinad31
