Upsizing .mdb for split DB

[Joel Wiseheart]

Hello,
I have found reference materials that discuss how to
use the "Upsizing Wizard" to upsize from an .mdb file to
a SQL/MSDE file. Th eproblem is that all the reference
materials that I have found assume that you're upsizing a
single, self-contained .mdb file.
Is there any guides to upsize a from a Jet DB to a
SQL/MSDE DB & .adp file, that has already been split, so
the tables are in one .mdb file and the queries (and
forms, reports, VBA etc.) are in another?

Thanks!

 

[Daran Johnson]

Joel,

The upsize procedure would only apply to the Access database that contains
the data (tables). The forms, reports, VBA etc. would remain in Access as
an ADP. Also, code that references data will have to be re-written. I
would also suggest that you take advantage of Stored Procedures. Books On
Line (BOL) is the best resource. You can find it at Microsoft's website.
There are a number of good books on the topic. I would suggest that you
begin to see this as an Access (ADP) front-end and a SQL Server back-end. I
think that will make the transition easier because some of the issues that I
have read in this newsgroup have more to do with continuing to see the new
system the same way as the old - as one product (i.e. MSDE is a part of
Access like JET is a part of Access).

Remember that you are moving to a true client/server system. The queries
and tables will now be on the server and that is where the work will be done
and the results will be sent to the client where that forms, reports,
macros, and VBA will be. I like to think of the ADP development environment
as a window to the tables, views, stored procedures, and functions residing
on the server. The forms, reports, macros, and VBA are local and in the
file you are working on. When you cannot connect to the server you will
still see the forms, reports, macros, and VBA but not the tables, views,
stored procedures, and functions.

I hope that helps.

-Daran

 

[Joel Wiseheart]

That's great info, but my question is that if you have
two split .mdb files, in order to make a SQL Server back
end, you have to take the tables from one .mdb file, and
the queries from another .mdb file.

All of the reference materials I have seen talk about
using the Upsizing Wizard in Access, but assume that your
tables and queries are in the same .mdb file.

I find it hard to believe I am the first person to try to
upsize a split Jet DB. There must be some sort of
resource out there that defines this process.

The only other way I can think of is to "un-split" the
files by importing the tables back into the front
end .mdb file, and then running the Upsizing Wizard. I am
wondering if there is an easier way.

Thanks!

 

[Daran Johnson]

Joel,

There is not an easier way that I am aware of. It is true that you could
attempt to upsize the database by moving the data and the queries into one
MDB file. The result would be that some of the queries would be transformed
into views on the server. The truth is that these are simply two different
types of systems. I would recommend not using the Upsize Wizard at all.
Plan the migration for the data from Access to SQL Server carefully. Map
the data types, identify where stored procedures and views should be
implemented. Review the VBA code and identify where the code will need to
be re-written to access SQL Server and whether the access should be a table,
view or stored procedure.

In Access all the work is done at the client and the server is a file on a
file server. One is not served the data one has requested, rather the whole
recordset is sent to the client for the client to do the work. SQL Server
is designed to only serve the data requested. That is a different model and
requires different architecture. The work is done at the server. How the
work is done cannot be determined by looking at your current Access
architecture. You must decide where the stored procedures go and the logic
they will use to get the data needed. Transact-SQL (the SQL language used
by SQL Server) is far more powerful than the SQL Access uses and it is
different.

I hope that helps - and is not more confusing.

-Daran