Results 1 to 5 of 5

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 online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,374
    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 Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    455
    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 !

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,931
    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. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,459
    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.)

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