You are here: MS Access Products > UI Builder > Product Support > Database Server Support

UI BuilderTM for Microsoft Access

  Deploying UI Builder with Microsoft SQL Server or MySQL

UI BuilderTM for Access can be used in scenarios where Microsoft Access serves as a front-end interface for users, and the data is stored in Microsoft SQL Server or a MySQL database. The information on this page is intended to help customers understand how to implement UI Builder with a remote database server. When moving your data to SQL Server or MySQL ("upsizing"), we strongly recommend you become familiar with the nuances of how Microsoft Access functions with your chosen database server. 

Microsoft Access Upsizing wizard  
Why Upsize?
  • Performance - With Microsoft Access as a front-end for SQL Server or MySQL Server, you will notice performance improvement over storing your database on a shared file server for multiple users.
  • Security - With Access as a front-end for MySQL Server or SQL Server, you can add sophisticated role- and user-level security at the table level.
  • Safety - Most companies have backup plans where the server database is backed up on a consistent basis.
  • Availability - IT departments typically look at a shared file server outage as being acceptable for a short period of time.  If your database needs to be up and running all the time to avoid negative impacts to your business, a database server is typically designed for more uptime, and outages are scheduled off-hours.

UI Builder with Microsoft SQL Server (using Linked Tables)

UI Builder is tested against Microsoft SQL Server where the Access database has linked tables to Microsoft SQL Server 2005.  Please take note of the following:

  • Be sure to leave the following tables on your local Microsoft Access database: tblSubMenu_options, tblUpgrade
  • Users must have authorization to write to tblAppInfo and tblAppInfo_advanced if they are allowed to administer settings.
  • Enterprise Edition customers:  If you are using the Audit History feature, be sure audit tables include a primary key field that does not exist in the source data table.  Otherwise the table will not be updateable, causing errors when the audit feature attempts to write.  The setup wizard step (Step 1) to create an audit table will not function with SQL Server. We recommend you create the table using the Audit setup Step 1 on your local database, then use the Access Upsizing Wizard to move the audit table to the SQL Server.
  • The linked table verification and repair feature of UI Builder will not recognize or repair broken links to SQL Server.

 

UI Builder with Microsoft SQL Server (using ADP Project)

UI Builder has not been certified against Microsoft SQL Server as an ADP project.  Microsoft's forward strategy is to move customers away from using ADP Project databases.  Microsoft recommends using MDB/ACCDB to connect to SQL Server as opposed to ADP projects. Refer to Microsoft TechNet for additional information.  There are a few important limitations to note:

  • The Enterprise Edition auditing functionality will work correctly, however the setup wizard step (Step 1) to create an audit table will not function with SQL Server. You will need to create the audit table on the target server, and add the audit fields to the table manually. Refer to the Enterprise Edition guide for the list of fields.
  • The upgrade feature built in to UI Builder will not function as UI Builder needs to create new tables in the database. You can upgrade UI Builder by manually importing forms and modules from the latest version released from OpenGate Software. The newest version's data in table tblSubMenu_options should replace the data in the same table on the SQL Server.

 

UI Builder using MySQL Server

UI Builder has been tested against MySQL where the Access database has linked tables to MySQL Server 5.x.  Please take note of the following:

  • Be sure to leave the following tables on your local Microsoft Access database: tblSubMenu_options, tblUpgrade
  • Users must have authorization to write to tblAppInfo and tblAppInfo_advanced if they are allowed to administer settings.
  • Enterprise Edition customers:  If you are using the Audit History feature, be sure audit tables include a primary key field that does not exist in the source data table.  Otherwise the table will not be updateable, causing errors when the audit feature attempts to write.  The setup wizard step (Step 1) to create an audit table will not function with MySQL Server. We recommend you create the table using the Audit setup Step 1 on your local database, then move the audit table to the MySQL Server.
  • The linked table verification and repair feature of UI Builder will not recognize or repair broken links to MySQL Server.

 

Additional Resources