Exporting data to multiple textfiles

[Guest]

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 by Allen Browne that I found elsewhere on this page 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.

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 debug this script? Thanks a lot

 

[Alex Dybenko]

Hi,
if you get error here:
db.QueryDefs(strcQuery4Export).SQL = strSql

then try to use 2 queries for export, first use Query1, then Query2, then
Query1 again.

DoCmd.TransferText "locks" query for some time, this is perhaps a reason for
error

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com