locked
SSMA (Access - SQL 2012) newbie question about new tables RRS feed

  • Question

  • I have successfully run SSMA and moved the back end tables of an Access project to SQL Express 2012.

    Some questions:

    - if I add a new table to the SQL backend, how do I get it to appear in the Access project?

    - if I want to move the whole thing to another computer, how do I go about it?

    - do I always have to have the SSMA app installed?

    - I can't see an ODBC datasource on the machine

    Thanks for some help


    CarolChi

    Friday, February 28, 2014 6:37 PM

Answers

  • I know about the 2GM limit, that is why I started on this. However migrating a new table every time one is created is really not an option.

    Since I only have one large and static table (1.85GB) I am going to put that table in a separate Access database and link it to another Access database with all the small tables which are still being developed.

    This way I don't have to keep on doing migrations.

    Many thanks


    CarolChi

    Monday, March 3, 2014 4:38 PM

All replies

    1. You need to manually create the table in access
    2. You can move the SQL database by using Backup/Restore or Attach/detach
    3. No, SSMA is required for the migration from access to SQL Server
    4. No ODBC connection is required

    Refer the link

    http://www.youtube.com/watch?v=q2eBNHTj86M

    http://blogs.msdn.com/b/ssma/archive/2011/01/28/access-to-sql-server-migration-how-to-use-ssma.aspx

    -Prashanth

    Friday, February 28, 2014 7:34 PM
  • Thanks, I was not clear in my questions:

    1. Given that from now on the tables should all be in SQL, I think it would be better to create new tables in SQL them there rather than create them in Access and have to keep moving them to SQL with SSMA. So if I create a new table in SQL how can I see it in Access? Or can I easily create a linked table in Access?

    The table created by SSMA have this property:

    SSMA ODBC string

    If I create a new table in SQL and link it I get a more classic ODBC connection:

    manually linked ODBC

    2. I am happy with SQL backup/restore, attach/detach. But how does Access know where the SQL Database has gone?

    3. I thought the migration was a one time task, but from what you say it is an ongoing "linking" app. So I would be better to export my tables and re-link them to get something that can be moved to another network or computer?

    4. The SSMA looks very much like an ODBC connection.

    This is a new project so I will have to make lots of new tables and also move the whole thing around to different computers, on different networks.


    CarolChi

    Friday, February 28, 2014 7:50 PM
  • You need to identify a strategy to perform such tasks.

    Why do you want to re-link the table from SQL to access? Once you done with the migration, you need to do any development on SQL Server.

    You can take a backup of the database and restore it across any number of different computers or n/w.

    Migration is one time task, Once you done with the migration no need to use SSMA tool.

    Internally it uses ODBC drivers to connect to the databases.

    -Prashanth

    Friday, February 28, 2014 8:40 PM
  • The project is not finished. There are still new tables to make. It got too big for Access, because of one very large table.

    It is not a question of re-linking but how to handle a new table.

    I want to keep the front end in Access. It is much easier for users.


    CarolChi

    Saturday, March 1, 2014 7:33 AM
  • Access databases are limited to 2GB. I don't think you will be able to handle large data in Access.

    In this case every time you create a new table you've to create a new project and migrate the data for the newly created table.

    -Prashanth

    Monday, March 3, 2014 4:04 PM
  • I know about the 2GM limit, that is why I started on this. However migrating a new table every time one is created is really not an option.

    Since I only have one large and static table (1.85GB) I am going to put that table in a separate Access database and link it to another Access database with all the small tables which are still being developed.

    This way I don't have to keep on doing migrations.

    Many thanks


    CarolChi

    Monday, March 3, 2014 4:38 PM