...
Save your document and the query has now been updated permanently.
SynergyOneFinance
Select the Data tab > Connections
...
- 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. SynergyOneSynergyOneFinance)
Press the OK button
As previous, ensure you have the correct connection selected
...
Replace the Database name SynergyOne SynergyOneFinance to your database name as noted previously (keeping the dbo for the non-finance connection
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
...
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 (SynergyOneSynergyOneExtraFinance) and schema (dbo) names
Press OK button
...
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)
...