473,320 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Opening Schema

Hi

I am using MS Access 2000 and want to open the Schema to know about
Table's structures like column names, data types, primary keys etc.

In SQL Server, we use OpenSchema method of Connection object,
in Oracle, we use "desc table" query,
in MySQL, we use "describe table" query,
in MS Access, we use ???????.

I found a method in this groups to use following query
"SELECT * FROM INFORMATION_SCHEMA.TABLES" for all tables' schema
but it gives following error

"could not find file 'C:\Program Files\Microsoft Visual Studio\VB98\
INFORMATION_SCHEMA.mdb'"

Does this database file gets installed by default or require custom
configuration or this is a wrong method??

HELP

Thanks
Regards
Nov 13 '05 #1
3 14131
Jet does not provide for access to a textual representation of the schema. I
don't know if it provides API access via ADOX yet (try it and find out), but
it does privovide API access via DAO, and I've used it in real world
applications before.

Look at the DAO help or on MSDN for more details. You'll see that there are
collections of Tabledefs, Querydefs, Relations, etc. You might have to look
at the Containers and Documents collections to get some of the information you
may need, but it's all there in one form or another.

On 26 Jul 2004 02:39:25 -0700, at*******@hotmail.com (Atif) wrote:
Hi

I am using MS Access 2000 and want to open the Schema to know about
Table's structures like column names, data types, primary keys etc.

In SQL Server, we use OpenSchema method of Connection object,
in Oracle, we use "desc table" query,
in MySQL, we use "describe table" query,
in MS Access, we use ???????.

I found a method in this groups to use following query
"SELECT * FROM INFORMATION_SCHEMA.TABLES" for all tables' schema
but it gives following error

"could not find file 'C:\Program Files\Microsoft Visual Studio\VB98\
INFORMATION_SCHEMA.mdb'"

Does this database file gets installed by default or require custom
configuration or this is a wrong method??

HELP

Thanks
Regards


Nov 13 '05 #2
Have you tried using the OpenSchema method of the connection object.

e.g.
Function testschema()
Dim loCon As ADODB.Connection
Dim loRst As ADODB.Recordset

Set loCon = Application.CurrentProject.Connection

Set loRst = loCon.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty,
"TABLE"))

With loRst
Do Until .EOF
Debug.Print .Fields("TABLE_NAME")
.MoveNext
Loop
.Close
End With
Set loRst = Nothing
loCon.Close
Set loCon = Nothing

End Function

--
Terry Kreft
MVP Microsoft Access
"Atif" <at*******@hotmail.com> wrote in message
news:3c**************************@posting.google.c om...
Hi

I am using MS Access 2000 and want to open the Schema to know about
Table's structures like column names, data types, primary keys etc.

In SQL Server, we use OpenSchema method of Connection object,
in Oracle, we use "desc table" query,
in MySQL, we use "describe table" query,
in MS Access, we use ???????.

I found a method in this groups to use following query
"SELECT * FROM INFORMATION_SCHEMA.TABLES" for all tables' schema
but it gives following error

"could not find file 'C:\Program Files\Microsoft Visual Studio\VB98\
INFORMATION_SCHEMA.mdb'"

Does this database file gets installed by default or require custom
configuration or this is a wrong method??

HELP

Thanks
Regards

Nov 13 '05 #3
thaks for replying BUT

OpenSchema(adSchemaTables) gives Tables' names, datatypes and other
things except PrimaryKeys

and when i use
OpenSchema(adSchemaPrimaryKeys) it results out with this table
"MSysAccessObjects" and PK_NAME="AOIndex"

and when i tried to open "MSysAccessObjects" it gives binary data(as i
think) which is not understandable.

What i require is to know what are the primary keys of a table.

Thanks
Regards
Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: wooks | last post by:
<?xml version='1.0'?> <userlogin xmlns="urn:faster:userlogin" xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'> <login>mick</login> <password>brown</password> </userlogin> Above is my...
0
by: C. M. Sperberg-McQueen | last post by:
wooks (wookiz@hotmail.com) wrote: > <?xml version='1.0'?> > <userlogin xmlns="urn:faster:userlogin" > xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'> > <login>mick</login> > ...
4
by: Gordon Dickens | last post by:
I have target xml to generate from schema. All of the XML instances have the same global element i.e. <base>. I would like to combine all of the schemas into a single schema where I could...
5
by: Vinay | last post by:
Hi I have a corrupt word file. I am able to open it with the code given below tr Dim pInfo As System.Diagnostics.ProcessStartInfo = New System.Diagnostics.ProcessStartInfo( pInfo.UseShellExecute...
0
by: Rajesh Jain | last post by:
I Have 2 separate schemas. --------------Schema 1 is defined as below----------- <xs:schema targetNamespace="http://Schemas/1" xmlns="http://Schemas/1" xmlns:xs="http://www.w3.org/2001/XMLSchema"...
3
by: Mike Wilson | last post by:
Is there a way to open an OLE DB database from within Access? I would like to use the Access GUI with its table and query explorer to examine a database only available through an OLEDB provider...
0
by: Derek | last post by:
I am creating an intranet using Visual Web Developer Express Edition. Everything has been working OK until yesterday when I started getting 62 messages all beginning "Could not find schema...
2
by: Michel | last post by:
Hi, I need to know if there is anyway of opening a database only for reading data, is an Access database, the problem is that if the file is open, the connection fails, saying that the file is...
2
by: Mad Scientist Jr | last post by:
>From an asp.net web page I want the user to open the results of a SQL query in Excel, as automatically as possible (ie not having to loop through columns, rows, in code). For this,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.