G
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
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