Re: SQL Express query in MDB

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I have two linked tables in Access 2003, one linked to a table in SQL Server
Express 2005 and the other is linked to a view in the same database.

If I open either of the linked tables in Access and add a record in the
back-end, this change is not reflected in the open linked table in Access.
I have to close the table and re-open it to see the changes (which I would
expect). If I run a query to count the number of rows in one of these
linked tables, add a record using the back-end and then re-run the query it
increments as expected.

I take it that when you tried re-opening the tables, the changes were not
reflected in the results?

MH

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:OEtn7n4hHHA.4880@xxxxxxxxxxxxxxxxxxxxxxx
Dear MH:

That's what I thought I would do initially. The SQL Express tables are
already linked.

So, I went to the Queries selection and asked it to link to the View I
wanted. It did link that view, but not as a query, but as a Table. The
table is not "dynamically" linked to the query - it doesn't follow changes
to the data that are behind the query, and you can modify and add rows to
this table. I suppose I could try to delete the table and recreate it,
getting a "snapshot" of the query data each time I need to process it, but
that's hardly a very handy way to do this.

So, have you tried the thing you're suggesting? Doesn't work here. I'm
on Access 2002. Is it different for other versions?

Tom Ellison


"MH" <noway@xxxxxxxxx> wrote in message
news:%23n2X7z3hHHA.4076@xxxxxxxxxxxxxxxxxxxxxxx
Hello Tom,

Could you not use views in the SQL Express back-end and link to the views
in Access?

MH

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:OA3Pz%232hHHA.4288@xxxxxxxxxxxxxxxxxxxxxxx
Dear friends:

I have an MDB application with a SQL Express back end. I have written
some extensive query work there that cannot be written for Jet (indeed,
if I try to extend this query work, SQL Server fails, "Internal Error").

I would like to write Jet queries based on the SQL Express query work.
I thought to Link to the SQL query, but you cannot link to a query, only
to a table. I can link a Jet table to that query, but it isn't dynamic
(is it?) It appears to just create a table from a snapshot of the
query's results at that moment.

Is there a way to do this?

Or can a report or a Jet query be based on a SQL Express back end query?
I'm so accustomed to working with ADPs that I guess this just hadn't
come up before. Naturally, I had assumed I could link to all these SQL
Express objects, not just the tables. (Well, it seems natural to me!)

Do you know?

Tom Ellison








.

Relevant Pages

  • Re: GROUP BY vs DISTINCT
    ... In Jet, the second query uses the same plan than MS SQL ... MS SQL Server uses 1 UET! ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: upsizing to sql 2005
    ... A query in Access-Jet will be started and under the supervision of Jet, ... Jet syntax parsing and evaluation. ... so, in this case, into MS SQL Server. ...
    (microsoft.public.access.queries)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: how do i get a query to work?
    ... > I need to know what a Query is, ... MS Access and JET are one such ... This is a key aspect of RDBMSes: The use of SQL to ask questions ... about data in Tables through the Database Manager. ...
    (microsoft.public.access.gettingstarted)