Unbound Forms - data in SQL Server - advice please

[Paul Kraemer]

Hi,

I have created an unbound form in Access 2007 with some controls on it. I
have a table in a MS SQL Server 2005 database with fields that correspond to
the controls on my unbound form.

I want to write code that will request one (and only one) record from the
SQL server table based on an ID field that I want to pass as a parameter. I
want to use this one row recordset to populate the controls on my unbound
form. I want the SQL server backend to do all the work in retrieving this
record. I want the Access front end to do as little as possible.

I am trying to find out the best way to do this. Should I first create a
stored procedure in SQL Server that takes one parameter for the ID and
returns the corresponding row from the table? Then should I create a
pass-through query in Access that calls the SQL Server stored procedure? If
so, what is the best way to pass a parameter to the stored procedure? Do I
have to use string concatenation to place the parameter value in the middle
of the SQL string that is tied to the pass-thru query? Or is there a better
way to pass parameters?

Thanks in advance,
Paul

[Mary Chipman [MSFT]]

There's several ways you can tackle the problem. It gets tricky when
there's a 1-many relationship with a subform. If it's a single table,
you can bind controls to a single row the user can select from a combo
box. If you go with unbound forms, then you'll need to handle
concurrency and form state on the front end, so having a relational
schema where multiple users aren't updating the same row is important.
If you use pass-through queries to stored procedures, you can
programmatically set the .SQL property of a QueryDef object to pass
the parameter value. I should have a link where you can download
sample code in the next few days -- stay tuned :)

--Mary

On Wed, 11 Jun 2008 10:27:00 -0700, Paul Kraemer

Post by Paul Kraemer
Hi,
I have created an unbound form in Access 2007 with some controls on it. I
have a table in a MS SQL Server 2005 database with fields that correspond to
the controls on my unbound form.
I want to write code that will request one (and only one) record from the
SQL server table based on an ID field that I want to pass as a parameter. I
want to use this one row recordset to populate the controls on my unbound
form. I want the SQL server backend to do all the work in retrieving this
record. I want the Access front end to do as little as possible.
I am trying to find out the best way to do this. Should I first create a
stored procedure in SQL Server that takes one parameter for the ID and
returns the corresponding row from the table? Then should I create a
pass-through query in Access that calls the SQL Server stored procedure? If
so, what is the best way to pass a parameter to the stored procedure? Do I
have to use string concatenation to place the parameter value in the middle
of the SQL string that is tied to the pass-thru query? Or is there a better
way to pass parameters?
Thanks in advance,
Paul

[Paul Kraemer]

Hi Mary,

Thanks for the resonse. Any sample code or reference material you can point
me toward would be greatly appreciated.

Forgive me if this is off topic, but I own a copy of your book "MS Access
Developer's Guide to SQL Server". I read it a long time ago and thoroughly
enjoyed it. Back then, I was thinking about converting an in house Access
application to use a SQL Server backend. Other projects ended up taking
priority so that got put on the back burner, but now I think I am ready to
try to tackle it again. I can give your book another read, but I would be
afraid that there might be things that have changed or newer technigues that
were not around at the time you wrote the book. As I am doing this mostly as
a learning experience, I really want to make sure I go about it in the most
up-to-date, currently preferred, most adaptable toward the future way. Do
you have any plans of writing a new edition of this book? If not, are there
any other books you would recommend?

Thanks again,
Paul
--
Paul Kraemer

Post by Mary Chipman [MSFT]
There's several ways you can tackle the problem. It gets tricky when
there's a 1-many relationship with a subform. If it's a single table,
you can bind controls to a single row the user can select from a combo
box. If you go with unbound forms, then you'll need to handle
concurrency and form state on the front end, so having a relational
schema where multiple users aren't updating the same row is important.
If you use pass-through queries to stored procedures, you can
programmatically set the .SQL property of a QueryDef object to pass
the parameter value. I should have a link where you can download
sample code in the next few days -- stay tuned :)
--Mary
On Wed, 11 Jun 2008 10:27:00 -0700, Paul Kraemer

Post by Paul Kraemer
Hi,
I have created an unbound form in Access 2007 with some controls on it. I
have a table in a MS SQL Server 2005 database with fields that correspond to
the controls on my unbound form.
I want to write code that will request one (and only one) record from the
SQL server table based on an ID field that I want to pass as a parameter. I
want to use this one row recordset to populate the controls on my unbound
form. I want the SQL server backend to do all the work in retrieving this
record. I want the Access front end to do as little as possible.
I am trying to find out the best way to do this. Should I first create a
stored procedure in SQL Server that takes one parameter for the ID and
returns the corresponding row from the table? Then should I create a
pass-through query in Access that calls the SQL Server stored procedure? If
so, what is the best way to pass a parameter to the stored procedure? Do I
have to use string concatenation to place the parameter value in the middle
of the SQL string that is tied to the pass-thru query? Or is there a better
way to pass parameters?
Thanks in advance,
Paul

[Mary Chipman [MSFT]]

The book pretty much covers all of your options. Things haven't
changed that much over the years when it comes to using
Access-Jet-ODBC-SQL Server - all of the samples work with Access 2007
and SQL Server 2005. Ignore the ADP chapters -- that code won't work
and it isn't the recommended approach today. The book takes you into
realms where you probably will never need (or want) to go (e.g.,
compiling class modules into VB6 DLLs and deploying in COM+).

In terms of unbound forms, it depends on what your deployment model
is. If your application is two-tier (client-server), then Armen's
techniques will be less work. If you need the application to scale in
an n-tier environment, then unbound forms can potentially support an
unlimited number of users (given a sound relational architecture).
Another driver might be security - if all data access is through
stored procedures, then you'll need unbound forms/pass-through
queries/recordsets so you can pass input parameters and process output
parameters.

--Mary

On Thu, 12 Jun 2008 10:19:00 -0700, Paul Kraemer

Post by Paul Kraemer
Hi Mary,
Thanks for the resonse. Any sample code or reference material you can point
me toward would be greatly appreciated.
Forgive me if this is off topic, but I own a copy of your book "MS Access
Developer's Guide to SQL Server". I read it a long time ago and thoroughly
enjoyed it. Back then, I was thinking about converting an in house Access
application to use a SQL Server backend. Other projects ended up taking
priority so that got put on the back burner, but now I think I am ready to
try to tackle it again. I can give your book another read, but I would be
afraid that there might be things that have changed or newer technigues that
were not around at the time you wrote the book. As I am doing this mostly as
a learning experience, I really want to make sure I go about it in the most
up-to-date, currently preferred, most adaptable toward the future way. Do
you have any plans of writing a new edition of this book? If not, are there
any other books you would recommend?
Thanks again,
Paul

[a***@gmail.com]

where do you get off? talking shit about ADP like that?
Just because 80% of the Access dipshits around here don't have the
mental capacity to learn SQL Server-- does that mean that it's not the
way to go?

Just because one person doesn't reccomend it-- it doesn't mean that
it's not the reccomended way to go.

Stop giving bad advice.

-Aaron


On Jun 16, 10:05 am, "Mary Chipman [MSFT]"

Post by Mary Chipman [MSFT]
The book pretty much covers all of your options. Things haven't
changed that much over the years when it comes to using
Access-Jet-ODBC-SQL Server - all of the samples work with Access 2007
and SQL Server 2005.  Ignore the ADP chapters -- that code won't work
and it isn't the recommended approach today. The book takes you into
realms where you probably will never need (or want) to go (e.g.,
compiling class modules into VB6 DLLs and deploying in COM+).
In terms of unbound forms, it depends on what your deployment model
is. If your application is two-tier (client-server), then Armen's
techniques will be less work. If you need the application to scale in
an n-tier environment, then unbound forms can potentially support an
unlimited number of users (given a sound relational architecture).
Another driver might be security - if all data access is through
stored procedures, then you'll need unbound forms/pass-through
queries/recordsets so you can pass input parameters and process output
parameters.
--Mary
On Thu, 12 Jun 2008 10:19:00 -0700, Paul Kraemer

Post by Paul Kraemer
Hi Mary,
Thanks for the resonse.  Any sample code or reference material you can point
me toward would be greatly appreciated.  
Forgive me if this is off topic, but I own a copy of your book "MS Access
Developer's Guide to SQL Server".  I read it a long time ago and thoroughly
enjoyed it.  Back then, I was thinking about converting an in house Access
application to use a SQL Server backend.  Other projects ended up taking
priority so that got put on the back burner, but now I think I am ready to
try to tackle it again.  I can give your book another read, but I would be
afraid that there might be things that have changed or newer technigues that
were not around at the time you wrote the book.  As I am doing this mostly as
a learning experience, I really want to make sure I go about it in the most
up-to-date, currently preferred, most adaptable toward the future way.  Do
you have any plans of writing a new edition of this book?  If not, are there
any other books you would recommend?
Thanks again,
Paul

[Armen Stein]

On Wed, 11 Jun 2008 10:27:00 -0700, Paul Kraemer

Post by Paul Kraemer
I want to write code that will request one (and only one) record from the
SQL server table based on an ID field that I want to pass as a parameter. I
want to use this one row recordset to populate the controls on my unbound
form. I want the SQL server backend to do all the work in retrieving this
record. I want the Access front end to do as little as possible.

From a practical point of view, I have to question your requirement of
using an unbound form. Unbound forms can provide the maximum
performance, but they require more development time and complexity.

In our shop, we've used a technique where we show read-only lists of
records using a passthrough query. When the user drills down to
update a particular record, we open only that *one* record using a
normal detail form bound to a linked table. We even have bound
subforms with child tables if necessary, as long as there aren't too
many child records. Access and SQL Server work very well together
using this technique, and you don't have to code all the unbound
logic.

We've built dozens of large, complex systems with these techniques and
they work well in situations with many simultaneous users.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Paul Kraemer]

Hi Armen,

Thanks for your response. It is not a requirement that I use an unbound
form. Right now, I am just trying to create a small "practice" app just so I
can get my feet wet learning a few different techniques. I am going to
re-read Mary's book. Looking quickly, it seems like she has a section that
describes doing pretty much what you describe so I will definitely experiment
with that (if you can recommend any sample apps or additional reading
material that might be helpful, I'd really appreciate it). I probably will
also experiment with unbound forms just so I can get a first-hand idea of how
much addtional work this requires.

I hope to gain some confidence in this. The company I work for has a
production application that is written in access that is definitely running
into performance issues. I think they will need to upsize in the near
future. Given the size of the job, I don't think i can hope to be able to do
it myself so they will probably have to hire outside help, but maybe I can at
least make myself a little more useful.

I will keep reading and keep working on my practice app. I am sure I will
come up with some more questions as I get deeper into it.

Thank you and Mary so much for your help. I do appreciate it.

Regards,
Paul
--
Paul Kraemer

Post by Mary Chipman [MSFT]
On Wed, 11 Jun 2008 10:27:00 -0700, Paul Kraemer

Post by Paul Kraemer
I want to write code that will request one (and only one) record from the
SQL server table based on an ID field that I want to pass as a parameter. I
want to use this one row recordset to populate the controls on my unbound
form. I want the SQL server backend to do all the work in retrieving this
record. I want the Access front end to do as little as possible.

From a practical point of view, I have to question your requirement of
using an unbound form. Unbound forms can provide the maximum
performance, but they require more development time and complexity.
In our shop, we've used a technique where we show read-only lists of
records using a passthrough query. When the user drills down to
update a particular record, we open only that *one* record using a
normal detail form bound to a linked table. We even have bound
subforms with child tables if necessary, as long as there aren't too
many child records. Access and SQL Server work very well together
using this technique, and you don't have to code all the unbound
logic.
We've built dozens of large, complex systems with these techniques and
they work well in situations with many simultaneous users.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Armen Stein]

On Tue, 17 Jun 2008 08:31:01 -0700, Paul Kraemer

Post by Paul Kraemer
Thanks for your response. It is not a requirement that I use an unbound
form. Right now, I am just trying to create a small "practice" app just so I
can get my feet wet learning a few different techniques. I am going to
re-read Mary's book. Looking quickly, it seems like she has a section that
describes doing pretty much what you describe so I will definitely experiment
with that (if you can recommend any sample apps or additional reading
material that might be helpful, I'd really appreciate it). I probably will
also experiment with unbound forms just so I can get a first-hand idea of how
much addtional work this requires.

Mary and I just co-presented a session at TechEd in Orlando a few
weeks ago. It covered some techniques to use for client-server
development using Access and SQL Server. You can get the slides and
materials (including a sample Access application and SQL database I
showed) at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp. It's
the second download on the list (Session DAT303).

You should also check out Best of Both Worlds on that same list also.

Post by Paul Kraemer
I hope to gain some confidence in this. The company I work for has a
production application that is written in access that is definitely running
into performance issues. I think they will need to upsize in the near
future. Given the size of the job, I don't think i can hope to be able to do
it myself so they will probably have to hire outside help, but maybe I can at
least make myself a little more useful.

For a production application, outside help might be a good idea. We've
assisted with just this kind of project before. This kind of
collaboration can turn out to be a benefit for the company and a
jumpstart for the internal developer.

Post by Paul Kraemer
I will keep reading and keep working on my practice app. I am sure I will
come up with some more questions as I get deeper into it.

Keep the questions coming!

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Paul Kraemer]

Hi Armen,

Thanks for the response. Are you sure the link you sent is correct? I get
a "page not found" message. Sorry for the trouble.

Regards,
Paul
--
Paul Kraemer

Post by Armen Stein
On Tue, 17 Jun 2008 08:31:01 -0700, Paul Kraemer

Post by Paul Kraemer
Thanks for your response. It is not a requirement that I use an unbound
form. Right now, I am just trying to create a small "practice" app just so I
can get my feet wet learning a few different techniques. I am going to
re-read Mary's book. Looking quickly, it seems like she has a section that
describes doing pretty much what you describe so I will definitely experiment
with that (if you can recommend any sample apps or additional reading
material that might be helpful, I'd really appreciate it). I probably will
also experiment with unbound forms just so I can get a first-hand idea of how
much addtional work this requires.

Mary and I just co-presented a session at TechEd in Orlando a few
weeks ago. It covered some techniques to use for client-server
development using Access and SQL Server. You can get the slides and
materials (including a sample Access application and SQL database I
showed) at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp. It's
the second download on the list (Session DAT303).
You should also check out Best of Both Worlds on that same list also.

Post by Paul Kraemer
I hope to gain some confidence in this. The company I work for has a
production application that is written in access that is definitely running
into performance issues. I think they will need to upsize in the near
future. Given the size of the job, I don't think i can hope to be able to do
it myself so they will probably have to hire outside help, but maybe I can at
least make myself a little more useful.

For a production application, outside help might be a good idea. We've
assisted with just this kind of project before. This kind of
collaboration can turn out to be a benefit for the company and a
jumpstart for the internal developer.

Post by Paul Kraemer
I will keep reading and keep working on my practice app. I am sure I will
come up with some more questions as I get deeper into it.

Keep the questions coming!
Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Armen Stein]

On Wed, 18 Jun 2008 05:23:02 -0700, Paul Kraemer

Post by Paul Kraemer
Thanks for the response. Are you sure the link you sent is correct? I get
a "page not found" message. Sorry for the trouble.

Hmm. This exact link opens for me in FF2 and IE7:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

If it still gives you trouble, try www.JStreetTech.com, then click
Custom Programming, then Downloads.

If you continue to have a problem with the link, feel free to email me
at Armen at JStreetTech dot com and we'll figure it out.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Paul Kraemer]

Hi Armen,

The link worked this time. Thanks again.

Paul
--
Paul Kraemer

Post by Armen Stein
On Wed, 18 Jun 2008 05:23:02 -0700, Paul Kraemer

Post by Paul Kraemer
Thanks for the response. Are you sure the link you sent is correct? I get
a "page not found" message. Sorry for the trouble.

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
If it still gives you trouble, try www.JStreetTech.com, then click
Custom Programming, then Downloads.
If you continue to have a problem with the link, feel free to email me
at Armen at JStreetTech dot com and we'll figure it out.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com