Upgrading from .mdb file format to .adp file format

[Jeannette]

Hi!

I am currently working on a systems enhancement plan in
order to make an improvement on our current system. We
are currently using MS Access 97 as desktop database to
create an .mdb file with linked tables that use an ODBC
driver to link to files in AS/400. The amount of data
that is stored in our current Access databases is getting
bigger every day. As the result, users need to spend more
time than before to process data.

I plan to use Access Projects as front-end application and
get SQL Server 2000 as back-end to solve our current
problem. In this case, all the existing applications
developed in Access 97 need to be converted to Access
2000. My questions are:

1) Can an .mdb file in Access 97 being converted to
an .adp file (Access Projects) in Access 2000? If so, how?
2) Do I migrate only tables and queries in our
current applications to SQL Server 2000 and convert only
modules, macros and reports to Access 2000? If so, any
hints on doing this?

I'd greatly appreciate it if you could provide me any
suggestions that would help me in upgrading from Access 97
to Access 2000 in .adp file format.

Thanks,
Jeannette

 

[Albert D. Kallal]

Hi!

I am currently working on a systems enhancement plan in
order to make an improvement on our current system. We
are currently using MS Access 97 as desktop database to
create an .mdb file with linked tables that use an ODBC
driver to link to files in AS/400. The amount of data
that is stored in our current Access databases is getting
bigger every day. As the result, users need to spend more
time than before to process data.

Any reson why ou don't just run off of the data direclity in the as/400 by
odbc?

Why move the data to ms-access? If you can setup ad build some nice views on
the as/400 side, then you can get decient (in fact better) perfoamcne then
moving all that data to ms-acesss. So, I am saying to keep using odbc...but
report on live data from the as/400 system...

I plan to use Access Projects as front-end application and
get SQL Server 2000 as back-end to solve our current
problem.

Gee, now you are dealing with 3 complex sysstems: sql server, as/400 and
ms-access. This is going to increase the complxing you have to deal with by
quite a large amount. If you are telling me that the as/400 system is being
retirned and dumped..then I am 100% with you on this. However, you seem to
be setting up another database server. Again it is not 100% clear why you
can't report on, and use the data direclity rom the as/400. Why does the
data need to be sent to ms-access (and now you are propsing to send the data
to sql server from the as/400?. You need to really be carefull here..as you
are about to increase the complxinty of this system by a laqrge amount..and
the benefits are not clear as to what or why you need to do this?

In this case, all the existing applications
developed in Access 97 need to be converted to Access
2000. My questions are:

1) Can an .mdb file in Access 97 being converted to
an .adp file (Access Projects) in Access 2000? If so, how?

You can most certanly do the above conversions. However, for EXISTING
applctons, it is generally reommcned to use linked tables to sql server via
odbc. If your appltion is well written, then there is goin gto be little, or
no differnce in perfoamnce here. At any rate, the converson process is NOT
automaclity, and code (espeically dao recordset code) will not work in a adp
proejct. If you have a good deal of dao code...then as menteond, the
perfecned migration path to sql sewrver is to contnue to use odbc. (however,
it strange...since you are now using odbc to the as/400...and that does NOT
seem to be wroking that welll...and it should...

2) Do I migrate only tables and queries in our
current applications to SQL Server 2000 and convert only
modules, macros and reports to Access 2000? If so, any
hints on doing this?

Well, since I am recommending to use odbc to sql server, then you don't have
to upgrade to access 2000 ...do you? However, the anser is yes, when you
migrate to sql server, you move the tables, and *most* of the querys will be
moved to the server. As mentioend, if ALSO WANT to use a adp project...then
any dao recordset code must be changed to ado (it is for this reason that it
is useally better to simply used linked tables via odbc..and NOT use a ADP
projejct.) Of ccouse, when staring from scraqtch...and you want to use sql
server, then in place of odbc, then a adp project is worthing of consdfing.
However, for user coutns low 100's number of users...access via odbc to sql
server is just fine. Not knowing how complex your applictiaon is.....it is
not cut and dry as to how much work it will be to convert your applction to
a adp project (as metoned, odbc is less work..and you don't even have to
upgrade to a2000. And, to be fair...If I was going the adp way..I would skip
a2000, and go to a2002.

 

[Albert D. Kallal]

opps...bumped the send key...

Lets try again:

Hi!

I am currently working on a systems enhancement plan in
order to make an improvement on our current system. We
are currently using MS Access 97 as desktop database to
create an .mdb file with linked tables that use an ODBC
driver to link to files in AS/400. The amount of data
that is stored in our current Access databases is getting
bigger every day. As the result, users need to spend more
time than before to process data.

Any reason why you don't just run off of the data directly in the as/400 by
odbc?

Why move the data to ms-access? If you can setup ad build some nice views on
the as/400 side, then you can get decent (in fact better) performance then
moving all that data to ms-access. So, I am saying to keep using odbc...but
report on live data from the as/400 system...

I plan to use Access Projects as front-end application and
get SQL Server 2000 as back-end to solve our current
problem.

Gee, now you are dealing with 3 complex systems: sql server, as/400 and
ms-access. This is going to increase the complexity you have to deal with by
quite a large amount. If you are telling me that the as/400 system is being
returned and dumped..then I am 100% with you on this. However, you seem to
be setting up another database server. Again it is not 100% clear why you
can't report on, and use the data directly on the as/400. Why does the
data need to be sent to ms-access (and now you are proposing to send the
data
to sql server from the as/400?. You need to really be careful here..as you
are about to increase the complexity of this system by a large amount..and
the benefits are not clear as to what or why you need to do this?

In this case, all the existing applications
developed in Access 97 need to be converted to Access
2000. My questions are:

1) Can an .mdb file in Access 97 being converted to
an .adp file (Access Projects) in Access 2000? If so, how?

You can most certainly do the above conversions. However, for EXISTING
applications, it is generally recommended to use linked tables to sql server
via
odbc. If your application is well written, then there is going to be little,
or
no difference in performance here. At any rate, the conversion process is
NOT
automatic, and code (especially dao recordset code) will not work in a adp
projects. If you have a good deal of dao code...then as mentioned, the
preferred migration path to sql server is to continue to use odbc. (however,
it strange...since you are now using odbc to the as/400...and that does NOT
seem to be working that well...and it should...

2) Do I migrate only tables and queries in our
current applications to SQL Server 2000 and convert only
modules, macros and reports to Access 2000? If so, any
hints on doing this?

Well, since I am recommending to use odbc to sql server, then you don't have
to upgrade to access 2000 ...do you? However, the answer is yes, when you
migrate to sql server, you move the tables, and *most* of the queries will
be
moved to the server. As mentioned, if ALSO WANT to use a adp project...then
any dao recordset code must be changed to ado (it is for this reason that it
is usually better to simply used linked tables via odbc..and NOT use a ADP
project.) Of course, when staring from scratch...and you want to use sql
server, then in place of odbc, then a adp project is worthy of
consideration.
However, for user counts low 100's number of users...access via odbc to sql
server is just fine. Not knowing how complex your application is.....it is
not cut and dry as to how much work it will be to convert your application
to
a adp project (as mentioned, odbc is less work..and you don't even have to
upgrade to a2000. And, to be fair...If I was going the adp way..I would skip
a2000, and go to a2002.

 

[Jeannette]

Hi, Albert-

Thank you very much for taking your time to respond to my
posting.

In fact, we have been using VBA routines in Access 97 to
process data in linked table that is link to AS/400. Most
of our client applications developed in Access 97 would
run a query based on linked tables in Access database and
then append the query to a new table in the same Access
database or different Access database. The problem now is
that the amount of records in the table is getting bigger,
which exceed the Access database size limit. Therefore,
we need to create another Access database to continue
holding the records. Basically, we will have many
databases with the same client applications and table
structure, but different records in tables. Because of
that, we need to spend large amount of time to run the
same application once for each database and combine all
the output from those databases.

We are now looking for a best solution to solve the
current problem. I plan to upgrade to Access 2000 and get
SQL Server 2000 so that large amount of data can be stored
in a SQL Server database. The only solution that I can
think of now is to use Access Projects as front-end and
SQL Server 2000 as back-end. That's why I need to upgrade
to Access 2000 in order to be able to use the Upsizing
Wizard.

The reason why I'm not planning to use ODBC to link to SQL
Sever is because we will not be able to use Access
database (.mdb file format) to create a new table in SQL
Server database (as mentioned earlier, the VBA routines
will append query to a new table). Therefore, in order to
be able to create new tables in SQL Server database by
using VBA routines in Access, continue using Access for
client application, as well as obtain a large data
storage; it appears that using Access Projects with SQL
Server 2000 is the optimal solution. I understand that I
need to deal with 3 complex systems: AS/400, SQL Sever
2000 and Access 2000 by pursuing this solution. By doing
this, I have to create a linked server to the AS/400
within SQL Server and create a view in SQL Server database
which allows users to look at all the data in specific
file in AS/400. We will use the VBA routines in Access
Projects to copy out specific records from the view into a
table within the SQL Server database. Do you think this
will work?

This is the first time that I need to perform systems
analysis task. Therefore, I'd be grateful, if you could
provide me some ideas to which our current problems can be
solved. Again, I truly appreciate your response.

Thanks,
Jeannette