Results 1 to 10 of 10

Migrate Data From MS Access 2013

  1. #1
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    22

    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
    6,860
    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 online now Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    874
    There is a SQL upsizing wizard available (depending on your version of access), which really does make things effortless.
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,438
    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.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start a sentence with, like, "so"?

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,733
    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.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,400
    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 ridders52; 06-18-2018 at 04:14 AM.
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  8. #8
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,013
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,400
    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:	19 
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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  10. #10
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    596
    @ 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, 08:49 PM
  2. Replies: 1
    Last Post: 09-01-2015, 01:01 PM
  3. Replies: 3
    Last Post: 07-30-2014, 04:04 PM
  4. Data Collection Access 2013
    By geraldk in forum Access
    Replies: 1
    Last Post: 05-26-2014, 11:28 AM
  5. migrate MS ACCESS to MySQL
    By radjiv in forum Access
    Replies: 2
    Last Post: 08-17-2011, 06: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
  •  
Tech Forums: Microsoft Office Forums