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@NoJunk4U².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
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!

[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.googlegr oups.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*****@localhost.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.googlegr oups.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.googlegr oups.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

[lylefair]

I'm quite serious about a year. From the middle of 1999 to the middle
of 2000 this learning was a priority for me. I tried to become familiar
and competent with:
ASP
HTML
DHTML
Document Object Model
JScript / Java Script
VB Script
ADO
MSSQL
ActiveX Client-Side Controls and MTS
Windows 2000
I bought and read books such as Wrox's Professional SQL Server 2000
Programming (Robert Vierra), Wrox's Beginning Active Service Pages 2.0
(Franicis and Others), Wiley's Building Distributed Applications with
ADO (martiner, Herion, Falino), OReilly's Javascript The Defintive
Guide (Flamagan) and several more. (Undoubtedly there is a list of much
better books for 2005). I studied Help Files such a Books on Line, and
the ADO chm files, downloaded and explored Software Development Kits,
and I experimented,experimented experimented.

Was this all worthwhile? For my own personal satisfaction, yes. From a
financial point of view, probably. ....

From answering questions here? No! There are posters here who have done

wery little reading or working with these technologies yet who post
their expert opinion about them frequently. One of them has contributed
to this thread. I think it's a common problem with newsgroups that the
person who seeks information is, almost by definition, unable to judge
the information he/she receives. This may encourage posters to post
answers to questions about which they know ZIP! This often appeals to
the original poster who thinks, "Oh, this poster (who knows little)
REALLY understands me and my problem". Actually, he mistakes a common
level of bewilderment for understanding.
When a beginnger posts a question here about some technology with which
he/she is unfamiliar, my estimate is that he/she chooses the worst of
several answers to respond to with, "Thanks,works like a charm" about
50 % of the time.
Many are not so clever as you ... to say ... Well, now I don't know
..... etc.

[Greg Strong]

On 2 Nov 2005 04:25:44 -0800, "lylefair" <ly***********@aim.com> wrote:

Many are not so clever as you ... to say ... Well, now I don't know
.... etc.

My intent was not to offend, but to state in somewhat humorous way which
of the 2 quite diverse replies to believe. If I did offend you, then I
do apologize.

--
Regards,

Greg Strong

[lylefair]

I was not offended. I was attempting to explain that my original reply
to your post was not intended to be sarcastic, and that my estimate of
a year was simply a rough measure of how long I had taken to learn
these technologies.

[Greg Strong]

On 3 Nov 2005 03:42:17 -0800, "lylefair" <ly***********@aim.com> wrote:

I was not offended.
Good!
I was attempting to explain that my original reply
to your post was not intended to be sarcastic, and that my estimate of
a year was simply a rough measure of how long I had taken to learn
these technologies.

I do appreciate your response. Thanks again!

--
Regards,

Greg Strong