Conversion Access MDB to ADP & MSDE 2000

[Greg Strong]

Hello All,

Hello All,

What are the ToDo's / Gotchas to convert an MDB Access 2K2 database to
an Access Project (i.e. ADP) file for the front end using Microsoft
SQL Server 2000 Desktop Engine (MSDE 2000) for the back end?

Now for the background. I have a prototype MDB file that was built in
Access 2K2, and compiled in Access 2K to provide backward
compatibility to Access 2K. I have Office Professional 2K, & 2K2. I
noticed MSDE 2K is provided on Office Pro 2K2, so I thought that I'd
try the conversion.

The prototype MDB file uses DAO, recordsets, & custom functions
extensively. The purpose of the MDB file is to generate either a YTD
or MTD report on up to 4 buckets/metrics of the data. For each
bucket/metric you can either select 'All' or one of the specific
details. You choose the period and the 4 buckets on a form for the
report, then run the report from the form. The form is critical to the
prototype function in 2 basic ways. First, the form provides the
criteria for the underlying report's query. Second, based upon the
buckets/metrics chosen determines how the report's underlying query is
defined. The query is redefined each time due to the use of
percentages that are generated with the use of Dsum as the denominator
for the percentage calculation which is always looking at the same
named query. With the math and 4 buckets/metrics there are 16
different queries that can be run from the form depending upon the
user's choice of the buckets/metrics. Maybe there is another way, but
it works.

Since I haven't used a separate back end database before in Access, I
thought this would be a good learning experience. My concerns are that
some of the functionality used in the MDB file will not be possible
due to the use of a separate back end database. Do the queries for the
report have to be defined on the back end database, or what are my
options? Since the MDB file categorizes ranges of data will I be able
to use DAO and recordsets to update data in the tables. Can I use
custom functions in queries? I suspect I may have to do things
differently, and would appreciate your direction/help.

TIA!

--
Regards,

Greg Strong

[Tom van Stiphout]

On Mon, 31 Oct 2005 17:51:40 GMT, Greg Strong <NoJunk@NoJunk4 U².com>
wrote:

I see a few red flags. The most important one "I notived MSDE ... so I
thought that I'd try...". That is NOT a good reason to do it. Try in
your spare time, and give your client/employer the benefit of your
expertise. Especially since the conversion is non-trivial. And from
your questions I infer you know very little about this new
environment. DAO is available, but NOT recommended. ADP is very much
ADO-centric. Custom VBA functions are NOT available.

It may be better to study ADP and SQL Server a bit more before
embarking on this project. The often-recommended "Access Developer
Handbook" has a Part 2 with a section on ADP. A good place to start.

-Tom.

Hello All,

Hello All,

What are the ToDo's / Gotchas to convert an MDB Access 2K2 database to
an Access Project (i.e. ADP) file for the front end using Microsoft
SQL Server 2000 Desktop Engine (MSDE 2000) for the back end?

Now for the background. I have a prototype MDB file that was built in
Access 2K2, and compiled in Access 2K to provide backward
compatibilit y to Access 2K. I have Office Professional 2K, & 2K2. I
noticed MSDE 2K is provided on Office Pro 2K2, so I thought that I'd
try the conversion.

The prototype MDB file uses DAO, recordsets, & custom functions
extensively. The purpose of the MDB file is to generate either a YTD
or MTD report on up to 4 buckets/metrics of the data. For each
bucket/metric you can either select 'All' or one of the specific
details. You choose the period and the 4 buckets on a form for the
report, then run the report from the form. The form is critical to the
prototype function in 2 basic ways. First, the form provides the
criteria for the underlying report's query. Second, based upon the
buckets/metrics chosen determines how the report's underlying query is
defined. The query is redefined each time due to the use of
percentages that are generated with the use of Dsum as the denominator
for the percentage calculation which is always looking at the same
named query. With the math and 4 buckets/metrics there are 16
different queries that can be run from the form depending upon the
user's choice of the buckets/metrics. Maybe there is another way, but
it works.

Since I haven't used a separate back end database before in Access, I
thought this would be a good learning experience. My concerns are that
some of the functionality used in the MDB file will not be possible
due to the use of a separate back end database. Do the queries for the
report have to be defined on the back end database, or what are my
options? Since the MDB file categorizes ranges of data will I be able
to use DAO and recordsets to update data in the tables. Can I use
custom functions in queries? I suspect I may have to do things
differently, and would appreciate your direction/help.

TIA!

[lylefair]

TO DO:
1. Study MS-SQL Server for 6 months;
2. Then Study ADO for 4 months;
3. Then Study ADPs for 2 months;
4. Then start your conversion; don't worry about finding the gotchas;
they will find you.

[Larry Linson]

"lylefair" <ly***********@ aim.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

TO DO:
1. Study MS-SQL Server for 6 months;
2. Then Study ADO for 4 months;
3. Then Study ADPs for 2 months;
4. Then start your conversion; don't worry about finding the gotchas;
they will find you.

My advice: re-read what Lyle wrote here. Then see if you can Google his
comments about ADPs requiring that the endusers have access to the SQL
Server _Tables_, a situation that sometimes sends DBAs into apoplectic fits.

My (admittedly limited) experience with ADPs indicated no particular
advantage over MDB's using SQL Server via ODBC. The ODBC option does have an
advantage in that you can use it with ANY server database that is
ODBC-compliant. ADPs are limited to SQL Server and its variants -- and, if I
am not mistaken, the traditional MDB, as well.

Larry Linson
Microsoft Access MVP

[Greg Strong]

On Mon, 31 Oct 2005 20:51:55 -0700, Tom van Stiphout
<no************ *@cox.net> wrote:

I see a few red flags. The most important one "I notived MSDE ... so I
thought that I'd try...". That is NOT a good reason to do it.
Well the real reason was to learn. Should have said this. It never hurts
to ask because that is one way that I learn.
Try in
your spare time, and give your client/employer the benefit of your
expertise. Especially since the conversion is non-trivial.
Yes non-trivial is the nice way to put it.
And from
your questions I infer you know very little about this new
environment. DAO is available, but NOT recommended. ADP is very much
ADO-centric. Custom VBA functions are NOT available.
Maybe I am not correct, but I've learned that DAO is only available via
linking with ODBC and not Access ADP. Like I said I'm learning, so
please correct me if I'm wrong.

It may be better to study ADP and SQL Server a bit more before
embarking on this project. The often-recommended "Access Developer
Handbook" has a Part 2 with a section on ADP. A good place to start.

Always a good source for Access. Thanks for the feedback.

--
Regards,

Greg Strong

[Greg Strong]

On 31 Oct 2005 20:31:08 -0800, "lylefair" <ly***********@ aim.com> wrote:

1. Study MS-SQL Server for 6 months;
2. Then Study ADO for 4 months;
3. Then Study ADPs for 2 months;
One whole year?
4. Then start your conversion; don't worry about finding the gotchas;
they will find you.

From your reply on gotchas I can see you are speaking from experience
which I can relate to a degree. Obviously after looking into it, I
didn't really have the proper perspective of the issues. What can I say
other than asking questions is one way to learn.

--
Regards,

Greg Strong

[Greg Strong]

On Tue, 01 Nov 2005 05:12:46 GMT, "Larry Linson" <bo*****@localh ost.not>
wrote:

My advice: re-read what Lyle wrote here. Then see if you can Google his
comments about ADPs requiring that the endusers have access to the SQL
Server _Tables_, a situation that sometimes sends DBAs into apoplectic fits.
Well to sum up my inquiry I look at Access as a tool. A tool that can be
used to obtain more information. No different than a hammer in a
carpenter's hand.

My (admittedly limited) experience with ADPs indicated no particular
advantage over MDB's using SQL Server via ODBC. The ODBC option does have an
advantage in that you can use it with ANY server database that is
ODBC-compliant. ADPs are limited to SQL Server and its variants -- and, if I
am not mistaken, the traditional MDB, as well.

Well I must admit the ODBC option looks desirable because of your stated
advantage "in that you can use it with ANY server database that is
ODBC-compliant." Thanks for the info.

--
Regards,

Greg Strong

[David W. Fenton]

"lylefair" <ly***********@ aim.com> wrote in
news:11******** **************@ g14g2000cwa.goo glegroups.com:

TO DO:
1. Study MS-SQL Server for 6 months;
2. Then Study ADO for 4 months;
3. Then Study ADPs for 2 months;
4. Then start your conversion; don't worry about finding the
gotchas; they will find you.

I often find Lyle obtuse, but even *I* can tell when he's being
sarcastic and actually means something about 180 degrees the
opposite of the surface meaning of the recommendations above.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

David W. Fenton wrote:

"lylefair" <ly***********@ aim.com> wrote in
news:11******** **************@ g14g2000cwa.goo glegroups.com:

TO DO:
1. Study MS-SQL Server for 6 months;
2. Then Study ADO for 4 months;
3. Then Study ADPs for 2 months;
4. Then start your conversion; don't worry about finding the
gotchas; they will find you.

I often find Lyle obtuse, but even *I* can tell when he's being
sarcastic and actually means something about 180 degrees the
opposite of the surface meaning of the recommendations above.

I mean every word.

[Greg Strong]

On Tue, 01 Nov 2005 19:02:09 -0500, Lyle Fairfield
<ly***********@ aim.com> wrote:

I often find Lyle obtuse, but even *I* can tell when he's being
sarcastic and actually means something about 180 degrees the
opposite of the surface meaning of the recommendations above.

I mean every word.

I don't know if I should have my boots on or not. :)

--
Regards,

Greg Strong