How to get the primary keys and the foreign keys from an Access database?

How to get the primary keys and the foreign keys from an Access database?

Post by Yves » Sat, 02 Dec 2000 04:00:00



Hi,

I'm trying to retrieve a list of keys for an Access database with ADOX.
According to the documentation, this should be possible using the following
code:

For i = 1 To objCatalog.Tables.Count
    Set objTable = objCatalog.Tables.Item(i - 1)
    If UCase(objTable.Type) = "TABLE" Then
        Text1.Text = Text1.Text + objCatalog.Tables.Item(i - 1).Name +
vbNewLine
        For Each objKey In objTable.Keys
            Text1.Text = Text1.Text + " - - " + objKey.Name + vbNewLine
        Next
    End If
Next

However, when I try to access the objTable.Keys collection, I receive an
error saying the provider doesn't support this functionality. Is there any
way to work around this? Am I using the wrong collection? Should I use DAO
instead of ADOX? Can somebody provide me with sample code for doing this?

Kind regards,

Yves

 
 
 

How to get the primary keys and the foreign keys from an Access database?

Post by Ryszard Gawro » Sat, 02 Dec 2000 04:00:00


Hello Yves,
I'm not sure, but I think you have a problem with system.mdw.
You can find some information on:
http://support.microsoft.com/support/kb/articles/Q243/4/66.ASP
I have tested your sorce code but I open the database this way:
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\mywork\ADOXUser\db1.mdb;User Id=Admin;Password=;Jet OLEDB:System
database=d:\mywork\ADOXUser\system.mdw"

It works fine.
Before you do it you have to prepare system.mdw file.
I copy the system.mdw to my project folder
and I refered to this file:

Be sure this file is converted to Access 2000. Then should be everything OK.
Do you have Access 2000?

Best regards
Ryszard Gawron
Microsoft Developer Support



Quote:> Hi,

> I'm trying to retrieve a list of keys for an Access database with ADOX.
> According to the documentation, this should be possible using the
following
> code:

> For i = 1 To objCatalog.Tables.Count
>     Set objTable = objCatalog.Tables.Item(i - 1)
>     If UCase(objTable.Type) = "TABLE" Then
>         Text1.Text = Text1.Text + objCatalog.Tables.Item(i - 1).Name +
> vbNewLine
>         For Each objKey In objTable.Keys
>             Text1.Text = Text1.Text + " - - " + objKey.Name + vbNewLine
>         Next
>     End If
> Next

> However, when I try to access the objTable.Keys collection, I receive an
> error saying the provider doesn't support this functionality. Is there any
> way to work around this? Am I using the wrong collection? Should I use DAO
> instead of ADOX? Can somebody provide me with sample code for doing this?

> Kind regards,

> Yves


 
 
 

How to get the primary keys and the foreign keys from an Access database?

Post by Yves » Sat, 02 Dec 2000 04:00:00


The only thing I'm using as a connectionstring is

"Driver={Microsoft Access Driver (*.mdb)};DBQ=mydb.mdb"

because I want to keep my code compatible for as many versions of Access as
possible. Isn't there any other way to determine the PK and FK constraints?
How does Access do it? Using the Access DOM I guess, but I don't have any
code for that.

Thanx for your help,

Yves


> Hello Yves,
> I'm not sure, but I think you have a problem with system.mdw.
> You can find some information on:
> http://support.microsoft.com/support/kb/articles/Q243/4/66.ASP
> I have tested your sorce code but I open the database this way:
> adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=d:\mywork\ADOXUser\db1.mdb;User Id=Admin;Password=;Jet OLEDB:System
> database=d:\mywork\ADOXUser\system.mdw"

> It works fine.
> Before you do it you have to prepare system.mdw file.
> I copy the system.mdw to my project folder
> and I refered to this file:

> Be sure this file is converted to Access 2000. Then should be everything
OK.
> Do you have Access 2000?

> Best regards
> Ryszard Gawron
> Microsoft Developer Support



> > Hi,

> > I'm trying to retrieve a list of keys for an Access database with ADOX.
> > According to the documentation, this should be possible using the
> following
> > code:

> > For i = 1 To objCatalog.Tables.Count
> >     Set objTable = objCatalog.Tables.Item(i - 1)
> >     If UCase(objTable.Type) = "TABLE" Then
> >         Text1.Text = Text1.Text + objCatalog.Tables.Item(i - 1).Name +
> > vbNewLine
> >         For Each objKey In objTable.Keys
> >             Text1.Text = Text1.Text + " - - " + objKey.Name + vbNewLine
> >         Next
> >     End If
> > Next

> > However, when I try to access the objTable.Keys collection, I receive an
> > error saying the provider doesn't support this functionality. Is there
any
> > way to work around this? Am I using the wrong collection? Should I use
DAO
> > instead of ADOX? Can somebody provide me with sample code for doing
this?

> > Kind regards,

> > Yves

 
 
 

How to get the primary keys and the foreign keys from an Access database?

Post by Ryszard Gawro » Sat, 02 Dec 2000 04:00:00




> The only thing I'm using as a connectionstring is

> "Driver={Microsoft Access Driver (*.mdb)};DBQ=mydb.mdb"

> because I want to keep my code compatible for as many versions of Access
as
> possible. Isn't there any other way to determine the PK and FK
constraints?
> How does Access do it? Using the Access DOM I guess, but I don't have any
> code for that.

> Thanx for your help,

> Yves



> > Hello Yves,
> > I'm not sure, but I think you have a problem with system.mdw.
> > You can find some information on:
> > http://support.microsoft.com/support/kb/articles/Q243/4/66.ASP
> > I have tested your sorce code but I open the database this way:
> > adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=d:\mywork\ADOXUser\db1.mdb;User Id=Admin;Password=;Jet
OLEDB:System
> > database=d:\mywork\ADOXUser\system.mdw"

> > It works fine.
> > Before you do it you have to prepare system.mdw file.
> > I copy the system.mdw to my project folder
> > and I refered to this file:

> > Be sure this file is converted to Access 2000. Then should be everything
> OK.
> > Do you have Access 2000?

> > Best regards
> > Ryszard Gawron
> > Microsoft Developer Support



> > > Hi,

> > > I'm trying to retrieve a list of keys for an Access database with
ADOX.
> > > According to the documentation, this should be possible using the
> > following
> > > code:

> > > For i = 1 To objCatalog.Tables.Count
> > >     Set objTable = objCatalog.Tables.Item(i - 1)
> > >     If UCase(objTable.Type) = "TABLE" Then
> > >         Text1.Text = Text1.Text + objCatalog.Tables.Item(i - 1).Name +
> > > vbNewLine
> > >         For Each objKey In objTable.Keys
> > >             Text1.Text = Text1.Text + " - - " + objKey.Name +
vbNewLine
> > >         Next
> > >     End If
> > > Next

> > > However, when I try to access the objTable.Keys collection, I receive
an
> > > error saying the provider doesn't support this functionality. Is there
> any
> > > way to work around this? Am I using the wrong collection? Should I use
> DAO
> > > instead of ADOX? Can somebody provide me with sample code for doing
> this?

> > > Kind regards,

> > > Yves

 
 
 

How to get the primary keys and the foreign keys from an Access database?

Post by Ryszard Gawro » Sat, 02 Dec 2000 04:00:00


Yves,
in this case try to use DAO. Let me know if you need some additional
information.
regards
Ryszard Gawron
Microsoft Developer Support



> The only thing I'm using as a connectionstring is

> "Driver={Microsoft Access Driver (*.mdb)};DBQ=mydb.mdb"

> because I want to keep my code compatible for as many versions of Access
as
> possible. Isn't there any other way to determine the PK and FK
constraints?
> How does Access do it? Using the Access DOM I guess, but I don't have any
> code for that.

> Thanx for your help,

> Yves



> > Hello Yves,
> > I'm not sure, but I think you have a problem with system.mdw.
> > You can find some information on:
> > http://support.microsoft.com/support/kb/articles/Q243/4/66.ASP
> > I have tested your sorce code but I open the database this way:
> > adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=d:\mywork\ADOXUser\db1.mdb;User Id=Admin;Password=;Jet
OLEDB:System
> > database=d:\mywork\ADOXUser\system.mdw"

> > It works fine.
> > Before you do it you have to prepare system.mdw file.
> > I copy the system.mdw to my project folder
> > and I refered to this file:

> > Be sure this file is converted to Access 2000. Then should be everything
> OK.
> > Do you have Access 2000?

> > Best regards
> > Ryszard Gawron
> > Microsoft Developer Support



> > > Hi,

> > > I'm trying to retrieve a list of keys for an Access database with
ADOX.
> > > According to the documentation, this should be possible using the
> > following
> > > code:

> > > For i = 1 To objCatalog.Tables.Count
> > >     Set objTable = objCatalog.Tables.Item(i - 1)
> > >     If UCase(objTable.Type) = "TABLE" Then
> > >         Text1.Text = Text1.Text + objCatalog.Tables.Item(i - 1).Name +
> > > vbNewLine
> > >         For Each objKey In objTable.Keys
> > >             Text1.Text = Text1.Text + " - - " + objKey.Name +
vbNewLine
> > >         Next
> > >     End If
> > > Next

> > > However, when I try to access the objTable.Keys collection, I receive
an
> > > error saying the provider doesn't support this functionality. Is there
> any
> > > way to work around this? Am I using the wrong collection? Should I use
> DAO
> > > instead of ADOX? Can somebody provide me with sample code for doing
> this?

> > > Kind regards,

> > > Yves

 
 
 

How to get the primary keys and the foreign keys from an Access database?

Post by Yves » Sat, 02 Dec 2000 04:00:00


All right, I got that far already. I have a list of tables for the database,
I have the primary keys and the foreign keys for each table. But how can I
detect whether a table is a system table or not? This was quite easy in ADOX
where I just had to check the type property for each table.

Anyway, once I have the list of foreign keys, how can I see what field and
table they link to? Using SQLDMO, I would say ReferencedKey and
ReferencedTable, but none of these properties seem to exist in DAO.

Kind regards and Thanx in Advance!

Yves


> Yves,
> in this case try to use DAO. Let me know if you need some additional
> information.
> regards
> Ryszard Gawron
> Microsoft Developer Support



> > The only thing I'm using as a connectionstring is

> > "Driver={Microsoft Access Driver (*.mdb)};DBQ=mydb.mdb"

> > because I want to keep my code compatible for as many versions of Access
> as
> > possible. Isn't there any other way to determine the PK and FK
> constraints?
> > How does Access do it? Using the Access DOM I guess, but I don't have
any
> > code for that.

> > Thanx for your help,

> > Yves



> > > Hello Yves,
> > > I'm not sure, but I think you have a problem with system.mdw.
> > > You can find some information on:
> > > http://support.microsoft.com/support/kb/articles/Q243/4/66.ASP
> > > I have tested your sorce code but I open the database this way:
> > > adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > Source=d:\mywork\ADOXUser\db1.mdb;User Id=Admin;Password=;Jet
> OLEDB:System
> > > database=d:\mywork\ADOXUser\system.mdw"

> > > It works fine.
> > > Before you do it you have to prepare system.mdw file.
> > > I copy the system.mdw to my project folder
> > > and I refered to this file:

> > > Be sure this file is converted to Access 2000. Then should be
everything
> > OK.
> > > Do you have Access 2000?

> > > Best regards
> > > Ryszard Gawron
> > > Microsoft Developer Support



> > > > Hi,

> > > > I'm trying to retrieve a list of keys for an Access database with
> ADOX.
> > > > According to the documentation, this should be possible using the
> > > following
> > > > code:

> > > > For i = 1 To objCatalog.Tables.Count
> > > >     Set objTable = objCatalog.Tables.Item(i - 1)
> > > >     If UCase(objTable.Type) = "TABLE" Then
> > > >         Text1.Text = Text1.Text + objCatalog.Tables.Item(i - 1).Name
+
> > > > vbNewLine
> > > >         For Each objKey In objTable.Keys
> > > >             Text1.Text = Text1.Text + " - - " + objKey.Name +
> vbNewLine
> > > >         Next
> > > >     End If
> > > > Next

> > > > However, when I try to access the objTable.Keys collection, I
receive
> an
> > > > error saying the provider doesn't support this functionality. Is
there
> > any
> > > > way to work around this? Am I using the wrong collection? Should I
use
> > DAO
> > > > instead of ADOX? Can somebody provide me with sample code for doing
> > this?

> > > > Kind regards,

> > > > Yves

 
 
 

How to get the primary keys and the foreign keys from an Access database?

Post by Ryszard Gawro » Wed, 06 Dec 2000 04:00:00


Yves,
I'm till Wednesday out of office. I can look for the solution on Thursday.
regards
Ryszard Gawron
Microsoft Developer Support



> All right, I got that far already. I have a list of tables for the
database,
> I have the primary keys and the foreign keys for each table. But how can I
> detect whether a table is a system table or not? This was quite easy in
ADOX
> where I just had to check the type property for each table.

> Anyway, once I have the list of foreign keys, how can I see what field and
> table they link to? Using SQLDMO, I would say ReferencedKey and
> ReferencedTable, but none of these properties seem to exist in DAO.

> Kind regards and Thanx in Advance!

> Yves



> > Yves,
> > in this case try to use DAO. Let me know if you need some additional
> > information.
> > regards
> > Ryszard Gawron
> > Microsoft Developer Support



> > > The only thing I'm using as a connectionstring is

> > > "Driver={Microsoft Access Driver (*.mdb)};DBQ=mydb.mdb"

> > > because I want to keep my code compatible for as many versions of
Access
> > as
> > > possible. Isn't there any other way to determine the PK and FK
> > constraints?
> > > How does Access do it? Using the Access DOM I guess, but I don't have
> any
> > > code for that.

> > > Thanx for your help,

> > > Yves



> > > > Hello Yves,
> > > > I'm not sure, but I think you have a problem with system.mdw.
> > > > You can find some information on:
> > > > http://support.microsoft.com/support/kb/articles/Q243/4/66.ASP
> > > > I have tested your sorce code but I open the database this way:
> > > > adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=d:\mywork\ADOXUser\db1.mdb;User Id=Admin;Password=;Jet
> > OLEDB:System
> > > > database=d:\mywork\ADOXUser\system.mdw"

> > > > It works fine.
> > > > Before you do it you have to prepare system.mdw file.
> > > > I copy the system.mdw to my project folder
> > > > and I refered to this file:

> > > > Be sure this file is converted to Access 2000. Then should be
> everything
> > > OK.
> > > > Do you have Access 2000?

> > > > Best regards
> > > > Ryszard Gawron
> > > > Microsoft Developer Support



> > > > > Hi,

> > > > > I'm trying to retrieve a list of keys for an Access database with
> > ADOX.
> > > > > According to the documentation, this should be possible using the
> > > > following
> > > > > code:

> > > > > For i = 1 To objCatalog.Tables.Count
> > > > >     Set objTable = objCatalog.Tables.Item(i - 1)
> > > > >     If UCase(objTable.Type) = "TABLE" Then
> > > > >         Text1.Text = Text1.Text + objCatalog.Tables.Item(i -
1).Name
> +
> > > > > vbNewLine
> > > > >         For Each objKey In objTable.Keys
> > > > >             Text1.Text = Text1.Text + " - - " + objKey.Name +
> > vbNewLine
> > > > >         Next
> > > > >     End If
> > > > > Next

> > > > > However, when I try to access the objTable.Keys collection, I
> receive
> > an
> > > > > error saying the provider doesn't support this functionality. Is
> there
> > > any
> > > > > way to work around this? Am I using the wrong collection? Should I
> use
> > > DAO
> > > > > instead of ADOX? Can somebody provide me with sample code for
doing
> > > this?

> > > > > Kind regards,

> > > > > Yves

 
 
 

How to get the primary keys and the foreign keys from an Access database?

Post by Ryszard Gawro » Fri, 08 Dec 2000 04:00:00


Yves,
look at the page:
http://msdn.microsoft.com/library/books/dnjet/c3_body_37.htm
I think you need relations this is part of database object.
Let me know, if you have any problems
regards
Ryszard



> All right, I got that far already. I have a list of tables for the
database,
> I have the primary keys and the foreign keys for each table. But how can I
> detect whether a table is a system table or not? This was quite easy in
ADOX
> where I just had to check the type property for each table.

> Anyway, once I have the list of foreign keys, how can I see what field and
> table they link to? Using SQLDMO, I would say ReferencedKey and
> ReferencedTable, but none of these properties seem to exist in DAO.

> Kind regards and Thanx in Advance!

> Yves



> > Yves,
> > in this case try to use DAO. Let me know if you need some additional
> > information.
> > regards
> > Ryszard Gawron
> > Microsoft Developer Support



> > > The only thing I'm using as a connectionstring is

> > > "Driver={Microsoft Access Driver (*.mdb)};DBQ=mydb.mdb"

> > > because I want to keep my code compatible for as many versions of
Access
> > as
> > > possible. Isn't there any other way to determine the PK and FK
> > constraints?
> > > How does Access do it? Using the Access DOM I guess, but I don't have
> any
> > > code for that.

> > > Thanx for your help,

> > > Yves



> > > > Hello Yves,
> > > > I'm not sure, but I think you have a problem with system.mdw.
> > > > You can find some information on:
> > > > http://support.microsoft.com/support/kb/articles/Q243/4/66.ASP
> > > > I have tested your sorce code but I open the database this way:
> > > > adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=d:\mywork\ADOXUser\db1.mdb;User Id=Admin;Password=;Jet
> > OLEDB:System
> > > > database=d:\mywork\ADOXUser\system.mdw"

> > > > It works fine.
> > > > Before you do it you have to prepare system.mdw file.
> > > > I copy the system.mdw to my project folder
> > > > and I refered to this file:

> > > > Be sure this file is converted to Access 2000. Then should be
> everything
> > > OK.
> > > > Do you have Access 2000?

> > > > Best regards
> > > > Ryszard Gawron
> > > > Microsoft Developer Support



> > > > > Hi,

> > > > > I'm trying to retrieve a list of keys for an Access database with
> > ADOX.
> > > > > According to the documentation, this should be possible using the
> > > > following
> > > > > code:

> > > > > For i = 1 To objCatalog.Tables.Count
> > > > >     Set objTable = objCatalog.Tables.Item(i - 1)
> > > > >     If UCase(objTable.Type) = "TABLE" Then
> > > > >         Text1.Text = Text1.Text + objCatalog.Tables.Item(i -
1).Name
> +
> > > > > vbNewLine
> > > > >         For Each objKey In objTable.Keys
> > > > >             Text1.Text = Text1.Text + " - - " + objKey.Name +
> > vbNewLine
> > > > >         Next
> > > > >     End If
> > > > > Next

> > > > > However, when I try to access the objTable.Keys collection, I
> receive
> > an
> > > > > error saying the provider doesn't support this functionality. Is
> there
> > > any
> > > > > way to work around this? Am I using the wrong collection? Should I
> use
> > > DAO
> > > > > instead of ADOX? Can somebody provide me with sample code for
doing
> > > this?

> > > > > Kind regards,

> > > > > Yves