Alpha Video Training
Results 1 to 5 of 5

Thread: Problem with Displaying Access Table data using ADO/ODBC

  1. #1
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    785

    Default Problem with Displaying Access Table data using ADO/ODBC

    Firstly, I have to say that this is probably just past the scope of my programming knowledge.
    I have two tables in an access MDB that I wish to display , using the "display ASO/ODBC data in a Form" genie.
    One table has a primary key defined, the second does not.
    I can use all the functions on the first table (one with primary key) but none of the functions work in the non-primary key table.
    I get variations on the message

    OLE Automation Error. Sources is ADODB.Recordset
    Operation is not allowed when Object is closed.
    Recordset.absoluteposition subelement not found.
    what do I have to define in the genie to overcome this problem. (this is the more important of the two tables to display !!).

    thanks
    Last edited by Graham Wickens; 08-04-2019 at 06:11 AM. Reason: spelling
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  2. #2
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,358

    Default Re: Problem with Displaying Access Table data using ADO/ODBC

    Create a PK in the other table.
    Without a PK, you can't do much more than read the table sequentially.
    You need a key to carry out processing operations.
    Same with SQL.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  3. #3
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    785

    Default Re: Problem with Displaying Access Table data using ADO/ODBC

    Love the first Sentence, Never used Access other than to read data
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  4. #4
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    785

    Default Re: Problem with Displaying Access Table data using ADO/ODBC

    Looking into this a little deeper, the two access tables are connected in a "one-to-many" link on the "Keyno" field.
    "Genav" is the Main table and "Genav_Index" is the child.
    Presumably I have to SELECT fields to build each combined record to display, something along the lines ...


    SELECT [CurrentID], [ACtype], [Series], [Code], [Con], [LineNum], [[Home_Airfield], [ [KeyNo] FROM [genav]
    SELECT [Current], [Reg], [Pi_Sort] FROM [genav_index] WHERE [genav_index.Keyno] = [genav.keyno]
    As yet I am still trying to find the correct syntax (by trial and error on my part).

    Looking at the xbasic from the ADO Genie, it would appear that I have to define a "DataObj" for both tables.
    I also presume there will be more horrors awaiting me as I scroll through the xbasic!

    I have got it working in A5, but each time the access DB is updated, I have to import the two tables into A5 from access, change the keyid to a character field and build the one-to-many set. This is very time consuming as the combined number of records is nearly 2 million !!

    I should add that I will only ever be reading from the access tables.

    I am both the Developer and the User, so it is an overhead I could really do without (in terms of time and brain).

    My theory is that this should be possible as both Access and A5 know about one-to-many links (terminology is different) and I am presuming that ADO/ODBC can also cope with one-to-many as well?
    Last edited by Graham Wickens; 08-05-2019 at 11:12 AM. Reason: Additional Information
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  5. #5
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,681

    Default Re: Problem with Displaying Access Table data using ADO/ODBC

    Have you tried converting the Access database to Excel? Then you should be able to save the XLS as DBF.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

Similar Threads

  1. Microsfoft Office 365 ( Click-to-Run ) Access ODBC Problem
    By twillans in forum General Questions
    Replies: 2
    Last Post: 07-25-2016, 11:58 AM
  2. Access DBF table data in dialog
    By russellhaggard in forum Application Server Version 10 - Web/Browser Applications
    Replies: 3
    Last Post: 08-06-2011, 05:33 PM
  3. ODBC Microsoft Access Driver Problem
    By PNiles in forum Application Server Version 9 - Web/Browser Applications
    Replies: 0
    Last Post: 11-02-2008, 10:59 PM
  4. Tutorial problem-Displaying images from a table
    By oldtony in forum Web Application Server v7
    Replies: 2
    Last Post: 11-11-2005, 11:48 AM
  5. ODBC Access 97 date problem
    By Doug Chanley in forum Alpha Five Version 4
    Replies: 0
    Last Post: 10-10-2000, 10:00 PM

Bookmarks

Posting Permissions

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