Results 1 to 5 of 5
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151

    Help moving from Access to SQL

    Currently I have a back-end Access database with just my Tables and relationships and a front-end with my queries, forms and reports.



    I would like to leave my front end in Access and just move my back-end Tables to SQL and I have a few questions.

    1. Will the relationships get moved as well as only see an option to move the tables.
    2. Once I move the tables and then link my back end to the SQL the links now appear as dbo.table instead of just the table name with a link arrow. none of my queries work because they all refer to the previous link Table name and not the dbo.Table. I have several dozen Queries. Is there an easy way to fix this?

    Thanks,
    Sam

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There are several solutions including:
    You can alias the table names to remove the dbo.
    Or rename the linked tables in Access
    Or use DSN-less connections in which case the dbo. prefix will not be included
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    So the renamed worked. Regarding my relationship question, it looks I need to make the relationships in my front end after I link the Tables from the new SQL data. Is that correct?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Relationships need to be done in the BE to be effective.
    Although you can create relationships on linked tables in the FE, you cannot enforce referential integrity from the FE so there is no point in doing so.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    relationships are made in SQL server through primary and foreign key retraints on the table. If you have int as field type, you can set the identity property on true and set the increment factor (standard this is set to 1, but you can set this to other values) which gives the same effect as an autonumber in access. You can use diagrams in the SSMS (SQL Server Management Studio) to visualise your tables and relations.

    tip 1: check your recovery model. Set it to simple if you don't take log backups on regular times, otherwise your log file will blowup and you might run out of disk space.

    tip 2: I would not create all tables in the dbo schema, but use different schema's for different groups of objects that need different security

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

Similar Threads

  1. Replies: 3
    Last Post: 09-05-2017, 04:11 PM
  2. Moving Access Database
    By midpitts in forum Access
    Replies: 3
    Last Post: 12-04-2012, 05:32 PM
  3. Replies: 0
    Last Post: 06-19-2012, 12:39 PM
  4. Replies: 6
    Last Post: 02-18-2012, 07:20 AM
  5. Moving from Excel to Access
    By austinres in forum Access
    Replies: 3
    Last Post: 10-13-2011, 08:18 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