DJRave


Hey there,

I've been trying to export data from one table to multiple textfiles. This 
should be done based upon 2 fields in this table. I have tried modifying an 
example that I found elsewhere but it returns this error as I run it:

Error '3027' Cannot update. Database or object is read-only.

I want to create a seperate file for every product for each supplier so something has to be added to this script but I'm not sure how.

This is the code that I have sofar:

Function ExportProducts() As Long
   
   Dim db As DAO.Database        
   Dim rs As DAO.Recordset  
   Dim strSql As String         
   Dim strFile As String        
   Dim lngCount As Long         
   Const strcPath = "C:\Export\"    
   Const strcQuery4Export = "Query1"  
   Const strcStub = "SELECT Field1, Field2, Field3, Supplier, Field5, 
Field6, Field7, Field8, Producttype, Field10 " & _
      "FROM table1 WHERE ('Field4' = "
   Const strcTail = ") ORDER BY Field4;"

   Set db = CurrentDb()
   strSql = "SELECT DISTINCT Field4 FROM table1 " & _
      "WHERE Field4 Is Not Null;"
   Set rs = db.OpenRecordset(strSql)

   
   Do While Not rs.EOF
      strSql = strcStub & rs![Field4] & strcTail
      db.QueryDefs(strcQuery4Export).SQL = strSql
      strFile = strcPath & rs![Field] & ".txt"
      DoCmd.TransferText acExportDelim, , strcQuery4Export, "strFile"
      lngCount = lngCount + 1
      rs.MoveNext
   Loop

   rs.Close
   Set rs = Nothing
   Set db = Nothing

   Export = lngCount

End Function

Can anybody help me  Thanks a lot