Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2004
    Posts
    20

    Unanswered: Questions About: ACCESS -> SQL Server

    Im consulting a financial company on their Access database. It has a back-end with about 100 tables (some close to 100,00 records) and a front-end with all the Forms, Queries, Modules, and Reports. The last few weeks they have had to compact and repair the front end twice a week where before it was a bi-weekly thing.

    Im trying to sell them the service of moving everything I can to SQL Server and link it back to the front-end Access GUI, but Im not sure of everything that goes into this process and how long it will take - of course my client needs the process on paper with an estimate timeline/cost.

    My questions are:
    1) What does Compact and Repair actually do?
    2) Can Access Queries be moved to SQL Server Views and be linked?
    3) What can I do about Access Modules and Macros? Can they be moved to SQL Server somehow easily?
    4) What other concerns should I have?

    Also if any of you fine people have done this sort of thing in the past please share your stories

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Compact and Repair option for MDB files is basically "first aid" for a corrupted data file. It sifts through a clobbered file, repairs what it can, jettisons what it can't repair, then compacts the remaining data to reclaim any lost space.

    At least 99% of the damage I see to MDB files is from someone undexpectedly disconnecting from the file, usually by either turning off a PC unexpectedly or tripping over a network cable. These things rarely happen with servers (or at least they better happen VERY rarely), so corrupted databases files are extremely uncommon. The more robust file architecture of SQL Server (data and log on two separate files, with logging first) means that even if file damage does occur, it is comparatively easy for the server to fix.

    There are multiple strategies for upsizing from MS-Access to MS-SQL. I think I remember a good chapter on the topic in the SQL Resource Kit.

    -PatP

  3. #3
    Join Date
    Oct 2004
    Location
    Durban - South Africa
    Posts
    13
    Macros and Modules must be converted to Stored Procedures and User Define Functions - How familiar are you with these?
    TrevorW
    If at first you don't succeed, call it version 1.0

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Compact and repair is primarily for the data, not the forms.
    Does this application have separate mdb files for the data and the forms?
    Is the forms file located on the user's desktops, or is it loaded from a shared network location?
    To upscale, your best bet is probably to convert the Access application to and Access Data Project (.adp file) with a SQL Server backend. That way, the data is stored securely in SQL server but you can retain all your forms and vb code with little modification.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41
    I also recommend creating a .adp database.

    You can "move" the queries to SQL. The easiest way is to creat pass-through queries. However, with a .adp you'll create stored procs. I'm guessing that the vast majority of your code (macros and modules) can remain on the front-end. I've rarely used VBA code to do data manipulation.

    I think your major concern is going to involve the complexity and documentation (or lack of) of the current .mdb. Hopefully, the database is clean (minimal user tables and ad hoc queries) and the code is straight-forward.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Stories?

    Do I have stories?

    Well they're more like horror stories....

    I have yet to do a straight conversion where it all just "works"

    Every "consultant" built Access "app" I was chosen to convert has always turned in to a re-write.

    Yo Blind dude...where you bean?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Been up to my butt in oraclegators.

    ADP is the way to go with this, but I also have to agree with Brett that it won't be a simple conversion. It sounds like it's a mess already. Theoretically you can upsize from Access to SQL Server, but remember:
    "In theory, theory and practice are the same. But in practice, theory and practice are different."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2004
    Posts
    20
    Guys,

    Thanks for all yor input, I have a better vision of what has to be done and a timeframe. Sounds like ADP is the way to go - Ive never made an adp front end, but Im always up for a challenge!

    Thanks for the input

  9. #9
    Join Date
    Sep 2004
    Posts
    20
    Ok now i have more questions after testing out an ADP with a SQL Server DB i have from another project:

    I want a basic outline of what data goes where to explain to my client.

    I see that SQL Views ported to Queries in Access when i made the adp - is that "ok"?

    Should I DTS the Access mdb to SQL Server then make a new ADP based on it?

    Im looking for fast as possible conversion, what code in the Modules will change? Will Macros change at all?

    I still have so may questions Ill be bugging y'all for ever on this.

    Can you guys supply me with a quick, basic Step-By-Step, based on each Access object, on what has to be done to change from a Access front and back end to SQL Server and an ADP? If you want make 2 outlines: A "quick" conversion that will work, and a "best" conversion that will work the best.

    Thank you Thank you Thank you

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's a basic outline of what data goes where in an Access Data Project:

    Data => SQL Server

    That's it.

    As far as upscaling, I think there is a decent wizard that will get you started, but every database is different so don't expect the process to be clean and complete. You'll need to do some tweaking for sure.

    You should convert your queries to Views or Stored Procedures. Push as much of the processing onto the Server as possible, and just use Access and Access Modules for forms and presentation layer calculations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Sep 2004
    Posts
    20
    BlindMan:

    Will the code in the Modules and Macros need to be altered or should they work fine as long as the tables are named the same?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The code should continue to work unless is references objects specific to Microsoft Access (Access table objects, for instance...).

    Like I said, it is almost guaranteed that there will be kinks in the upsizing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Sep 2004
    Posts
    20
    Back to my DTS question - Should I DTS the Access tables and queries to SQL Server to start the upsizing?

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not sure about that. Always more than one way to skin a cat. If you are comfortable with DTS, then use DTS.

    If the Access=>SQLServer upsizing wizard will do this for you, then I would let it handle it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •