Results 1 to 9 of 9
  1. #1
    UKRTB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    Manchester
    Posts
    1

    Move entire Access DB to SQL

    Is there a programme that will move an entire MS database to SQL?
    I have a very well used MS db that originated in 2010 and has had lots of tables, queries, forms and reports added over the years.
    It is a programme/project database and tracks, tasks, lessons learned, finances, risks, decisions etc etc.
    I now need to share it with co-workers and wondered if SQL was an option.
    Any help appreciated.
    Thanks
    Rob

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SQLServer (expensive) or freebies SQLServer Express or MySQL are options. However, Access can function quite well as a multi-user database.

    Review https://www.microsoft.com/en-us/down....aspx?id=42656
    Last edited by June7; 12-24-2017 at 12:31 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just to add to June's answer, Access contains an upsizing wizard to move data to SQL Server.
    It's part of the Database Tools ribbon menu.

    One warning
    If you use attachment datatype or multivalue fields, these cannot be upsized.
    A very good reason for not using them.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Also, calculated type fields will not migrate.

    Never noticed the SQL migration tool, although think I've read of it often enough and should have remembered it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The upsizing wizard works well
    Forgot about calculated fields as I also never use those

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    just be aware when upsizing that you will not be taking advantage of performance improvements available in SQL Server/Express without the rewriting of queries etc. Either to make use of SQL Server functionality such as stored procedures or to minimise levels of network traffic. You haven't said but I presume your db is already split into front end/backend - it will only be the backend that will be upsized. If the system is currently a single user system with the backend located on your local drive, expect to see a performance drop due to the slower network speeds v the speed of access to a local drive.

  7. #7
    AdrianG001 is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2018
    Posts
    9
    You can move entire Access DB to SQL using upsizing wizard.

    Before you upsize your Access database to a SQL Server database or Access project, consider doing the following:

    • Back up your database Although the Upsizing Wizard doesn't remove any data or database objects from your Access database, it's a good idea to create a backup copy of your Access database before you upsize it.
    • Ensure you have adequate disk space You must have adequate disk space on the device that will contain the upsized database. The Upsizing Wizard works best when there is plenty of disk space available.
    • Create unique indexes A linked table must have a unique index to be updateable in Access. The Upsizing Wizard can upsize an existing unique index, but can't create one where none exists. If you want to be able to update your tables, make sure you add a unique index to each Access table before upsizing.
    • Assign yourself appropriate permissions on the SQL Server database

      • To upsize to an existing database, you need CREATE TABLE and CREATE DEFAULT permissions.
      • To build a new database, you need CREATE DATABASE permission, and SELECT permissions on the system tables in the Master database.


        Step 1: Choose to upsize to an existing database or a new database

        On the first page of the Wizard, you specify whether you want to upsize the Access database to an existing SQL Server database or create a new SQL Server database.

        • Use existing database If you select this option and then click Next, Access displays the Select Data Source dialog box so that you can create an ODBC connection to the existing SQL Server database.
          About ODBC data sources
          A data source is a source of data combined with the connection information needed to access that data. Examples of data sources are Access, SQL Server, Oracle RDBMS, a spreadsheet, and a text file. Examples of connection information include server location, database name, logon ID, password, and various ODBC driver options that describe how to connect to the data source.
          In the ODBC architecture, an application (such as Access or a Microsoft Visual Basic program) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (for example, Microsoft SQL ODBC driver) to connect to a data source (in this case, a SQL Server database). In Access, you use ODBC data sources to connect to data sources external to Access that do not have built-in drivers.
          To connect to these data sources, you must do the following:

          • Install the appropriate ODBC driver on the computer that contains the data source.
          • Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the Microsoft Windows registry or a DSN file, or a connect string in Visual Basic code to pass the connection information directly to the ODBC Driver Manager.
            Machine data sources
            Machine data sources store connection information in the Windows Registry on a specific computer with a user-defined name. You can use machine data sources only on the computer they are defined on. There are two types of machine data sources — user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer and system-wide services. A machine data source is especially useful when you want to provide added security, because only users who are logged on can view a machine data source and it cannot be copied by a remote user to another computer.
            File data sources
            File data sources (also called DSN files) store connection information in a text file, not the Windows registry, and are generally more flexible to use than machine data sources. For example you can copy a file data source to any computer with the correct ODBC driver so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.
            A file data source can also be unshareable. An unshareable file data source resides on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.
            Connect strings
            In a module, you can define a formatted connect string that specifies connection information. A connect string passes the connection information directly to the ODBC Driver Manager, and it helps simplify your application by removing the requirement that a system administrator or user first create a DSN before using the database.

        • Create new database If you select this option and then click Next, Access displays a page where you enter information about the new SQL Server database.

          • What SQL Server would you like to use for this database? Type the name of the server you would like to use.
          • Use Trusted Connection You can use a trusted connection, that is, SQL Server can integrate with the Windows operating system security to provide a single log on to the network and the database.
          • Login ID and Password If you don't use a trusted connection, type the logon ID and password of an account with CREATE DATABASE privileges on the server.
          • What do you want to name your new SQL Server database? Type the name of the new SQL Server database. Access revises the name if it conflicts with an existing database name and adds a numbered suffix


            Step 2: Choose which tables to upsize

            In this step, you select the Access tables that you want to upsize to the SQL Server database. Select the tables that you want to upsize, and then use the arrow buttons to move them to the Export to SQL Serverlist. Alternatively, you can double-click a table to move it from one list to the other.
            The Available Tables list includes all linked tables except for SQL Server tables already in a SQL Server database. Linked tables that point to a SQL Server database that has been selected for upsizing automatically appear in the Export to SQL Server list box and can't be removed. Tables that are not currently visible in the Navigation Pane are also excluded, including hidden tables and system tables.

            Tip: Any table that has a name ending in "_local" is excluded from the list of available tables to prevent upsizing tables that have already been upsized. If you do want to upsize these tables again, rename them before you run the Upsizing Wizard by removing the suffix "_local".

    Step 3: Specify the attributes and options to be upsized

    In this step, you select which table attributes to upsize to your SQL Server database. By default, all attributes are selected for upsizing by default.
    Note: By default, the Upsizing Wizard converts Access field names to legal SQL Server field names, and converts Access data types to the equivalent SQL Server data types.


    Step 4: Choose how to upsize your application

    On the next page of the wizard, you can select one of three different ways to upsize your Access database application. Under What application changes do you want to make?, select one of the following options:

    • Create a new Access client/server application If you select this option, the Upsizing Wizard creates a new Access project. The Upsizing Wizard prompts you for a name, which defaults to the current Access database name, adds a "CS" suffix, and then stores the project in the same location as the existing Access database.
      The Upsizing Wizard creates the Access project file and then upsizes all the database objects from the Access database to the Access project. If you don't save the password and user ID, then the first time you open the Access project, Access displays the Data Link Properties dialog box so that you can connect to a SQL Server database.
    • Link SQL Server tables to existing application If you select this option, the Upsizing Wizard modifies your Access database so that your queries, forms, reports, and data access pages use the data in the new SQL Server database rather than the data in your Access database. The Upsizing Wizard renames the Access tables you upsize with the suffix "_local." For example, if you upsize a table named Employees, the table is renamed Employees_local in your Access database. Then, the Upsizing wizard creates a linked SQL Server table named Employees.
      Note: After the upsizing operation is complete, the tables that were renamed with the "_local" suffix will no longer be used. However, it is a good idea to retain the local tables until you verify that the upsizing was successful. At a later date, you can delete the local tables to reduce the size of your Access database. Be sure to back up your database prior to deleting any tables.

      Queries, forms, reports, and data access pages based on the original Employees tables will now use the linked SQL Server Employees table. Many of the properties of the fields in the original local table are inherited by the new local table including, Description, Caption, Format, InputMask, and DecimalPlaces.
    • No application changes Select this option if you only want to copy your data to the SQL Server database, and not make any other changes to your existing Access database application.

    Save password and user ID By default, the Upsizing Wizard creates linked tables in the existing application or creates an Access project without saving the username and password. This means that users are prompted for a username and password each time they log on to a SQL Server database.
    If you select Save password and user ID, users can connect to a SQL Server database without logging in. If you select Create new Access client/server application, the Access project stores the username password in the OLE DB connection string.

    The Upsizing Wizard report


    When you click Finish, the Upsizing Wizard creates a report that provides a detailed description of all objects created, and reports any errors encountered during the process. The Upsizing Wizard displays the report in Print Preview, and you can then print or save the report, for example, as an XPS or PDF file. The report is not saved as an Access object when you close the Print Preview window.
    The Upsizing Wizard report contains information about the following:

    • Upsizing parameters, including what table attributes you chose to upsize and how you upsized.
    • Table information, including a comparison of Access and SQL Server values for names, data types, indexes, validation rules, defaults, triggers, and whether or not time stamps were added.
    • Any errors encountered, such as database or transaction log full, inadequate permissions, device or database not created, table, default, or validation rule skipped, relationship not enforced, query skipped (because it cannot be translated to SQL Server syntax), and control and record source conversion errors in forms and reports.

      Adrian Gates
      Sr. Cloud Expert - CloudDesktopOnline

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @AdrianG

    Why didn't you just give the web page link rather than copy the entire article from here:
    https://support.office.com/en-us/art...7-e6e759d72924

    Also on your other posts today, you have given links to web pages - but not used the hyperlink button in the quick reply toolbar
    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

  9. #9
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If you're moving to SQL, don't forget to have a look at your indexes and create a good maintenance plan, or you might get into trouble with logs blowing up and your DB getting very slow.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-11-2014, 11:26 AM
  2. Update and Append Entire table in Access
    By Yoyo120 in forum Access
    Replies: 1
    Last Post: 06-05-2014, 02:42 PM
  3. Replies: 1
    Last Post: 11-07-2013, 11:15 AM
  4. Copy Paste the entire MS Access Database
    By mkc80 in forum Access
    Replies: 6
    Last Post: 08-24-2012, 01:07 PM
  5. Replies: 9
    Last Post: 12-28-2009, 04:01 PM

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