Do I really need Tables with SQL Backend (1 Viewer)

[SmallTime]

Do I really need linked Tables with SQL Backend

Hi all,

I've recently started to convert an access database BE to SQL Server and am using pass through queries to retrieve data from stored procedures. On the face of it this seems to imply that I can now do away with linked tables altogether. In fact I can't at this moment see why I should have any linked table at, as all is working fine with just the pass through queries.

Admittedly I've only just started wetting my feet with SQL server as a BE and would be grateful for opinions on why I would still need linked tables. If I do away with them is there an ugly beast waiting for me down the way?


Many thanks
SmallTime

 

[pdx_man]

If you have no forms, then there would be no reason to "require" linked tables.

 

[SmallTime]

Hi

I have forms, queries and temp tables that are based on pass through queries or access native queries based on PT's. I'm also updating records on sql server using Sproc's, so far everything's working fine without the need for linked tables. Which is making me wonder why I should have them at all.

Must admit I've only recently started messing about with SQL server BE and have previously always had linked tables to an access BE so the idea of not having any linked tables is a little scary, It's purely academic but the thought does occur.

Regards
SmallTime

 

[Fear Naught]

I have just experimented with Pass Through Queries (PTQ) for the first time as in the past I have always used linked tables. I can certainly see the benefit of PTQ as it seems to take the processing away from the local PC and Jet Engine.

However what I can't work out is how to use them with Forms for adding and updating records. SELECT queries are one thing but I can't work out how to use Stored Procedures in Access (in fact I know little about them).

Can somebody either explain (in laymans lanquage) or point me to a decent book or URL that will help.

Thanks.

 

[SmallTime]

I'm using procedures that are written and stored within SQL Server and then executed from within a PTQ to update records. The downside is that oftentimes I'm having to construct the PTQ statements in VBA on the fly. not always the easiest of tasks.

SmallTime

 

[Fear Naught]

How do you execute a Stored Procedure in a PTQ?

 

[Banana]

I personally used all tools since they are great in their own ways:

1) I use linked tables whenever I need an updatable query that I can use for form's recordsource. Almost most of the time, the form have one-to-one correspondence to the tables (subforms helps make this happen by representing the child tables so the parent form only really need the parent table)).

2) I use pass-through query for reports. Because they're non-updatable, there's no reason to not use PTQs.

3) In some corner cases where we require a particularly complex form and using a linked table & updatable query is not paticularly efficient, I may use...

a) a SQL View
b) an ADO recordset bound to a stored procedure that's also updatable

The common cases where we need #3 is whenever we need to perform several calculations but also allow updates to noo-calculated fields. Doing calculations can be problematic since it may sometime render an Access query non-updatable and doing it in code using additional queries or domain functions are going to make everything much slower. So there, I consider it worthwhile the extra efforts required to construct a SQL View and/or an updatable ADO recordset. But for most well-designed forms, linked tables & Access query work quite wonderfully and are already efficient (the real problem is that if you force Access to do something that can't be expressed in SQL, Access has no choice but to pull in more than needed so it can do the evaluations for you. If you're careful with how you write SQL, it's smart enough to leave the processing entirely to the backend).