Output Data to Excel using SQL Server

R

Radar069

We have a database which we need to export some data to excel spreadsheets.
The database has been upsized to SQL and works fine in design mode.
When we run the application we get a run time error.

Can anyone aadvice how to over come this?

Do we need a full version of Access on the server?
 
D

Douglas J Steele

It would help to know the exact error you're getting.

As well, how are you doing the export?
 
R

Radar069

Code used is


Public Sub ExportQuery1()

Dim FileName As String
Dim Result As Variant
Dim Complete, Continue As Boolean

Complete = False
Continue = True
Do While Not Complete And Continue
FileName = InputBox("Note: For security purposes this file will be
written to your personal u: Drive", "Export File Name",
"ForecastPlanningData.xls")
If FileName = "" Then
Continue = False
Else
If StrComp(Right(FileName, 4), ".xls", vbTextCompare) <> 0 Then
FileName = FileName & ".xls"
End If

With Application.FileSearch
.FileName = FileName
.LookIn = "u:\"
If .Execute() > 0 Then
Result = MsgBox("File Already Exists. Do you wish to
overwrite?", vbYesNoCancel + vbDefaultButton1, "Warning")
If Result = vbYes Then
Complete = True
Else
If Result = vbCancel Then
Continue = False
End If
End If
Else
Complete = True
End If
End With
If Complete Then
DoCmd.TransferSpreadsheet acExport, , "ForecastPlanning",
"u:\" & FileName, True
End If
End If
Loop



End Sub



ERROR is

Execution of this application has stopped due to a run-time error.

The application can't continue and will be shut down
 
D

Douglas J Steele

Have you tried single-stepping through the code to see exactly where it's
failing?

I never use the Application.FileSearch: a simple If Len(Dir("u:\" &
FileName)) > 0 is all you need to see whether or not the file exists.
 
R

Radar069

Douglas,

As i said in the original post ALL works OK in design mode it is when we use
a secured application (Cannot design,see tables etc.) it falls over.

I feel this is SQL Server specific not Access.............
 
D

Douglas J Steele

If you know that it's failing on the DoCmd.TransferSpreadsheet, then I'd
probably agree with you that it's SQL Server-related.

Otherwise, there are other possibilities.
 
Top