Private dbpath As String
Private strConn As String
Private querystring As String

Private Sub GenerateSchema()
	dbpath = Server.MapPath("NWIND.MDB") & ";"

	strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath
	querystring = "SELECT * from " + DropDownList1.SelectedItem.Value & " where Month(OrderDate)=" + DropDownList2.SelectedValue
	

	Dim myAutoSchema As New System.IO.FileInfo(Server.MapPath("DynamicSchema.schema"))
	Dim connection As New System.Data.OleDb.OleDbConnection(strConn)
	Dim query As String = querystring
	Dim name As String = "Dynamics query"
	Dim sb As GrapeCity.ActiveAnalysis.Schema.SchemaBuilder = Nothing
	sb = GrapeCity.ActiveAnalysis.Schema.SchemaBuilder.AutoGenerate(name, connection, query)
	sb.SaveSchema(myAutoSchema)
	connection.Close()
End Sub


Private Sub SetConnection()
	GenerateSchema()
	Dim myDS = New RdDataSource()
	myDS.ConnectionString = strConn
	myDS.ConnectionType = GrapeCity.ActiveAnalysis.DataSources.ConnectionType.OleDb
	myDS.CustomSchemaFile = querystring
	myDS.QueryString = "SELECT * from " + DropDownList1.SelectedItem.Value & " where Month(OrderDate)=" + DropDownList2.SelectedValue


	myDS.CustomSchemaFile = Server.MapPath("DynamicSchema.schema")
	myDS.Connect()
	PivotView1.DataSource = myDS

End Sub

Private Function LoadTables() As DataTable
	dbpath = Server.MapPath("NWIND.MDB") & ";"

	strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath

	Dim conn As New OleDbConnection(strConn)
	conn.Open()
	Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
	Return schemaTable
End Function

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

	If Not IsPostBack Then

		Dim dt As DataTable = LoadTables()
		Dim x = dt.Columns(0)
		Me.DropDownList1.Items.Add("Select Table")
		For i As Integer = 0 To dt.Rows.Count - 1
			Me.DropDownList1.Items.Add(dt.Rows(i)(2).ToString())
		Next
	Else

		SetConnection()
	End If


End Sub