1. Home
  2. Program Maintenance
  3. Converting from Access Database to SQL Express
  1. Home
  2. Reference Topics
  3. Converting from Access Database to SQL Express
Searching...

Converting from Access Database to SQL Express

Contents

Overview

The following is provided for those that have outgrown the Access database limits of 2GB. Converting to SQL from Access is needed when you outgrow the size constraints of Access. The information is provided to make creating, configuring, and converting to a SQL database as smooth as possible. Please be sure to read the entire set of instructions before starting the conversion, and if you have any questions about the conversion or would like assistance, please reach out to Stone Edge Support at Support@stoneedge.com.

Requirements

See System Requirements Article.

SQL Server

Downloading and Installing SQL Server Express

  1. Be sure to download the SQL Server to a computer or server where all network users can connect to and have read and write permissions to and from the computer.
  2. Make a note of the PC name where SQL Server is now installed and the name of the SQL Server Instance (by default, the Instance Name is SQLEXPRESS).

Setting up SQL Express Server

Make sure the SQL Server Instance is running.

  1. Start Menu>All Programs>Microsoft SQL Server 2019>Configuration Tools>SQL Server Configuration Manager.
    • Find the service called SQL Server that has the name of the instance you installed after it.
    • Right-click and go to Start.
  2. If Start is not clickable, right-click on the service and go to Properties, then to service, make sure Start Mode is set to Automatic, click Apply and Ok, and restart the service.
  3. In SQL Server Configuration Manager, make sure the SQL Browser service is running.
    • Start Menu>All Programs>Microsoft SQL Server 2019>Configuration Tools>SQL Server Configuration Manager.
      • Look for SQL Server Browser and right-click on it, and go to Properties.
        • Go to the Service tab and make sure Start Mode is Automatic, then click Apply.
        • Go to the Log On tab and then click Start. Then close out of that window.
  4. Within SQL Server Configuration Manager, make sure the TCP/IP protocol is enabled.
    • In Configuration Manager, expand the option for SQL Server Network Configuration.
    • Select the option for the Instance of SQL installed.
      • Right-click on TCP/IP under Protocol Name and then make sure Enabled is set to Yes. Also, click on the IP Addresses tab, scroll to the bottom of the screen, and note the port number(s) listed. These ports may be necessary when creating the System DSN. If no ports are entered, add 1433 under TCP Port. You will need to stop and restart the SQL Server Service as discussed above.

Creating a Blank SQL Database

You will need to Create a blank SQL database within SQL Server Management Studio to export your Existing Access database.

  1. Open up SQL Server Management Studio from the Start Menu.
    • Once it opens right click on the database and select New Database.
      • Name the database anything you’d like.
      • Settings for Initial Size, Autogrowth, Collation, Recovery Model.
      • Initial size should be the size of their current Access database plus 25% (e.g., a 1-gigabyte Access data file, allocate at least 1.25GB on SQL Server). For the Log File, please set it to half the size of the Access database.
      • Autogrowth should typically be Unrestricted and by 10%.
      • The collation should be Latin1_General_CP1_CI_AS.
  2. Create a SQL user set to use SQL Authentication.
    • To add users in SQL Server Management Studio, expand Security, right-click on Logins, and choose New Login.
      • Enter a login name ( same as Stone Edge Login preferred).
      • Select SQL Server Authentication.
      • Please enter a password and then confirm it.
      • Refer to your company policies on how to address the boxes regarding password policy.
      • Under the default database, select the blank database you just created.
      • Click User Mapping on the left-hand side of the screen.
      • In the first box in the center of the screen, select the database you created earlier.
      • On the lower box, select db_datareader, db_datawriter, and db_owner and then click Ok.
  3. Ensure Mixed Mode Authentication is enabled in SQL Server Management Studio.
      • Look for the first line on the left-hand side of the screen in SQL Server Management Studio (the object explorer window). Right-click on it and choose Properties.
      • Go to Security and click the radio button for SQL Server and Windows Authentication and then click Ok.
      • Restart the SQL Server Service by right-clicking on the first line in the Object Explorer Window and selecting Restart.

Windows Firewall Setup

Under the Windows Firewall, open the Incoming and Outgoing ports for the port mentioned above.

  • Go to the Start Menu and begin typing Firewall, then select it. The Firewall & network protection screen will open.
    • Click Advanced Settings.
      • Select Inbound Rules on the left, and then on the right side, select New Rule.
        • Select port and click Next.
        • Enter 1433 in the Specific local ports text box and click Next.
        • Make sure Allow the connection is selected and click Next.
        • Keep all checkboxes selected and click Next.
        • Enter SQL-Inbound for the name and click Finish.
      • Select Outbound Rules on the left, and then on the right, choose New Rule.
        • Select port and click Next.
        • Enter 1433 in the Specific local ports text box and click Next.
        • Make sure Allow the connection is selected and click Next.
        • Keep all checkboxes selected and click Next.
        • Enter SQL-Outbound for the name and click Finish.

Setting up a DSN / ODBC connection

  • If Microsoft Access and Stone Edge are installed on the server, you will want to create a DSN using the above user account information. Follow the steps under Creating a DSN.
  • If Microsoft Access or Stone Edge is not installed on the server, go to any workstation and make sure the latest version of StoneEdge is installed. Create a System DSN on the workstation.

Creating a ODBC / DSN

  1. Whether it is 32 bit or 64-bit, your version of Windows determines where the DSN will need to be created.
    • For the 64-bit version of Windows.
      • C:\Windows\SysWOW64\odbcad32.exe.
    • For the 32-bit version of Windows (Ancient PC’s).
      • Under Control Panel>Administrator Tools>Data Sources (ODBC).
  2. Once you have the ODBC Data Source Administrator window open, select System DSN>Add.
  3. Under Create New Data Source, look for SQL Server (typically near the end of the list) and then click Finish.
  4. Name the DSN. This value can be anything, but it’s recommended that it be kept the same for each workstation. For this guide, we will call Stone Edge.
  5. Select the Server and Instance name (#2 under Downloading and Installing SQL Server Express), click Next.
  6. If using Windows Authentication, move on to Step #9.
  7. If using SQL Server Authentication select the radio button to use it.
  8. Enter the user name and password defined for a user created in SQL Server Management Studio.
  9. Under Client Configuration, make sure the port number entered matches the port number in Step #4 in Setting SQL Express Server. Then close that window and click Next.
  10. Suppose the correct SQL Server Name and Instance is entered, and there are no issues with the port being used or the user account or user name and password being used on the next screen. In that case, you will want to select the SQL database name you created in Step# 2 under Creating a Blank SQL Database.
  11. Then click Next.
  12. Click Finish.
  13. Click Test Data Source. If it comes back successful, click Ok.

Troubleshooting DSN Issues

  1. Obtain the port number referenced in SQL Server Configuration Manager under TCP/IP Protocols>Properties>IP Addresses.
  2. Obtain the IP address of the server.
    1. Go to Start>Run and then enter cmd.exe.
    2. Once the command prompt opens, key in ipconfig and look for the IP address.
  3. Go to another workstation.
    1. Confirm that Telnet is installed. Go to Control Panel>Turn Windows Features on or off and then click off on Telnet Client and click Ok.
    2. Go to Start>Run and then enter cmd.exe
    3. Key in telnet IP address portnumber and then hit Enter (for example, if the IP Address is 192.111.1.11 and the port is 1433, you would key in telnet 192.111.1.10 1433).
    4. If the screen changes to a blank black Window, that means you can connect to the server on that port. If you get any other error messages, it either means there is a Firewall blocking the connection or the SQL Server Browser service is not started, or the PCName and or SQLInstance is incorrectly entered.

Converting to SQL in Stone Edge

Performing the actual SQL conversion.

  1. Start Stone edge on the server or a workstation if it is not installed on the server where the SQL Server has been installed. under Settings>Data Functions>Convert to SQL Server
    1. Select the DSN you created under Use and Existing DSN
    2. Set the Network location for shared files
      1. To find what path to use on the main screen in Stone Edge, go to Info and look at the current location of their Access database. This will more than likely be the path to use unless the merchant specifies they want to use a different path.
  • As the conversion is working, go to the other machines, create DSN’s and make sure the same version of StoneEdge is installed on the workstations compared to the server or on the workstation where you are running the conversion.
  • Once the conversion is finished on each workstation, open Stone Edge and click Switch Store at the top of the screen, click the System DSN tab, and select the System DSN you created on that machine.
  • Rename the old Access database to something else so when they re-open StoneEdge, no workstations accidentally reconnected to the Access database. Something as simple as adding –Access to the end of the file name will suffice.
  • Install SQL Stored Procedures in SQL Server Management Studio

SQL Stored Procedures for Stone Edge Enterprise Edition

Knowledge Base Article: SQL Stored Procedures

  • If  Stone Edge is not installed on the server, go to any workstation where Stone Edge is installed and then go to C:\StoneEdge and look for the SQL folder. Copy that folder to someplace on the network where it will be accessible from the server. Then go to the server and follow the steps in the kb article above. In Step #3 from that article, browse to the location where you saved the SQL folder compared to C:\StoneEdge.

Importing Customizations and mapping Shipping Systems.

  • Import any custom reports, forms, modules, or queries they may have on one workstation and then tell them they will need to do the same thing on each workstation.
  • Perform UPS Worldship or FedEx Ship Manager mappings if necessary
Updated on December 7, 2021

Was this article helpful?

Yes No

Related Articles