Upsizing A2K to SqlServer, is Access Project necessary?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Jim Devenish

    #1

    Upsizing A2K to SqlServer, is Access Project necessary?

    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

    #2
    Re: Upsizing A2K to SqlServer, is Access Project necessary?

    On 4 Jul 2005 09:53:14 -0700, "Jim Devenish" <internet.shopp ing@foobox.com>
    wrote:
    [color=blue]
    >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?[/color]

    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.
    [color=blue]
    >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.[/color]

    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.
    [color=blue]
    >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.[/color]

    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.
    [color=blue]
    >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.[/color]

    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.
    [color=blue]
    >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)[/color]

    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.

    Comment

    Working...