Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Linked Tables vs Pass-Through Queries

Discussion in 'Information Technology' started by Steve Happ, Jul 28, 2006.

  1. Steve Happ

    Steve Happ
    Expand Collapse
    Guest

    Hello:

    I've built an Access application (2003) that pulls some data from a SQL
    Server database, allowing additional data to be added in an Access table. I
    have been using linked tables to the SQL database with Access queries to
    "merge" the added data to the SQL data. There are a couple of problems that
    are causing me to rethink how it's done:
    1. It's slow
    2. The non-Access SQL application freezes up when my users are in the
    Access application.

    I have no need to open the SQL data in edit mode ... I'm simply reading and
    displaying related data. I've read the comments about pass-through queries
    as an option and have replaced the linked tables with pass-through queries,
    but I'm running into snags:

    1. Can you create a pass-through query that has data from both SQL database
    and Access tables? I haven't been able to do so. So I create the
    pass-through query with just SQL data and create another query to pull from
    it and the Access tables. Is there a better way?

    2. I had my main form's data record source as a query (with SQL linked
    table data and access table). When I replaced the SQL linked tables with
    pass-through queries, I could no longer add rows with my main form. Why?

    3. My main report had a subreport at the end using linked table data. When
    replacing the linked table with pass-through query, it now says you cannot
    use a pass-through query in a subform.

    SO ... is there a better way than pass-through queries to solve my initial 2
    problems?

    Thanks much. If you need more info to respond, I'm happy to give it, but
    this has gotten too long already ... just ask.
    Steve
     
  2. Loading...

    Similar Threads Forum Date
    Opinion Letter: When terrorism is falsely linked by race or religion, we all become the victims Breaking News Apr 27, 2013
    SciTech Law of Entropy Linked to Evolution of Intelligence Breaking News Apr 23, 2013
    Canada GM corn linked to early death in new study Breaking News Sep 22, 2012
    India set to ban 100 Al Qaeda-linked groups Hard Talk May 17, 2010
    Sikh News Temple linked to terrorists still active (Moldova.org) Breaking News Feb 5, 2008

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    "Steve Happ" <SteveHapp@discussions.microsoft.com> wrote in message
    news:E36EBA93-D6CD-495A-8D5F-99DB5A5713C4@microsoft.com...
    > Hello:
    >
    > I've built an Access application (2003) that pulls some data from a SQL
    > Server database, allowing additional data to be added in an Access table.
    > I
    > have been using linked tables to the SQL database with Access queries to
    > "merge" the added data to the SQL data. There are a couple of problems
    > that
    > are causing me to rethink how it's done:
    > 1. It's slow
    > 2. The non-Access SQL application freezes up when my users are in the
    > Access application.
    >
    > I have no need to open the SQL data in edit mode ... I'm simply reading
    > and
    > displaying related data. I've read the comments about pass-through
    > queries
    > as an option and have replaced the linked tables with pass-through
    > queries,
    > but I'm running into snags:
    >
    > 1. Can you create a pass-through query that has data from both SQL
    > database
    > and Access tables? I haven't been able to do so. So I create the
    > pass-through query with just SQL data and create another query to pull
    > from
    > it and the Access tables. Is there a better way?


    There's no other way.

    > 2. I had my main form's data record source as a query (with SQL linked
    > table data and access table). When I replaced the SQL linked tables with
    > pass-through queries, I could no longer add rows with my main form. Why?


    Pass-through queries are read-only. Therefore, you can't add rows to them.
    However, this seems to contradict your previous statements that "I have no
    need to open the SQLdata in edit mode"

    > 3. My main report had a subreport at the end using linked table data.
    > When
    > replacing the linked table with pass-through query, it now says you cannot
    > use a pass-through query in a subform.


    This surprises me, and unfortunately I'm not in a position to test at the
    moment.

    > SO ... is there a better way than pass-through queries to solve my initial
    > 2
    > problems?


    As there's is no way to solve your initial 2 problems, this seems a moot
    question. <g>

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)
     
  4. Steve Happ

    Steve Happ
    Expand Collapse
    Guest

    Douglas:

    Thanks for your quick reply. Regarding: "Pass-through queries are
    read-only. Therefore, you can't add rows to them. However, this seems to
    contradict your previous statements that "I have no need to open the SQLdata
    in edit mode"" ...
    I don't want to add rows to the SQL table, just the Access Table. A field
    on the Access table links to a field on the SQL table just to fill in the
    data that's needed.

    Regarding: "As there's is no way to solve your initial 2 problems, this
    seems a moot question." ... Really? There's no way to solve the initial 2
    problems?

    Thanks\
    Steve

    "Douglas J. Steele" wrote:

    > "Steve Happ" <SteveHapp@discussions.microsoft.com> wrote in message
    > news:E36EBA93-D6CD-495A-8D5F-99DB5A5713C4@microsoft.com...
    > > Hello:
    > >
    > > I've built an Access application (2003) that pulls some data from a SQL
    > > Server database, allowing additional data to be added in an Access table.
    > > I
    > > have been using linked tables to the SQL database with Access queries to
    > > "merge" the added data to the SQL data. There are a couple of problems
    > > that
    > > are causing me to rethink how it's done:
    > > 1. It's slow
    > > 2. The non-Access SQL application freezes up when my users are in the
    > > Access application.
    > >
    > > I have no need to open the SQL data in edit mode ... I'm simply reading
    > > and
    > > displaying related data. I've read the comments about pass-through
    > > queries
    > > as an option and have replaced the linked tables with pass-through
    > > queries,
    > > but I'm running into snags:
    > >
    > > 1. Can you create a pass-through query that has data from both SQL
    > > database
    > > and Access tables? I haven't been able to do so. So I create the
    > > pass-through query with just SQL data and create another query to pull
    > > from
    > > it and the Access tables. Is there a better way?

    >
    > There's no other way.
    >
    > > 2. I had my main form's data record source as a query (with SQL linked
    > > table data and access table). When I replaced the SQL linked tables with
    > > pass-through queries, I could no longer add rows with my main form. Why?

    >
    > Pass-through queries are read-only. Therefore, you can't add rows to them.
    > However, this seems to contradict your previous statements that "I have no
    > need to open the SQLdata in edit mode"
    >
    > > 3. My main report had a subreport at the end using linked table data.
    > > When
    > > replacing the linked table with pass-through query, it now says you cannot
    > > use a pass-through query in a subform.

    >
    > This surprises me, and unfortunately I'm not in a position to test at the
    > moment.
    >
    > > SO ... is there a better way than pass-through queries to solve my initial
    > > 2
    > > problems?

    >
    > As there's is no way to solve your initial 2 problems, this seems a moot
    > question. <g>
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    >
    >
     

Share This Page