SQL Express query in MDB

[Tom Ellison]

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

 

[MH]

Hello Tom,

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

MH

 

[Tom Ellison]

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]

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

 

[Aaron Kempf]

dude.. MDB is obsolete

use Access Data Projects

 

[Michael Gramelspacher]

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

Tom,

After seeing your post I created my first view ever on SQL Express. I
changed my form recordsource to the view and made some record changes.
After moving to the next record and back my changes still were there. From
my limited test it seems like it works as it should. All my forms and
reports running against the view are extremely fast - they are there as
fast as the window can repaint - with 4,000 obituary records.
I think I read in Andy Baron's white paper something about when changes are
written to the server, but maybe this pertains to forms linked to tables.
In order to use the view I had to run a replace of qryObituaries with
dbo_qryObituaries for all VBA, which was simple. Changing all the queries
by editing the query text in SQL viewwas simple, but time consuming.

Mike

 

[Tom Ellison]

Dear Michael:

I'm surprised. After some testing, this "table" turns out to be dynamic.
If the data behind the query changes, this "table" also changes. It is
surprising that it is called a "table." That seems quite misleading.

My primary problem was that this works, but not like in an ADP, as I'm
accustomed. Well, problem solved! Thanks very much.

Tom Ellison

 

[MH]

You're welcome Tom.

MH

Dear Michael:

I'm surprised. After some testing, this "table" turns out to be dynamic.
If the data behind the query changes, this "table" also changes. It is
surprising that it is called a "table." That seems quite misleading.

My primary problem was that this works, but not like in an ADP, as I'm
accustomed. Well, problem solved! Thanks very much.

Tom Ellison