SQL Query to CSV

[Chris]

Hi,
Does anyone know if access (.adp) has a native method
where I can provide a SQL statement and have the results
written out to a .csv file. I use this as a source of
mailmerge data.

I have tried the Docmd.outputTo method as so:
'DoCmd.OutputTo acOutputStoredProcedure, strSQL,
acFormatXLS, FilePath, False

and this works fine to excel for small datasets, but fails
for larger datasets. (I also really need .csv as excel
will have a maximum row limit)

I can write something to do it, but would prefer to use a
native Access method if one exists.

Thanks in Advance
Chris

 

[Van T. Dinh]

TransferText Method / Action for csv + large number of "rows", perhaps?

 

[Chris]

Thanks Guys,
I looked at both these methods but could not
see how to base the file on a SQL string, rather than a
table or stored proc. Unlike the OutputTo method, where I
was able to specify the type as stored proc but still
supply a sql string as an arguement.

After looking into it a bit more I think I may now be able
to execute the query into a #temp table on the SQL server
and then use one of the methods you mentioned to write it
to a file.

The other alternative would be to write a function using
ado/adox to create a recordset based on the SQL string and
loop through it, writing the contents out to a csv. More
work and more chance of introducing an error, but may be a
good function to have for the future.

Cheers
Chris

 

[Van T. Dinh]

I am not sure I understood you first sentence. It seems to me that the word
"not" is at the wrong place?

(Not having used ADP, but) it seems to me that you should be able to use a
View as the Source for the TransferText Method since SQL Server Views are
nearly the same as Access Queries.

Have you tried to create a View in the ADP and used the (saved) View as the
"tablename" in the TransferText Method?

Don't trust the Help 100%. I found things that work but not mentioned in
Help, especially A2K and AXP Help. The best way is to think logically and
then try it.

 

[Michael Shao [MSFT]]

Hi Chris,

You are right that TransferSpreadsheet is different from OutputTo function.
You need create the temple table in the stored procedure and use
DoCmd.RunSQL to implement the stored procedure. After that, you could use
TransferSpreadsheet to meet your requirement as below:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "temp table
name",
"File name"

Hope it helps

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Al Walton]

-----Original Message-----
Hi,
Does anyone know if access (.adp) has a native method
where I can provide a SQL statement and have the results
written out to a .csv file. I use this as a source of
mailmerge data.

I have tried the Docmd.outputTo method as so:
'DoCmd.OutputTo acOutputStoredProcedure, strSQL,
acFormatXLS, FilePath, False

and this works fine to excel for small datasets, but fails
for larger datasets. (I also really need .csv as excel
will have a maximum row limit)

I can write something to do it, but would prefer to use a
native Access method if one exists.

Thanks in Advance
Chris


.
Best bet is to use DTS in SQL.

 

[Chris]

Hi Van,
I can't see what you mean about the 'Not' being in
the wrong place????

You are probably correct that a view could be used instead
of a table name in SQL Sever .adp . I don't have a view
though, I have a String of SQL, such that I could execute
it to get an ADO recordest. Views exist on the SQL server
and need permissions to create which my users don't have.

Cheers
Chris

 

[Chris]

Hi Al,
Thanks for answering.

I already dismissed DTS.I don't think that DTS will be
dynamic enough for my needs. I would need to give my users
permissions on the SQL box to create DTS packages, and
then look at writing some code using DMO to allow for the
creation of the package at runtime.

I agree DTS is good for producing CSV's, but not
dynamically like I need.

Cheers
Chris

 

[Van T. Dinh]

(just in case you misunderstood: I am NOT an expert in SQL server by any
means; so this is a friendly discussion and what I wrote may be incorrect.)

I was thinking of creating view using code in Access because I think you can
construct the "dynamic" Views in code using "CREATE VIEW ...". I do the
same with constructing "dynamic" Pass-Through Queries often.

(I am possibly wrong on this as I haven't tested the Application Role) but I
thought you may have the user accesses the MS-SQL Server using Windows NT
Authentication and hence unable to create / views due to the user's
permission. When you need to create / modify the View (by code), you
activate the Application Role (temporarily) which allows to create Views due
to the Application Role's permission. After you create the View, you can
de-activate (possible???) the Application Role and the access is reversed
back to the user's permission. So, most of the time, you can tell the users
apart. The only exception is when the VBA code (written and controlled by
YOU) tries to create / modify the View.

Sorry, I got no ideas about the problems with bound Forms but if you are
using A2K, make sure you apply all the Service Releases / Service Packs.

 

[Michael Shao [MSFT]]

Hi Chris,

Below is an example to export data to an excel file using spreadsheet
method with temp table from stored procedure.

Here is the Stored Procedure:
Vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

CREATE PROCEDURE [dbo].[procedure1] AS
select customers.*
into tblDumpit
from customers
GO

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

There is a button in a form with the following code behind it. It includes
a call to an outside sub procedure that refreshes the database window so
the Temp table can be seen by the code:

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

'Runs a stored procedure to create a temp table. Passes
'variables in from ComboBoxes on this form similar to Query By Form
technique.

Dim MySQL As String
MySQL = "Execute Procedure1"

'Runs the Stored Procedure
DoCmd.RunSQL MySQL, True

'This calls a separate subprocedure to refresh the
'Database Window so the TransferSpreadsheet method can find the temp
table.
' See
'Q304256 ACC2000: RefreshDatabaseWindow Method Does Not Work in an
Access
'http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q304256

'Turn echo off to minimize flashing.
DoCmd.Echo False, "Please Hold..."
Call RefreshTheWindow
'Return focus to the current form.
Me.SetFocus
'DoCmd.SelectObject acForm, "Form1"
DoCmd.Echo True


'Now use TransferSpreadsheet to export the new table.
'First kill the existing file on disk.
'If the file doesn't exist, there is an error handler for it.
Kill "C:\Dumpitxxxxr.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblDumpit",
"C:\Dumpitxxxxr.xls"

'Drop the temp table.
DoCmd.RunSQL "Drop Table tblDumpit"

MsgBox "Done!"

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
DoCmd.Echo True

'If the Kill statement fails above, continue.
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 2757 Then
DoCmd.RunSQL "Drop Table tblDumpit"
Resume
Else
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume Exit_Command0_Click
End If


End Sub

Private Sub RefreshTheWindow()
'Requires reference to Office 9.0 object library.
Dim strSQL As String
Dim cBars As Office.CommandBars
Dim cBarCtl As Office.CommandBarControl
'Select the Database Window so you can refresh it.
DoCmd.SelectObject acTable, , True

Set cBars = Application.CommandBars
Set cBarCtl = cBars.FindControl(msoControlButton, 3812)
If Not cBarCtl Is Nothing Then
cBarCtl.Execute
End If

End Sub

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
In the directory "C:\", you could find the excel file Dumpitxxxxr.xls

Hope it helps

Thank you,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Chris]

Hi Michael,
Thanks for your help, this looks great! I will
give it a try.

Cheers
Chris

-----Original Message-----
Hi Chris,

Below is an example to export data to an excel file using spreadsheet
method with temp table from stored procedure.

Here is the Stored Procedure:
Vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

CREATE PROCEDURE [dbo].[procedure1] AS
select customers.*
into tblDumpit
from customers
GO

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^

There is a button in a form with the following code behind it. It includes
a call to an outside sub procedure that refreshes the database window so
the Temp table can be seen by the code:

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv vvv

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

'Runs a stored procedure to create a temp table. Passes
'variables in from ComboBoxes on this form similar to Query By Form
technique.

Dim MySQL As String
MySQL = "Execute Procedure1"

'Runs the Stored Procedure
DoCmd.RunSQL MySQL, True

'This calls a separate subprocedure to refresh the
'Database Window so the TransferSpreadsheet method can find the temp
table.
' See
'Q304256 ACC2000: RefreshDatabaseWindow Method Does Not Work in an
Access
'http://support.microsoft.com/default.aspx?scid=kb;EN- US;Q304256

'Turn echo off to minimize flashing.
DoCmd.Echo False, "Please Hold..."
Call RefreshTheWindow
'Return focus to the current form.
Me.SetFocus
'DoCmd.SelectObject acForm, "Form1"
DoCmd.Echo True


'Now use TransferSpreadsheet to export the new table.
'First kill the existing file on disk.
'If the file doesn't exist, there is an error handler for it.
Kill "C:\Dumpitxxxxr.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblDumpit",
"C:\Dumpitxxxxr.xls"

'Drop the temp table.
DoCmd.RunSQL "Drop Table tblDumpit"

MsgBox "Done!"

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
DoCmd.Echo True

'If the Kill statement fails above, continue.
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 2757 Then
DoCmd.RunSQL "Drop Table tblDumpit"
Resume
Else
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume Exit_Command0_Click
End If


End Sub

Private Sub RefreshTheWindow()
'Requires reference to Office 9.0 object library.
Dim strSQL As String
Dim cBars As Office.CommandBars
Dim cBarCtl As Office.CommandBarControl
'Select the Database Window so you can refresh it.
DoCmd.SelectObject acTable, , True

Set cBars = Application.CommandBars
Set cBarCtl = cBars.FindControl(msoControlButton, 3812)
If Not cBarCtl Is Nothing Then
cBarCtl.Execute
End If

End Sub

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^
In the directory "C:\", you could find the excel file Dumpitxxxxr.xls

Hope it helps

Thank you,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

.

 

[Chris]

Hi Van,
Thanks for sharing your thoughts, expert or not, it
is sometimes helpful to look at a problem from a fresh
perspective.

I don't try to disagree, but I challenge everything until
I get a solution that meets my goals, that's how I can be
sure to get the best solution - Accept no compromise!

I think for a simple solution views would work fine, but
when things get more complex they would not be extensible
enough. Aggregate functions/group by's etc would not be
possible. With a #temp table they are.

Also I believe that #temp tables are created for each user
in SQL Server. This is important because you only want the
user to have access to his/her own temp table, so someone
else isn't altering the data while they work.

The application roles stuff came from Microsoft, so it
must be true I rejected application roles as being too
troublesome based on this.

see: http://support.microsoft.com/default.aspx?scid=kb;en-
us;308312

I don't have difficulty giving users permissions on the
database if I wanted, but I believe it is better from a
security point of view not to give them things they don't
need.

Cheers
Chris