TransferSpreadsheet method when connected to SQL DB?

[jgoodman00]

Has anybody sucessfully used this method with a view or stored procedure, using an Access project, connected to a SQL server database?

It would appear that it only supports the exporting of a table, rather than a view from an ADP.

I need to export views from the server, into an excel spreadsheet.

Any ideas?? James Goodman

 

[RobertT687]

Haven't seen this problem, but you could always create a Query based on the View or Stored Procedure and use the query as the basis of the TransferSpreadsheet.

 

[cmmrfrds]

Views in SQL Server are ways to restrict access to data. Views can give subsets of columns from a table or combine columns from multiple tables in order to restrict what a user can access. Look on views as templates or maybe virtual tables that expose columns in other tables. You can also, restrict in a view by a where clause which would restrict rows instead of columns.

To use a view then SELECT on the view instead of table names. As RobertT687 suggested, you will need to setup up a query.

 

[jgoodman00]

I am not entirely sure you understand my question.

A SQL Server view is a select statement.
A SQL Server view is also a query (in SQL Server terms).
A SQL Server stored procedure is the equivilent to an action query (& much more) in Access.

An Access Data Project (ADP) is a way of creating client-server databases, based upon SQL server databases. As a result the objects present in the Tables & Queries tabs are only those objects which exist in the SQL server database itself. An Access SELECT query will be transferred into an SQL view, & any Access action queries (such as insert, update etc) will be converted into Stored Procedures.

As a result, there are no 'Queries', only views & sp's.



Hence my problem...


Have i clarified things??




James Goodman

 

[kunjal29]

I think you have to use &quot;SELECT * FROM <VIEW>;&quot; as the string parameter where the table name is supposed to appear.

Kunjal

 

[jgoodman00]

This does not work either. The help file states:

TableName Optional Variant. A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet.


Hence I do not think this method can be used with a SQL server database. However, I am sure there must be another way of achieving this... James Goodman

 

[smuckers]

Good Morning!

Just a thought. Could you produce a local temporary table out of a view or sp? Or, load a permanent local table with values from SQL Server? Then, try to export that local table to MS Excel? Not all tables have to link to SQL Server, some can be local.

Good luck!

Smuckers.

 

[cmmrfrds]

Smuckers, can you briefly explain how to create a LOCAL table in an Access Project (adp). I am not aware of how it is done.

The transfer spreadsheet does not seems to work as one would like, such as, it does in an MDB.
Some alternatives.
1. make 2 steps. First step do a transferdatabase which exposes the view and create a table form the view. The second step would be to transferspreadsheet from the table.
Then it would be necessary to drop the table. Seems messy.
2. Create a function in the standard module that does an ADO connection to the spreadsheet and another ADO connection to the View. Will need to setup a loop to read the recordset from the View and update the excel spreadsheet. Seems like a reasonable option.
3. Use the tools and facilities in SQL Server. Setup a DTS package to export or update the excel spreadsheet from the view. Could, also, use the scheduler in SQL Server to run package on a scheduled basis. Good option is you have Access to the SQL Server.

 

[jgoodman00]

Within an ADP, you can use the TransferSpreadsheet method, using a SQL server table as the source. However, in a relational database, it is unlikely this will yield helpful results.

Smuckers,
Creating a temp table by using a sp is possible. However I dont like using temporary tables, especially considering many tables would be required (it is not 1 TransferSpreadSheet method which is called in my case, more like 25!).

cmmrfrds,
I have thought a little about using a dts package in order to run the export, & I think this would be the best option. I think idea 2 would be very system intensive, especially considering in my case many thousands of records (which will have tens of rows each) need to be exported.

I believe at this stage, ADP's are in their infancy. As a result, certain methods & functions are not yet working perfectly. I am guessing as the program evolves, this problem will be addressed (crossing fingers!)


James Goodman

 

[jgoodman00]

As an update to this situation, I have been in contact with microsoft. They have suggested using the DoCmd.OutputTo method instead of the TransferSpreadsheet method when using an ADP connected to an SQL db.

However, this appears to have some limitations:
1. It does not appear possible to export several views into a single workbook, as individual worksheets. Instead individual workbooks must be created for each view.
2. There is some kind of error generated regarding numerical datatypes. I have tried exporting several views using this method, but when the excel file is opened, an error message ('File Error. Some number formats may have been lost.'). They are yet to resolve this issue. This problem does not appear solely connected to the OutputTo method. If the export is carried out manually, the same problem is encountered. However, if the same data is exported through dts, the error does not occur.


Has anybody else used this method?
Have you found similer errors?
James Goodman