InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:36:09 GMT20http://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxDo you really need the data in Access? If not, you could setup a linked table(s) to the source data in SQL and design the report on those tables.Wed, 13 Mar 2013 11:20:44 GMTDon Urquharthttp://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxSure you could do it that way. Open your excel and have it pull down the table into a worksheet and then have that worksheep mapped to a file table in access. Not sure how that's going to work with dynamic column names but it's certainly doable.Tue, 12 Mar 2013 11:29:59 GMTErin Ramsayhttp://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxHey Erin,this is an access report, where the client has the front GUI, i am populating the back end data tablesis there way, i could populate my data into excel and feed the access tables ....? Thanks for your help...?Tue, 12 Mar 2013 09:26:19 GMTlraju10http://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxwe thought about implementing this report in SSIS, but we are dealing with 24 months worth of sales data, as well quarterly and yearly data, i have dynamically changing columnsFor Example : for the Month for August 2012, my table columns names will be tblSalesData : SalesId, Name, Address, Zip,[Aug'12],[Jul'12],[Jun'12],[May'12]...... [Sep'10],[12 Months Ended Aug'12], [Q2'12],[Q1'12],[Q4'11].....[Q4'10] for the Month of September 2012, my table columns names will betblSalesData : SalesId, Name, Address, Zip,[Aug'12],[Jul'12],[Jun'12],[May'12]...... [Sep'10],[12 Months Ended Sep'12],[ Q3'12] [Q2'12],[Q1'12],[Q4'11].....[Q4'10] , i have built this column names using dynamic sqlIn SSIS, is there a way to map this changing column names in Data Flow Task ...?may i know your thoughts on this....? appreciate your help.. Note: No Partial Quarters are allowed, so for August we start from [Q2'12], where as September we start from [Q3'12]Tue, 12 Mar 2013 09:21:20 GMTlraju10http://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxAnother solution could be, depending on the design of the report, to put it in Excel and have a linked datasource that updates on open so that every time someone opens the report the current numbers are available. I've done book-keeping reports for clients that way.Tue, 12 Mar 2013 08:35:56 GMTErin Ramsayhttp://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxHallo Iraju10,this is not possible. To push data into ACCESS with OPENROWSET.Basically the only possible solution is an access db and start of it by a SQL Server JOB.In the access db you have an autoexec macro which may pull the data into local tables.Another option maybe BCP but I'm not sure whether it supports mdb as OUTPUT-Format.Last but not least think about an SSIS package - that's exactly for solutions you are looking for.Tue, 12 Mar 2013 03:44:53 GMTUwe Rickenhttp://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxHi Erin, Client requirement is to have a report in push off a button.. using ODBC Linked Tables is more of a manual process.. let me know your thoughts on this.....Mon, 11 Mar 2013 16:51:29 GMTlraju10http://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxWhat about using ODBC and creating a linked table in Access so you have constant and immediate access to the data for the report?Mon, 11 Mar 2013 16:31:25 GMTErin Ramsayhttp://www.sqlservercentral.com/Forums/Topic1429485-392-1.aspxHi Guys,I am trying to insert data from SQLServer into an access database,using T-SQLI used OPENROWSET to Select the data from access into SQLServer, it worked fineQUERY:SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','"ServerName"';'admin';'',TableName);My table has columns with the changing month namesis there any way, i could do some sort of SELECT INTO an access DB using T-SQL ..?Note : This is an automated report, i cannot use Import Export Wizard, there is no SSIS installed on the machineMon, 11 Mar 2013 14:53:54 GMTlraju10