Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    I am trying to write a program that works with an Access database through ODBC. My program is retrieving, inserting, updating, and deleting records all right, but only after I build into it what tables to use and their relationships. I need it to find out what are the relationships among the tables. The ODBC function SQLForeignKeys would do the job nicely but, when I run it, I get return status -1 and, from SQLGetDiagRec, the information

    SQLState = "IM001".
    NativeError = 0.
    Message = "[Microsoft][ODBC Driver Manager] Driver does not support this function".

    The table MSysRelationships contains that information but, when I try to retrieve that table, I get the response, from SQLGetDiagRec,

    SQLState = "42000".
    NativeError = -1907.
    Message = "[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysRelationships'.".

    Can anyone please tell me how I can retrieve relationships from an Access database through ODBC? Please be patient with me, because I am new to both ODBC and Access. I may be missing something obvious and, whether I am or not, in any answer I would be grateful for details that might seem obvious to you. Thank you for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    May I ask why you're using the ODBC driver in the first place and from what application you're trying to read this information?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    The application is Access 2000. I thought the header and context expressed that, but I suppose I could have been looking for general information about ODBC. Apparently, part of ODBC for Access is missing. However, there could be another way to do the same thing. (The function SQLPrimaryKeys for Access is missing, but SQLSpecialColumns serves the same purpose. I have not found a similar work-around for SQLForeignKeys.) Perhaps I can download missing components from somewhere?

    I am actually working on two programs involving ODBC.

    One of them is for my industrial day-job employer. The program is in Fortran 95 (not to be confused with the ancient language FORTRAN), because that language is supreme for scientific and engineering work. My employer is combining those advantages with the advantages of a database in MS Access, so that humans can observe and intervene in the operations of that program. Since the structure of the database is fixed, that program only needs to retrieve, update, insert, and delete. I have no problem there.

    The program for which this thread is important is to interact with an Access database of interesting music. In contrast with simple music, in which every piece is a song with a title, interesting music requires a complicated database, in which different pieces and performances are identified in different ways. I am trying to write a program in which the query is created and displayed interactively, in a more flexible and intuitive manner than the forms, reports, etc. of Access permit. Relationships in the database are subject to change, and the program would be more reliable if its logic does not depend on the structure. Of course, a program that adapts itself to the structure is also an interesting challenge.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    Sorry, but that doesn't quite square with what you asked. Using ODBC to talk to Access is definitely doing it the hard way. Am I correct in assuming that your database is Access but the "application" is in something else? That was the way I interpreted your original question and your reference to Fortran 95 in your current post. So the music app is something different, right. What does it use as a front end for the Access database, because if the answer is Access, then there is no reason to use ODBC at all.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    I guess I misunderstood your question; in particular, the word "application". Let me try again.

    I am trying to write an application, which I have not yet named, but let us call it "ODBC Browser". Ideally, I would like its function to be retrieval of information from any ODBC-compliant application. However, the only such applications I have at my disposal are Access and Excel, and the immediate motivation for my work is to use "ODBC Browser" to retrieve information about interesting (i.e. not simply described) music from an Access database in which I have planted such information. (How the information got there is a separate matter.) Therefore, I would be content to write an application that is specific to Access.

    At present, "OBDC Browser" consists only of a few hundred lines of Fortran 95, which only retrieve very basic information, because I am building up from my work at my day job. Since Fortran 95 is very weak in user interaction, I intend such interaction to be in JavaScript. The next step is to get that glob of Fortran 95 to retrieve the relationships. It appears that the way to do so is with the ODBC function SQLForeignKeys, and that is where I am stuck, because even though that function was introduced with ODBC 1.0, it seems to be missing from the Access driver.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    I see that I understood the question correctly. Unfortumately, I can't help you with an answer. Perhaps someone else has delved into the ODBC drivers and can assist you.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    Thank you for your attention. Because of the clarification you elicited, others have a better chance of understanding the problem.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    Unfortunately the Access ODBC driver exposes very little of the internals of the Jet engine. In point of fact, that is true of most ODBC drivers - the SQL Server driver is one of the most robust, but it doesn't expose many of the capabilities of SQL Server 6.5, 7 or 2000. The Access object model can be manipulated in nearly every respect, including adding or removing table relationships, using VBA. It seems to me that trying to use Fortran 95 to manipulate the Access object model is a bit like driving nails with a screwdriver. (I usually use a saw myself <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    Wendell

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    After chewing on your ideas a bit more, you might want to try tinkering with the MSDE that ships with Access. It let's you among other things create an ODBC connection to a SQL Server database without having to buy SQL Server - of course the version that you get is dumbed down quite a bit, but it still responds to the SQL Server ODBC driver the same way the full product does. Then you could set up a trace on the ODBC activity (that's done with the ODBC administrator you can find buried somewhere in the Control Panel - it has different names and is often in the Administrative Tools folder). That would let you work with a more full-featured ODBC driver. Note however that relationships in a database must be explicitly defined before you can return any information about them - that's done in the relationships window in Access. SQL Server can implement it in several different ways, which makes it a bit more of a challenge. Hope this is useful.

    PS - I'm intrigued with your idea of a music database - I spend a fair bit of time doing either musical theatre or opera, sometimes both.
    Wendell

  10. #10
    New Lounger
    Join Date
    Jun 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    Thank you for the suggestion. I'll follow that up.

  11. #11
    New Lounger
    Join Date
    Jun 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    Even if the ODBC function SQLForeignKeys worked as advertized, it would leave ambiguous a situation in which more than one link exists between two tables. It has no way to indicate whether more than one relationship exists between the two tables, or whether the combination of keys denotes one relationship. That is a defect in the ODBC standard. Maybe it gives Microsoft a good excuse not to implement the function SQLForeignKeys.

    My present solution to the problem is a fallback in the function of my projected software. It requires a table that lists the relationships unambiguously. That can be prepared easily in Access, by copy-and-pasting from MSysAccessObjects, plus slight editing. However, it means that my program requires that setup to be made by someone with access to the database.

    While we are on the subject of the shortcomings of ODBC, here is another. It only provides data in 8-bit ASCII. You may have seen another thread in which HansV kindly told me how to get sharp signs and flat signs into Access. (Thanks again, HansV.) When my program retrieves them through ODBC, they come as question marks. Since my program is only for passively exploring a database, I don't have to worry about corrupting the data, and these limitations only somewhat impair the program's usefulness.

    I am making good use of another feature of Fortran. The process I'm developing is computationally intensive. Fortran is designed for maximum efficiency in that sort of work. I am still using JavaScript for user interaction, because it is designed for maximum efficiency in user interaction. Getting Fortran and JavaScript to talk to each other is an interesting challenge.

    Thank you again to everyone who helped with, or even read about, my struggles with ODBC.

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Retrieving Relationships through ODBC. (2000 (9.0.3821 SR-1))

    It's an interesting project you've undertaken. One comment on table design: In general we try to avoid having multiple relationships between two tables, often by adding a primary key to a table that is an autonumber, and then using that as a foreign key in the related table. That way Access will happily deal with referential integrity issues and only requires a single relationship to manage the data. Good luck with your project.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •