Passing Parameters between Access Project and SQL

[jmillerWV]

I hope I am posting to the correct area if not please let me know. I am
preparing to upsize my Access DB to SQL server 2000 (Small Business Server
2k3). I have many Queries in Access that use data from forms as parameters.
Question: how do I pass data from my forms (Access project) to
Procedures/functions in SQL? Example I have a form that allows the user to
select a county in the state to see what orders have been placed today, this
is to be able to give these to our filed people in that county. the query
uses the county and state off of the form to get the list. This is simple one
I have others that are a lot more complex. I just need a starting point.
Thanks in advance for any assistance.

 

[Tom van Stiphout]

On Wed, 3 Dec 2008 15:53:01 -0800, jmillerWV

The subject of this forum is ADP, the special flavor of Access
designed to work with a SQL Server back-end. It has a special
InputParameters property that would allow you to pass arguments to an
sproc. For example it could say:
@CustomerID = Forms!FindCustomer!cboCustomerID

Some people see the writing on the wall that MSFT is no longer
actively supporting ADP.

-Tom.
Microsoft Access MVP

 

[jmillerWV]

Thanks for the reply. May I take from your comment that I would be better
served to find a different frontend? Like an Access *.mdb instead of a *.adp?
If so can you make a suggestion of the best interface for a SQL backend?
Again thanks.
jmillerwv

 

[Mary Chipman [MSFT]]

Here are a list of resources that will help you figure out the best
solution for your upsized application. There isn't any "one size fits
all" solution, as I'm sure you already know

--Mary

TechEd Online Panel (video):
Go to http://msdn.microsoft.com/en-us/events/teched/cc676818.aspx and
search for:
"Are we there yet? Successfully navigating the bumpy road from Access
to SQL Server"

Microsoft Access or SQL Server 2005: What's Right in Your
Organization?
http://www.microsoft.com/sql/solutions/migration/access/sql-or-access.mspx

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

What are the main differences between Access and SQL Server?
http://sqlserver2000.databases.aspf...ifferences-between-access-and-sql-server.html

"The Best of Both Worlds--Access MDBs and SQL Server"
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

SQL Server Migration Assistant for Access (SSMA for Access)
http://www.microsoft.com/sql/solutions/migration/access/default.mspx

FMS Upsizing Center
http://www.fmsinc.com/Consulting/sqlupsizedocs.aspx

Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446

 

[jmillerWV]

Again thanks for the reply. I will check out the links you supplied and go
from there. I will do some testing to see if there is a preformance hit using
a *.mdb.

jmillerwv

 

[Mary Chipman [MSFT]]

Performance depends on your database and application architecture, not
on whether it's an mdb or adp. You can get decent or abysmal
performance from either.

--Mary

 

[Sylvain Lafontaine]

You should try both - MDB and ADP - to see which one is best serving your
needs.

In term of support and commitment from MS, ADP is actually going down but on
the other side, the capabilities offered by MDB are pretty much limited; so
you have a conundrum here: using something weaker but with a better future
from MS or using something more powerful but with a dimmer future.

In my personal opinion, I would be really surprised if MS was to remove the
actual support for using a SP as the record source of form, subform, report
and subreport that you will find in ADP because it's really important for
many big companies; so the worst case scenario would probably be the
integration of the actual capabilities of ADP directly into the ACCDB format
in a future version of Access; which would mean that you would lose nothing
in the change. However, this is only my personal opinion.

If you want a suggestion about the best interface for a SQL backend in term
of both power and commitment from MS, there is only one option at this
moment and it's the .NET framework. This is where MS is actually putting
much of its development's money but it's not the easiest option to learn and
use. MDB/ACCDB and ADP applications are easier to use but JET has
practically not changed at all since 1995 and as for ADP, you'll see
constant postings from some MS guys telling you that's better instead to go
back in the past with JET.