Using DAO or ADO, obtain foreign keys or relationships

[Kevin.Shirey]

Hello,

I have been using LabVIEW Database Toolkit as well as the LabSQL code. I have been trying to use LabVIEW to obtain the relationships that are defined for specific fieldnames in an MSAccess database. I have tried ADO and DAO and a few others and have no found a way to obtain these foreign keys or relationships. I also tried the MSAccess active x reference and could not obtain them. I also tried to use the Schema 's and could not get them to work correctly to obtain the information I needed.

So, In short: Does anyone know of a method in which to FIND or OBTAIN the restrictions/foreign keys/relationships on a given table or feild?

Thanks in Advance,

--Kevin

[rolfk]

sinequanon wrote:

> Hello, <br><br>I have been using LabVIEW Database Toolkit as well as
> the LabSQL code. I have been trying to use LabVIEW to obtain the relationships
> that are defined for specific fieldnames in an MSAccess database. I have tried
> ADO and DAO and a few others and have no found a way to obtain these foreign
> keys or relationships. I also tried the MSAccess active x reference and could
> not obtain them. I also tried to use the Schema 's and could not get them to
> work correctly to obtain the information I needed. <br><br>So, In short: Does
> anyone know of a method in which to FIND or OBTAIN the restrictions/foreign
> keys/relationships on a given table or feild?

I'm afraid that this is highly datebase specific and if it can be
retrieved in some ways through ODBC, ADO, DAO or such at all is at least
questionable and for some databases certainly impossible. I would
assume that high profile databases would maintain these relationships in
private or at least protected tables which may or may not be queried
through remote interfaces.

Rolf kalbermatter

[Kevin.Shirey]

Thanks for the reply.

I am using a very simple MS Access database. It only has a few relationships per table. I thought this was something standard in all MS Access databases? The ability to retrieve a relationship should be straight forward at least in an Access database.
Is there any material in which I can use in LabVIEW to at least point me in the right direction? DAO, ADO, JET, Access; all of it has yielded dead ends for me. I posted this to try to get help in which to accomplish the task. I hope someone could help!

[Dennis_Knutson]

If it's available, it would only be available with Access's ActiveX interface and not through SQL or ADO. SQL and ADO know nothing about the actual database and are certainly not Access specific. It's been a very long time since I've had to use Access but you could try opening Access and seeing what info is in the vb help. If there is properties or methods that expose the database design, then you would require a local installation of Access where ever you use the program. Starting Access is also going to slow things down.

Is there a reason you can't simply open the database and view the relationships before you write your program? If you can't open it for editing because you don't have the password, I kind of doubt that going through a back door via ActiveX would work either.

[Kevin.Shirey]

Thanks for the reply Dennis. I have been toying around with the Active X calls for Access but it also uses DAO within itself (actually rhe JET driver[MS Access only] for DAO) and seems to yeild inconsistent functions with what is contained in the help file for Access.

As far as ADO, you are incorrect. I use ADO to get the table names, field names, feild types and feild sizes. Within ADO, you can open the Database Schema and choose which type of schema to open (tables, table contraints...). I haven't gotten the parameters correct yet so I do not know how it works completely. I don't have any working examples and once again the help is inconsistent with the function call. I looked into the DB Toolkit proivided by NI and they use the Schema to get the table names, however they call their own DLL to do it! I got no help there.

I can open the database and view the relationships, but thats not the purpose. I am trying to build a database documentor program for our customer. It is imperative that I find the relationships in the tables to document the contraints and such. Right now the program gets all the tables, fields, types and sizes and exports them into tables into MS Word as a report document. This is through the use of Active X calls to MS Word and ADO calls from the DB Toolkit(NI). If I can obtain the relationships dynamically, then I won't have to manually write them down. We have over 100 tables, a few tables with over 140 fields. This database is huge, but will be ported to Oracle once the initial design is complete. Also, the database is going through multiple changes from multiple sources. Its way to time consuming to document the design of the database manually, and should be able to be documented by dynamic calls through active x.

So nonetheless I am still stuck with the problem without a viable solution. If I find one, I will post it for others to use. Thanks!