MS Query

Owned by Andrew Paydon
Last updated: Oct 20, 2017 by Tim Dawson (Deactivated)

NOTE: Upon conversion, Synonym databases are used to mitigate change risk, such that existing queries will continue to work.   It is important that you do update existing queries to the new structures, as Synonyms have a limited life (in terms of versions).

Modifying an existing query

Due to the implementation of the Synonym database, existing MS Queries should continue to function as previous.

These will need to be modified to utilise the new single database.

The following instructions are for Excel 2013, however the steps are the same for most versions.

 

Before commencing, take note of your database and schema names (see your System Administrator if required).

Video of process

SynergyOne

Select the Data tab > Connections

Select the relevant query > Properties button

  • Below example shows three as there are 3 tabs in my spreadsheet connecting to three separate databases

Select the Definition tab

Update

  • DSN = Synergetic
  • DATABASE = {your database name}

Press OK button

You may be asked to log in, depending if you use database or windows authentication

You should receive a similar error message (this is because the query is still attempting to connect to the original database name, e.g. SynergyOne)

Press the OK button

As previous, ensure you have the correct connection selected

Select the Properties button > Definition tab

There are two methods to this process

First Process - Directly modify the Command Text after the FROM in the query

Replace the Database name SynergyOne to your database name as noted previously (keeping the dbo for the non-finance connection)

Select OK button

Re-enter your password

Query complete

Refresh and test your results

Second Process - Edit Query

Select the Edit Query button

You will  not see the results displayed as yet

Select the SQL button

Remove all old database (SynergyOne) and schema (dbo) names

Press OK button

Your query should now display graphically (unless it is too complex for MSQuery to display)

Selecting the SQL button again will show the replaced database and schema names

Select the Return Data button

Note that the Command text has been updated automatically

Press the OK button (you may be required to enter your password again)

Save your document and the query has now been updated permanently.

SynergyOneFinance

Select the Data tab > Connections

Select the relevant query > Properties button

  • Below example shows three as there are 3 tabs in my spreadsheet connecting to three separate databases

Select the Definition tab

Update

  • DSN = Synergetic
  • DATABASE = {your database name}

Press OK button

You may be asked to log in, depending if you use database or windows authentication

You should receive a similar error message (this is because the query is still attempting to connect to the original database name, e.g. SynergyOneFinance)

Press the OK button

As previous, ensure you have the correct connection selected

Select the Properties button > Definition tab

There are two methods to this process

First Process - Directly modify the Command Text after the FROM in the query

Replace the Database name SynergyOneFinance to your database name as noted previously

Replace the dbo to finance (this is the schema name that the old SynergyOneFinance objects belong to)

Select OK button

Re-enter your password

Query complete

Refresh and test your results

Second Process - Edit Query

Select the Edit Query button

You will  not see the results displayed as yet

Select the SQL button

Remove all old database (SynergyOneFinance) and schema (dbo) names

Press OK button

Your query should now display graphically (unless it is too complex for MSQuery to display)

Selecting the SQL button again will show the replaced database and schema names

Select the Return Data button

Note that the Command text has been updated automatically

Press the OK button (you may be required to enter your password again)

Save your document and the query has now been updated permanently.

Extra Finance Databases

NOTE:

  • Extra finance databases can be called many numerous things (e.g. Foundation, Executive, Swimschool) for the interest of this documentation we will call it SynergyOneExtraFinance
  • Extra finance databases require the user to note the schema name and be careful when entering as you could easily point to the regular finance database
  • Where stated below, do not user extrafinance as your schema name unless you have explicitly used this name

Select the Data tab > Connections

Select the relevant query > Properties button

  • Below example shows three as there are 3 tabs in my spreadsheet connecting to three separate databases

Select the Definition tab

Update

  • DSN = Synergetic
  • DATABASE = {your database name}

Press OK button

You may be asked to log in, depending if you use database or windows authentication

You should receive a similar error message (this is because the query is still attempting to connect to the original database name, e.g. SynergyOneExtraFinance)

Press the OK button

As previous, ensure you have the correct connection selected

Select the Properties button > Definition tab

There are two methods to this process

First Process - Directly modify the Command Text after the FROM in the query

Replace the Database name SynergyOneExtraFinance to your database name as noted previously

Replace the dbo to extrafinance (this is the schema name that the old SynergyOneExtraFinance objects belong to)

Select OK button

Re-enter your password

Query complete

Refresh and test your results

Second Process - Edit Query

Select the Edit Query button

You will  not see the results displayed as yet

Select the SQL button

Remove all old database (SynergyOneExtraFinance) and schema (dbo) names

Press OK button

Your query should now display graphically (unless it is too complex for MSQuery to display)

Selecting the SQL button again will show the replaced database and schema names

NOTE: If your SQL Statement has replaced the dbo with finance (and not your extra finance database schema), then you will need to replace this

CORRECT: 

INCORRECT: 

Select the Return Data button

Note that the Command text has been updated automatically

Press the OK button (you may be required to enter your password again)

Save your document and the query has now been updated permanently.

Creating a new query

Create a new query as you normally would

Choose the new data source name of 'Synergetic'

Before logging in, ensure that the correct Database is selected in the Options screen (this is to ensure you are not accidentally connecting to the old or synonym databases)

When adding tables/views you can select the Owner of:

  • <All> - all objects in the database you have permission to access

  • dbo - only the objects that have a schema of dbo (in Synergetic, all non finance and media objects)

  • finance - only the objects that have a schema of finance

  • media - on the objects that have a schema of media

At present, users are unable to access media schema objects unless a Role is created manually

Users and object permissions will need to be manually managed in SQL Management Studio

  • {extrafinance databases} - if you have any additional finance databases, the objects that have this schema will only display

  • Other options may display in this list, however they are not specifically related to Synergetic

Error Messages

You may receive an error message like [Microsoft][ODBC Driver Manager] Data source name not found and no default drivers specified

This may be due to the name of your ODBC data source changing

When prompted in the [Select Data Source] window, select the appropriate name

Note: Those prefixed with 'SynergyOne' will most likely be older entries, in most cases selecting 'Synergetic' would be the correct data source to select