Access front end for sql server

[Gwindor]

I have been doing programming in Access for about 10 years. I am now
being asked to make an Access front end for data that is stored in SQL
Server.

My impression is that the only way to do this is to use a .adp
application, rather than an .mdb file. Is that correct?

Is it possible to use DAO or ADO with .adp objects?

Also, is there a good manual on making access front ends for SQL
server data?

Gwindor

[Tom van Stiphout]

On Sat, 28 Jun 2008 10:52:40 -0400, Gwindor <gw*****@verizon.net>
wrote:

No, you can also create an ODBC connection to the SQL Server and stay
within the familiar MDB environment.
It appears MSFT is phasing out support for ADP.

-Tom.

>I have been doing programming in Access for about 10 years. I am now
being asked to make an Access front end for data that is stored in SQL
Server.

My impression is that the only way to do this is to use a .adp
application, rather than an .mdb file. Is that correct?

Is it possible to use DAO or ADO with .adp objects?

Also, is there a good manual on making access front ends for SQL
server data?

Gwindor

[Albert D. Kallal]

"Gwindor" <gw*****@verizon.netwrote in message
news:3m********************************@4ax.com...

>I have been doing programming in Access for about 10 years. I am now
being asked to make an Access front end for data that is stored in SQL
Server.

My impression is that the only way to do this is to use a .adp
application, rather than an .mdb file. Is that correct?

No, hopefully, if you been using ms-access, then you been splitting your
databases for years?

>
Is it possible to use DAO or ADO with .adp objects?

When you go ADP, you can't use DAO, it all ADO by default. And, ADP projects
are ONLY for sql server. if you use linked tables, then you can use just
about any database server.

For this reason, for existing applications, we tend to prefer using linked
tables. Those tables can be linked to your back end (mdb), or linked to sql
server. When you do this linking, most, if not all of your code and
application sill simply function as before.

This means that you can develop the application, then split it. make sure it
works, and then upgrade the back end to sql server, and then link the front
end to that.

Here is some links that should be of use:

http://support.microsoft.com/default...19&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Cente

The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.

http://support.microsoft.com/?id=128384

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Tony Toews [MVP]]

Tom van Stiphout <no*************@cox.netwrote:

>It appears MSFT is phasing out support for ADP.

MSFT certainly haven't made any enhancements to ADPs for the last few versions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Gwindor]

Thanks. ODBC works fine for me. At least, it works fine if I can
make the connection.

I have tried a few different tricks to create a connection, but the
connection fails. Could this be because I am working with Access 2003
and my sql server is SQL Server Express 2005?

(My client has a full SQL Server copy at his location, but here at my
office on my PC I am dealing with SQL Express 2005.)

Gwindor

On Sat, 28 Jun 2008 08:22:35 -0700, Tom van Stiphout
<no*************@cox.netwrote:

>On Sat, 28 Jun 2008 10:52:40 -0400, Gwindor <gw*****@verizon.net>
wrote:

No, you can also create an ODBC connection to the SQL Server and stay
within the familiar MDB environment.
It appears MSFT is phasing out support for ADP.

-Tom.

>>I have been doing programming in Access for about 10 years. I am now
being asked to make an Access front end for data that is stored in SQL
Server.

My impression is that the only way to do this is to use a .adp
application, rather than an .mdb file. Is that correct?

Is it possible to use DAO or ADO with .adp objects?

Also, is there a good manual on making access front ends for SQL
server data?

Gwindor

[Tom van Stiphout]

On Sat, 28 Jun 2008 14:22:15 -0400, Gwindor <gw*****@verizon.net>
wrote:

No.
Likely a permissions issue. Are you getting any error messages?
Compare your connectionstring with those posted at
www.connectionstrings.com

-Tom.

>Thanks. ODBC works fine for me. At least, it works fine if I can
make the connection.

I have tried a few different tricks to create a connection, but the
connection fails. Could this be because I am working with Access 2003
and my sql server is SQL Server Express 2005?

(My client has a full SQL Server copy at his location, but here at my
office on my PC I am dealing with SQL Express 2005.)

Gwindor

On Sat, 28 Jun 2008 08:22:35 -0700, Tom van Stiphout
<no*************@cox.netwrote:

>>On Sat, 28 Jun 2008 10:52:40 -0400, Gwindor <gw*****@verizon.net>
wrote:

No, you can also create an ODBC connection to the SQL Server and stay
within the familiar MDB environment.
It appears MSFT is phasing out support for ADP.

-Tom.

>>>I have been doing programming in Access for about 10 years. I am now
being asked to make an Access front end for data that is stored in SQL
Server.

My impression is that the only way to do this is to use a .adp
application, rather than an .mdb file. Is that correct?

Is it possible to use DAO or ADO with .adp objects?

Also, is there a good manual on making access front ends for SQL
server data?

Gwindor

[lyle fairfield]

That's just like my dates with redheads. Of course, I don't have any,
which is to say they work fine for me except they don't work at all.

On Jun 28, 2:22*pm, Gwindor <[e-mail]:

Thanks. *ODBC works fine for me. *At least, it works fine if I can
make the connection.

[lyle fairfield]

How unfamiliar could an ADP environment be?

Will form design be different? I don't think so.
Will report design be different? Not in my experience.
How about code? Well, we use ADO rather than DAO. Is this tough? Given
that ADO has an extensive and easily accessed object model with
numerous examples, while the secrets of DAO are hidden among the tombs
of MVP ancestors I can't understand how it could be. How difficult is
it to type "Find" rather than "FindFirst" anyway? C'mon now; hands up
those who knew that we can use the ` (lower case tilde) in place of
square brackets in JET sql?
Well what will be different? QUERY DESIGN! We'll be able to design our
queries and views in T-SQL; if Jet SQL and T-SQL were in the hockey
business JET MIGHT get to carry T-SQL's skates into the arena and wash
his jock-strap after the game, but T-SQL would be the big handsome guy
on the ice scoring all the goals.
But what about Access's interactive Query Wizard. Won't we miss it?
C'mon! Is this a PIG or is Oink a Conservative Election Slogan? Should
you miss it you can download the superb and free SQL Server Mangement
SQL Express utility and see how things can work in the real Database
world. Did I mention UDFs? And linked servers? Sure we can use these
with OBDC but is there any encouragement to do so?
Oh, Oh, Local tables. Gonna get me with Local Tables right? Well, of
course you can save any recordset, locally, in ADTG or XML format and
access it with a simple open command.
If I were writing this in response to most, Tom, I would say, I can do
more with an ADP than with an MDB, and I can do more with an MDB than
you can. But since I'm responding to you, I'll just say I can do a lot
with an MDB, and even more with an ADP.

On Jun 28, 11:22*am, Tom van Stiphout <[e-mail]:

On Sat, 28 Jun 2008 10:52:40 -0400, Gwindor <[e-mail]>
wrote:

No, you can also create an ODBC connection to the SQL Server and stay
within the familiar MDB environment.
It appears MSFT is phasing out support for ADP.

-Tom.

I have been doing programming in Access for about 10 years. *I am now
being asked to make an Access front end for data that is stored in SQL
Server.

My impression is that the only way to do this is to use a .adp
application, rather than an .mdb file. *Is that correct?

Is it possible to use DAO or ADO with .adp objects?

Also, is there a good manual on making access front ends for SQL
server data?

Gwindor

[lyle fairfield]

I think this is not correct, For instance, Access 2003 ADPs allow
setting an ADO recordset as a report's recordset. Access 2002 ADPs,
(nor any MDBs, TTBOMK) does not have this capability.

The general notion among MDB aficionados that ADPs are separate but
equal is prompted, IMO, by the those aficionados' lack of ADP
experience. ADPs are superior creatures in many ways.

On Jun 28, 1:04*pm, "Tony Toews [MVP]" <[e-mail]:

Tom van Stiphout <[e-mail]:

It appears MSFT is phasing out support for ADP.

MSFT certainly haven't made any enhancements to ADPs for the last few versions.

[Tom van Stiphout]

On Sat, 28 Jun 2008 17:57:29 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:

Try running an ADP in the A2007 runtime, and you'll see that MSFT's
support is dwindling. I haven't tried it yet after SP1 came out, but
out of the chute it was HORRIBLE.
I was an afficionado for a long time, and we have built several large
ADP-based apps for our clients, but I have had to reconsider that
position.

-Tom.

>I think this is not correct, For instance, Access 2003 ADPs allow
setting an ADO recordset as a report's recordset. Access 2002 ADPs,
(nor any MDBs, TTBOMK) does not have this capability.

The general notion among MDB aficionados that ADPs are separate but
equal is prompted, IMO, by the those aficionados' lack of ADP
experience. ADPs are superior creatures in many ways.

On Jun 28, 1:04*pm, "Tony Toews [MVP]" <[e-mail]:

>Tom van Stiphout <[e-mail]:

>It appears MSFT is phasing out support for ADP.

MSFT certainly haven't made any enhancements to ADPs for the last few versions.

[Larry Linson]

"lyle fairfield" <ly************@gmail.comwrote

I think this is not correct, For instance, Access 2003
ADPs allow setting an ADO recordset as a report's
recordset. Access 2002 ADPs, (nor any MDBs,
TTBOMK) does not have this capability.

As far as I have been able to determine, there were no "enhancements" to ADP
in Access 2007, though there were some "make it work" changes.

The general notion among MDB aficionados that
ADPs are separate but equal is prompted, IMO, by
the those aficionados' lack of ADP experience.
ADPs are superior creatures in many ways.

I would have to defer to Lyle's obviously-more-extensive experience with
ADP, but my meager experience with ADP only indicated that using it was not
as onerous as some have thought, but I could see no compelling advantage in
the (relatively straightforward) processing that was done in that
application.

ADO may, as Lyle has stated, have a "simpler object model", but all the same
information is required... it's just that some of it is being provided in a
different way. I'm sure it it good to have "choice" in how you do things,
but the fact that ADO has several ways to accomplish most operations will
mean a learning curve for even accomplished programmers whose previous
experience with Access has only included DAO.

My view may be affected by the fact that the design and implementation of
the ADP application I was engaged to modify was atrocious, but with the same
author, I admit that it would likely have been equally atrocious if it had
been an MDB using DAO. From the programming style, I would guess the person
was a "refugee from VB", not at all familiar with database.

The client, however, was adamant that there be no changes other than the
functional enhancements they had identified, because the function was to be
included in the enterprise requirement planning ERP system they were
implementing, in "just a matter of a few months" and they just wanted to
nurse the Access / SQL Server app along until it the ERP system was ready.

I was not hired to advise on the ERP project, so I didn't. However, the
follow-up is: I know my colleague on that project was still doing minor
changes to the Access application over two years later, so I have to guess
the "few months" stretched a little. That's a lot better than many ERP
projects, which soak up resources for years before being cancelled and
abandoned.

Larry

[susheel kumar]

how to create backend in sqlserver and frontend access
*** Sent via Developersdex http://www.developersdex.com ***