Results 1 to 10 of 10
  1. #1
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    23

    Question Migrate Data From MS Access 2013

    Hello,

    I am looking to move my Access 2013 database into SQL Server 2017 Express, and use Access 2013 as my front end.


    Is there any guidance or instructional documentation on the best approach for using SQL Server 2017 Express as my back-end and Microsoft Access 2013 as my front-end?




    In Access 2010, the missing manual book, it states that you can add objects to SQL Server, your tables and queries, while using Access as your front-end to hold the forms and reports -- I'm curious, is it the same for Access 2013? I've noticed there's been changes since the 2010 edition.

    I'd still like to use my 2013 Access database as the front-end, but split the database and have my tables and queries in the SQL Server, what is the best approach to start that procedure?

    Any help would be appreciated. Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    it should be effortless.
    you link in the SQL tables and name them the same as the access tables and it works flawlessly.

    I actually had 3 links for 1 table in order to swap between Production (SQL) and Test (access). The access link table, the SQL link table, and the 'working' table.
    tClients_Prod
    tClients_Test
    tClients

    I had code to swap between production to Test and back.
    For production, the code copies tClients_Prod to tClients, for every table in the db.
    Test mode copies the _Test tables

    (note , it doesnt copy the data, just the table ,aka the link).

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    There is a SQL upsizing wizard available (depending on your version of access), which really does make things effortless.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Doesn't one have to be concerned with Access features that are not compatible, such as multi value fields? It's probably not something the more experienced responders would have created, but a novice might have. I'm only asking because I've never had to migrate/convert like that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Agree with Micron.

    If you have Look up FIELDS, multi-value fields or calculated fields you will have problems.
    If you have spaces, punctuation or special characters in your field names, you will have problems. (Underscore is OK).

    There are field type differences you should be aware of. One is that a Boolean field type (Yes/No) in SQLSE is converted to a Bit type field.
    Every table NEEDS a PK field or a timestamp field will automatically added (IIRC).


    When you link to the SSQLE tables, in Access the table names are prefixed with "dbo_" (database owner). There is code (internet) that will remove the "dbo_" so the controls/forms/queries/code will not have to be changed because of the table names. If you cannot find the code, post back and ask. I will find the code I used and post it.

  6. #6
    EMalu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    1
    I used a non-free tool from DBConvert to move an access database to sql server, and found it well worth the money compared to the amount of time I wasted trying to do it reliably for free.
    As I understand they support all versions of MS Access and SQL servers.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just adding to previous comments, there should be no need to purchase a tool like DBConvert.
    Unfortunately the Access upsizing wizard that Minty mentioned was deprecated in A2013.
    However SQL Server includes an Import and Export Wizard as a separate start menu item which is more powerful and probably does everything that DBConvert does
    Last edited by isladogs; 06-18-2018 at 05:14 AM.
    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

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by ridders52 View Post
    Just adding to previous comments, there should be no need to purchase a tool like DBConvert.
    Unfortunately the Access upsizing wizard that Minty mentioned was deprecated in A2013.
    However SQL Server includes an Import and Export Wizard as a separate start menu item which is more powerful and probably all everything that DBConvert does
    Do you know if this is available in the free versions

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Its supplied with the free SQL Server Management Studio (SSMS) Express 2014 and presumably with later versions as well

    Click image for larger version. 

Name:	Capture.PNG 
Views:	21 
Size:	16.8 KB 
ID:	34465

    With the free version, you can't save the settings for future use. Irritating but not the end of the world
    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

  10. #10
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    @ Micron
    Hi,
    I'm working with SQL server since version 2000 and experience told me it's never a good idea to take the quick route and use an upsizing wizard. Upsizing to SQL Server (Express ore full version) means you take it up to the next professional level. It means thinking about backup and restore strategies (best start with the simple model when you have no experience) , using SQL that is a lot more performing than the queries you can write in Access, making use of procedures, implementing security.
    The first step should be fine-tuning your tables and look at the new field types. Do you want to use varchars, nvarchars, what type of numbers do you need, what indexes do you need to keep/create, ect. ? Best to redesign your tables on SQL server and move your data from Access to the linked SQL tables through action queries, or better still SSIS packages.
    Implement the security by creating server logins and roles and on the database level users, roles and schema's. The default database schema is dbo, but best to create different schema's for different purposes and security levels .
    Where needed you can create views and procedures on SQL to replace complex Access queries. Views can be linked to Access in the same way as tables are.

    Kind regards
    Noella

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

Similar Threads

  1. Replies: 2
    Last Post: 07-24-2016, 09:49 PM
  2. Replies: 1
    Last Post: 09-01-2015, 02:01 PM
  3. Replies: 3
    Last Post: 07-30-2014, 05:04 PM
  4. Data Collection Access 2013
    By geraldk in forum Access
    Replies: 1
    Last Post: 05-26-2014, 12:28 PM
  5. migrate MS ACCESS to MySQL
    By radjiv in forum Access
    Replies: 2
    Last Post: 08-17-2011, 07:43 AM

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