Trying to export MySql/VBA to csv - cant get it working??

[shabbaranks]

Hi all,



I managed to export to csv using a query but Im trying to tie it all into VBA so users cant run the query. My code thus far is:

    MySql = "SELECT TimesheetTable.ID, TimesheetTable.sUser, UserNames_tbl.WorksNumber, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description, TimesheetTable.Approved "
    MySql = MySql & "FROM UserNames_tbl INNER JOIN TimesheetTable ON UserNames_tbl.sUser = TimesheetTable.sUser"
 
Docmd.OutputTo acOutputFunction, “N:\TimesheetDatabase\” & Format(Date, “ddmmyyyy”) & “.csv", True
End Sub
    Else
        MsgBox "Sorry, you do not have access to this function", _
               vbOKOnly, "Important Information"
        Exit Sub
    End If

but I cant get it workin - any ideas please?

Thanks in advance

[ssanfu]

Hi all,

I managed to export to csv using a query but Im trying to tie it all into VBA so users cant run the query. My code thus far is:

    MySql = "SELECT TimesheetTable.ID, TimesheetTable.sUser, UserNames_tbl.WorksNumber, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description, TimesheetTable.Approved "
    MySql = MySql & "FROM UserNames_tbl INNER JOIN TimesheetTable ON UserNames_tbl.sUser = TimesheetTable.sUser"
 
Docmd.OutputTo acOutputFunction, “N:\TimesheetDatabase\” & Format(Date, “ddmmyyyy”) & “.csv", True
End Sub
    Else
        MsgBox "Sorry, you do not have access to this function", _
               vbOKOnly, "Important Information"
        Exit Sub
    End If

but I cant get it workin - any ideas please?

Thanks in advance

It would be nice to see more of the code, but.........

Looking at what you have provided, it appears that "MySQL" is declared as a type String. You created the SQL string, but you didn't open the recordset. There is nothing to output


Also, I think the syntax is wrong. The syntax is:

DoCmd.OutputTo objecttype[, objectname][, outputformat][, outputfile][, autostart][, templatefile]

For objecttype, I couldn't find a type of "acOutputFunction"
for objectname, there is no table/query name to get the records from
for outputformat , there is no format specified.


Why is "End Sub" after the DoCmd" line?? There should only be ONE "End Sub" in any subroutine. This is different than an "Exit Sub".

[shabbaranks]

WOuld you happen to have any examples of how to achieve this? I think I need to use querydef - what ever that is? Thanks

[orange]

Try google

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

[shabbaranks]

Thanks, its working now. I have been looking at examples and trying to get my head around it - slowly but surely its sinking in.

[ssanfu]

Here is another way: (air code)

Public Sub test_ss()
   Dim rst As DAO.Recordset
   Dim MySQL As String
   Dim SaveToFile As String

   '.
   '.your other code
   '.
   '
   MySQL = "SELECT TimesheetTable.ID, TimesheetTable.sUser, UserNames_tbl.WorksNumber,"
   MySQL = MySQL & " TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project,"
   MySQL = MySQL & " TimesheetTable.[Task Date], TimesheetTable.Description, TimesheetTable.Approved"
   MySQL = MySQL & " FROM UserNames_tbl INNER JOIN TimesheetTable ON UserNames_tbl.sUser = TimesheetTable.sUser"

   'open the recordset
   Set rst = CurrentDb.OpenRecordset(MySQL)
   If rst.BOF And rst.EOF Then
      'no records returned
      MsgBox "Sorry, you do not have access to this function", vbOKOnly, "Important Information"
   Else
      ' create save to file name
      SaveToFile = "N:\TimesheetDatabase\" & Format(Date, “ddmmyyyy”) & ".csv"
      DoCmd.OutputTo acOutputQuery, rst, acFormatTXT, SaveToFile, True

   End If

   'clean up
   rst.Close
   Set rst = Nothing

End Sub