Been having lots of difficulties with Access 2002 adp and SQL Server 2000 when exporting data to Excel. Problem is that you can only use DoCmd.TransferDatabase with a table, no stored procedures, no views, no recordsets allowed.
Solution 1 is to either use DoCmd.OutputTo which will accept parameterised stored procedures and views but will only work up to maximum of 16284 rows (2^14).
Solution 2 is to populate a temporary table and then pass this table name as a parameter to DoCmd.TransferDatabase. Only problem with this is that if you have only just created the temp table, Access will not see it and you'll get a "cant find object" runtime error. To get around this (and this I couldn't find anywhere, hence this post) use another command object to populate the temp table, then when you make the call to DoCmd.TransferDatabase, Access can see it. Why? Who knows? It seems to work though. If anyone has a better solution then please let me know. Sample code is below:
'::: Check for existence of table
sSQLTableExists = "SELECT * FROM sysobjects WHERE name = " & QuoteString(sTableName) & " AND xType = 'U'"
Set rsDoesTableExist = New ADODB.Recordset
rsDoesTableExist.Open sSQLTableExists, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
If Not rsDoesTableExist.EOF Then
sSQLDropTable = " DROP TABLE " & sSQLQualTableName
'::: Use new connection to prevent "cannot find object" error
Set cnnTemp = New ADODB.Connection
.ConnectionString = CurrentProject.Connection.ConnectionString
.CursorLocation = adUseServer
'::: Execute main INSERT statement to populate temp table
cnnTemp.Execute " INSERT INTO " & sSQLQualTableName & " " & sSQL, lnRecsAffected
If lnRecsAffected > 0 then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "dbo." & sTableName, sFileName, True