Results 1 to 4 of 4
  1. #1
    Jeff Lipton is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2022
    Posts
    2

    SSMA is driving me NUTS!


    Versions:
    Access 2013
    SSMA for Access 9.1
    SSMS 18.10

    SSMA never works the same way twice -- it will fail the Migrate, or fail the Link, or create the linked table as Read-Only. And that's after I clear "SSMATableState" on the table(s) each time before I start the migration. I suspect it might be because I'm using VPN -- I hope to be able to try an Ethernet connection tomorrow.

    Should I be saving the changes to my projects? I only use each project once.

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Using a wizard for migration is not a very good option. Why not doing it manually? It's better to oversee each step, creating the database with the correct recovery model and back-up strategy, choosing the correct data types for your fields, ect. . You will have a better design, a good understanding of your SQL database, and in the end it will probably be faster.

  3. #3
    Jeff Lipton is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2022
    Posts
    2
    My tables have indexes which link them to the primary key of other tables. If I migrate manually and want to keep the AutoNumber feature, it won't match up with the existing index. I have over 100 tables to migrate so having a wizard that works will save me a lot of time and hardship.

    Morreover, I don't want to manually check and update up to 100 data fields for each table. If the wizard gets any field wrong, I can go and fix that field.

    I wish there was a better product to migrate but it doesn't seem like there is.
    Last edited by Jeff Lipton; 10-05-2022 at 12:30 PM. Reason: Add response

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    for your autonumber: check the identity specifications of the column, or create a sequence: this is the most flexible.
    For the upload of the tables: create one or more SSIS packages. I would create one SSIS package/schema. I would create a different schema/ module (customers, sales, ect.) . That will help you to set up the security on your SQL server.
    You can also create scripts for moving the table, long ago I came across a tool that could create SQL scripts from Access tables, but wouldn't know if that still exists. That way you can check the script and change it before you run it on your server.
    Remember: moving the tables is just one step of your upgrade. Don't forget to setup a correct back-up strategy, security settings (logins on server level and users on database level). Indices need to be reviewed, they differ from Access indices and are very important there, use included columns in your indexes where needed. Don't mix up indices (used when searching data) with constraints (primary and foreign keys, default settings, ...)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append to SQL Driving Me Nuts
    By NewOldFella in forum Queries
    Replies: 6
    Last Post: 07-25-2018, 02:11 AM
  2. this is driving me nuts
    By Raleyoz in forum Access
    Replies: 20
    Last Post: 06-02-2015, 08:10 AM
  3. switchboard driving me nuts
    By bbxrider in forum Forms
    Replies: 4
    Last Post: 12-06-2014, 06:24 PM
  4. Can't create relationships (Driving me nuts!!)
    By zlloyd1 in forum Database Design
    Replies: 2
    Last Post: 08-12-2012, 08:49 PM
  5. SELECT Query, Driving Me Nuts!!
    By mystifier in forum Queries
    Replies: 4
    Last Post: 04-21-2011, 04:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums