Access 2010 & Pass-through queries (1 Viewer)

I am beginning to test an Access front-end with a SQL Server back-end. In the end, my intention is to morph the SQL Server db into an Azure version of SQL Server and run my Access front-end with Azure as the back-end.

It has been implied that unless you use stored procedures or pass-through queries instead of Access queries, the speed may not be pleasant. Is this true? If it is true then I need to bone-up on SQL and stored procedures and how to call them (sp's) from within Access. I have used SQL Server int he past, but it's been a while.

But there is one thing that confuses me right away. My understanding is that you can not edit the results of a pass-through query. I can see the benifit of pass-through queries for things like reports, but if you can't edit the result set of a pass-through query they seem not useable when you are retrieving data for a form? Is this right? Lets say I have a large SQL db of orders but I only want to retrieve a certain order and its associated detail records in a 1:m form.

Is an Access query adequate for this? A pass-through query does not seem an option as you can't edit the answer set. How should I be looking at this? What am I missing?

Woody, for updateable records you may want to take a look at ADO.

Thanks for responding Bob.

I am familiar with ADO but not sure what you're getting at. Whether you're using a an Access query, an embedded query or ADO you're constructing a query Yes/No? I guess to me the question is do I need to construct some sort of special query when using SQL Server as the backend or will the way I do things normally in Access be sufficient?

Best Regards

Hi to All!

I too would like to get an answer for this question.
If I replace an Access 2003 BE with a SQL server DB, using Pass-through queries, do I loos the easy development? why would Using ADO be better than replacing the FE wiyh a .net UI, using ADO.net?

marlan said:

If I replace an Access 2003 BE with a SQL server DB, using Pass-through queries, do I loos the easy development?

Click to expand...

ADO is just a way the data is connected between the server and whatever frontend the developer cares to use. Excel or Word can be used as a front end if you like.

It continues to be easy so long as you know how to make a database in the server and how to connect using ADO.

why would Using ADO be better than replacing the FE wiyh a .net UI, using ADO.net?

Click to expand...

Access is better if you don't have or know how to work with the other tools. If you already have an Access front end then you would need a reason to go to the trouble of reconstructing it in another environment.

Hi GalaxiomAtHome, thanks for your reply,

By writing easy development, I refer to the fact that by using a .mdb BE, simple user data modifications are done by defining a table or query as a form's RecordSource, and a field as a conrol's dataSaource, and the developer doesn't have to write the "regular" SQLs.
Can this still be done using ADO and Pass-through queries, or do I have to modify all my forms to have DB Abilities?

Any part of your data can come from embedded tables, linked tables or server tables. It doesn't all have to be done from one place. You can even link a table from SQL server just like linking from another Access file. Linking is a good place to start with connections to SQL server then work towared the more sophisticated connections. The ADO is just them most elegant and powerful connection.

ADO recordsets come in several different types that support read only, append only going right through to the full dynaset capacity found in embedded or linked Acccess tables.

ADO is based on an SQL statement very like an Access query with some very small changes that also apply to PassThrough.

It you are not confident in SQL, maintain an Access table equivalent to the server table. Write your query in the Acceses desiger and then switch it to SQL view. Copy and paste the sql text to the recordset statement that uses the ADO connection.

The main difference in SQL server is the date delimiter uses single quotes instead of # used in Access. There are some functions that don't work too but don't worry about these until you encounter them. When something doesn't work just remember it might be one of these unsupported functions rather than bad syntax per se.

GalaxiomAtHome said:

Any part of your data can come from embedded tables, linked tables or server tables. It doesn't all have to be done from one place.

Click to expand...

Well, you gave a few things to think about, befor I go and upgrade this DB.
Allwase good to think before you work, nd better to have people help you think.
Thank you!