Results 1 to 5 of 5
  1. #1
    bonnerm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    7

    Issues with using SSMA (SQL Migration Assistant)

    I've been developing an Application in Access 2010 for a few weeks and I've just used SSMA to migrate the tables only to SQL Server 2008. For the most part things work but I have 2 questions ..

    1. I went to some trouble to draw the Relationship disgram in Access. Now I don't see table connections - is that diagram invalid now? Referential integrity seems to be working - any comments?
    2. Are there any tricks I should be aware of before I migrate? I already changed settings for dates and had to update some code to use the"dbSeeChanges" option when writing to a recordset. I also noticed that the error handling has changed a little too?

    Am I crazy to attempt this or should I do all the table design in SQL to start with (not strong at that) .. Mark

  2. #2
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Are you using SQL Management Studio to view your SQL Tables and database? You need to open the Database Diagrams Node of your database to view the relationships. There won't be any diagrams there - you just need to create a new diagram and add the tables. Right-click on Database Diagrams and select New Database Diagram.

    SQL and Access are a great combination and it sounds like you are already doing the things that need to be done. For future reference you may want to look into using pass through queries and ADODB for certain operations instead of DAO. In particular if you plan to use stored procedures.

    I personally don't like to use any conversion or upsize tools. I create the sql database and tables manually when I convert from Access. I have more control over the datatypes and tables.

  3. #3
    bonnerm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    7
    Sorry for the slow reply. Yes - I'm using Management Studio. I'm not overly confident in SQL but I can muddle through. The most important thing to me is that things like referential integrity are still working. For example, I setup the relationships in Access, checking the boxes for Cascade Updates and Deletes in most cases. So when I deleted a "client" record, all related records are deleted too. I haven't properly tested this with the Migrated SQL data but how would you achieve that from scratch in SQL? It looks like these are "constraints"? Creating the diagram in Access imples certain results - is the SQL diagram the same or more for documentation? I need to do a bit more experimenting but I'd appreciate some guidance. I think you've convinced me to create the tables from scratch - there are almost 50 of them!!! Mark

  4. #4
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    The SQL Diagrams are for documentation and creating the indexes visually. If the relationships are already set up when you create the diagram they should automatically draw. In order to do it from scratch in SSMS you can either use SQL statements : http://www.w3schools.com/sql/sql_primarykey.asp OR just draw them visually in the diagram (similar to Access). Below is a diagram where I have just drawn the relationship between tblJob and tblJobOrderParts. Once you draw the relationship the settings window will open where you can set your cascade options and other options.
    Click image for larger version. 

Name:	SQLDiagrams.jpg 
Views:	4 
Size:	111.3 KB 
ID:	7249

    If you need to go back and modify existing keys you would do this under the table > Keys node and double-click the key you want to modify.

    Click image for larger version. 

Name:	foreignKeymodification.jpg 
Views:	3 
Size:	95.9 KB 
ID:	7251

  5. #5
    EuniceH is offline Novice
    Windows Vista Access 2000
    Join Date
    Oct 2011
    Posts
    11
    SSMA imported my relationships, I ended up not using them, but if I imported using SSMA, then went back to Mgmt Studio, right clicked on the database, then did Tasks - Generate Scripts, I see them as, (example)
    ALTER TABLE [dbo].[tblCounties] WITH NOCHECK ADD CONSTRAINT [tblCounties$tblStatestblCounties] FOREIGN KEY([StateID])
    REFERENCES [dbo].[tblStates] ([StateID])
    GO
    ALTER TABLE [dbo].[tblCounties] CHECK CONSTRAINT [tblCounties$tblStatestblCounties]
    GO

    I ended up using SSMA to create my basic table structure, then went back to SQL Server Mgmt Studio, right clicked on the database, then did Tasks - Generate Scripts, then trial and error edited that .sql. Part of editing was to turn certain Date/Time fields to either [date] or [time](0) and not datetime2 like they were created as. Also my datetimes were all defaulted to Today or Now in Access, and I had to make them default to getdate() rather than the complicated getdate() formula that SSMA came up with. Then I created a new database with the edited .sql, did SQL Server Management Studio, right click on database, Tasks, Import to import the data.
    Last edited by EuniceH; 04-24-2012 at 12:58 PM. Reason: added info on how I used SSMA

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

Similar Threads

  1. Access 2010 to SQL Azure migration
    By Aurelius7 in forum SQL Server
    Replies: 6
    Last Post: 06-18-2012, 10:15 AM
  2. SSMA Linked table reconfiguration
    By Paladindc in forum Import/Export Data
    Replies: 0
    Last Post: 02-07-2011, 12:35 PM
  3. Migration to web
    By thbaig1 in forum Access
    Replies: 1
    Last Post: 11-22-2010, 10:04 AM
  4. launching migration script
    By MrGrinch12 in forum Programming
    Replies: 0
    Last Post: 06-23-2010, 08:28 PM
  5. Data migration open source tool
    By pgdabler in forum Import/Export Data
    Replies: 0
    Last Post: 10-07-2009, 08:46 AM

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