Upsizing A2K to SqlServer, is Access Project necessary?

[Jim Devenish]

I am continuing my exploration about upsizing to SQLServer from Access
2000.

I have a split database with a front-end and a back-end, each of which
is A2K. I have spent some time in bookshops trying to learn about the
way forward and trying to decide which to buy. The bit I am interested
in is usually at page 900 and I am not sure whether I want to buy the
first 800.

It appears that upsizing creates an Access project as the front-end and
SQLServer as the back-end. Is it necessary for the front-end to be a
project instead of an .mdb (or .mde) file? What are the advantages and
the differences?

My front-end creates the links to the back-end by means of VBA code.
This means that if the back-end file is moved or I transfer a new
version of the front-end from my laptop to the client's system,
relinking is painless.

The actual tables are not all in a single .mdb file. The front-end
links most tables to one file but links a small number to a different
one. I'll not go into the reason why here.

If I use a project front-end can I link some tables to SQLServer and
some to an .mdb file? No there is another good reason why the second
lot cannot go into the SQLServer! I am trying to find out what it
possible.

The books usually seem to discuss upsizing an unsplit database. Do I
need to bring the tables back to the front-end before upsizing? or do
I simply upsize the back-end? If so, how does the front-end become a
project (that is, if it should)

Any guidance would be welcome.

[Steve Jorgensen]

On 4 Jul 2005 09:53:14 -0700, "Jim Devenish" <in***************@foobox.com>
wrote:

I am continuing my exploration about upsizing to SQLServer from Access
2000.

I have a split database with a front-end and a back-end, each of which
is A2K. I have spent some time in bookshops trying to learn about the
way forward and trying to decide which to buy. The bit I am interested
in is usually at page 900 and I am not sure whether I want to buy the
first 800.

It appears that upsizing creates an Access project as the front-end and
SQLServer as the back-end. Is it necessary for the front-end to be a
project instead of an .mdb (or .mde) file? What are the advantages and
the differences?
ADPs were a pretty good idea that went terribly wrong somewhere. Since, 2
Access versions later, the major problems with ADPs have not been addressed,
and Microsoft is no longer actively encouraging people to develop ADPs, we can
assume that they're just letting ADPs quitely die.

Having done one project as an ADP from scratch, I don't recommend it. I
recommend converting an already working MDB to an ADP even less.
My front-end creates the links to the back-end by means of VBA code.
This means that if the back-end file is moved or I transfer a new
version of the front-end from my laptop to the client's system,
relinking is painless.
You'll have to make minor changes to your relinking code to use the connection
string to the server rather than to the back-end MDB.
The actual tables are not all in a single .mdb file. The front-end
links most tables to one file but links a small number to a different
one. I'll not go into the reason why here.
You can continue to use this strategy if you want, moving some tables to the
server, and keeping some tables in MDBs. If you do join queries between them,
however, all records Access cannot filter before joining will have to be read
through the local system to perform the join. That can be fine in some cases,
but you need to think about each case.
If I use a project front-end can I link some tables to SQLServer and
some to an .mdb file? No there is another good reason why the second
lot cannot go into the SQLServer! I am trying to find out what it
possible.
No, but you can link to the .mdb file from the server, and have the
application use them as if they were server tables. I can't imagine anyone
actually choosing to do that, though, unless the .mdb back-end is also shared
by other front-end apps outside of your control.
The books usually seem to discuss upsizing an unsplit database. Do I
need to bring the tables back to the front-end before upsizing? or do
I simply upsize the back-end? If so, how does the front-end become a
project (that is, if it should)

I'm not sure how the upsizer works in that case because I never use it. I
always hand-write the scripts to create the server-side tables, then link to
the tables. This gives you a chance to decise what tables should have
TIMESTAMP columns (hint - most do), whether to make the primary key clustered,
what SQL Server data type to use for each number and date, etc.