This tutorial shows how to create a version of Stonefield Query that can report on both the Microsoft SQL Server and Access versions of the Northwind database. This tutorial assumes you completed the "creating a project" tutorial for either Access or SQL Server and that project is open in Stonefield Query Studio.

  • Select the Northwind database in the TreeView.

  • Click the Create "get data sources" script link in the properties pane; this creates a new script called Northwind.GetDataSources and selects that script.

  • Select the desired language and enter one of the following scripts, depending on which language you chose.

    Visual FoxPro

        lparameters toApplication as SQApplication, toDatabase as Database
        loDataSource = toDatabase.DataSources.AddItem('ODBC', ;
          'SQL Server')
        loDataSource.Driver   = 'SQL Server'
        loDataSource.Database = 'Northwind'
        loDataSource.Server   = 'server'
        loDataSource.UserName = 'sa'
        loDataSource.Password = 'password'
        loDataSource = toDatabase.DataSources.AddItem('ODBC', ;
          'Access')
        loDataSource.Driver   = 'Microsoft Access Driver (*.mdb)'
        loDataSource.Database = 'path\Northwind.MDB'

    VBScript

        function Main(Application, Database)
        dim DataSource
        set DataSource = Database.DataSources.AddItem("ODBC", _
          "SQL Server")
        DataSource.Driver   = "SQL Server"
        DataSource.Database = "Northwind"
        DataSource.Server   = "server"
        DataSource.UserName = "sa"
        DataSource.Password = "password"
        DataSource = Database.DataSources.AddItem("ODBC", _
          "Access")
        DataSource.Driver   = "Microsoft Access Driver (*.mdb)"
        DataSource.Database = "path\Northwind.MDB"
        end function

    JavaScript

        function Main(Application, Database) {
        var DataSource = Database.DataSources.AddItem('ODBC', 
          'SQL Server') ;
        DataSource.Driver   = 'SQL Server' ;
        DataSource.Database = 'Northwind' ;
        DataSource.Server   = 'server' ;
        DataSource.UserName = 'sa' ;
        DataSource.Password = 'password' ;
        DataSource = Database.DataSources.AddItem('ODBC', 
          'Access') ;
        DataSource.Driver   = 'Microsoft Access Driver (*.mdb)' ;
        DataSource.Database = 'path\\Northwind.MDB' ;
        }

    C#

        public static bool northwind_GetDataSources(SFQApplication sfqApplication, 
          database database)
        {	
          ODBCDatasource dataSource;
          dataSource = database.DataSources.AddItem("ODBC", 
            "My Sample ODBC Datasource");
          dataSource.Driver = "SQL Server";
          dataSource.Database = "MyDatabase";
          dataSource.Server = "MyServer\\ServerInstance";
          dataSource.UserName = "sa";
          dataSource.Password = "";
          return true;
        }

    VB.NET

        public shared function northwind_GetDataSources(sfqApplication as
          SFQApplication, database as Database) as Boolean
          Dim dataSource as ODBCDataSource
          dataSource = database.DataSources.AddItem("ODBC", 
            "My Sample ODBC Datasource")
          dataSource.Driver = "SQL Server"
          dataSource.Database = "MyDatabase"
          dataSource.Server = "MyServer\ServerInstance"
          dataSource.UserName = "sa"
          dataSource.Password = ""
          Return true
        End Function

    In place of server, password, and path, insert the appropriate server name (such as "(local)"), password, and the path to Northwind.mdb.

  • Click the Configuration panel bar and select the Data node.

  • Select the Allow Multiple Data Sources setting and change it to True.

  • If you want to query on both Access and SQL Server databases on the same report, set the Allow Queries on Multiple Data Sources setting to True.

  • Click the Launch Stonefield Query button ().

  • Select the report you created in the "creating a project" tutorial and run it.

  • Select Open Database from the File menu, select a different data source from the list, and click the OK button.

  • Run the report again. The results may not look very different because the Access and SQL Server Northwind database have almost identical contents. You can confirm that you are querying on the appropriate database by making some changes in one or the other and then running the report again.

  • If you set the Allow Queries on Multiple Data Sources setting to True, select the Filter page in the Reports Explorer and notice there's a Database button. Click this button and turn on both Access and SQL Server databases, then run the report again. This time you'll notice there are twice as many records as before, since the report includes records from both databases.