Query cannot export

[benkingery]

I have a query drawing its data from two tables. The two tables are related through Item number. One table contains item data, the other contains transactions for individual items on the item table. Here is the SQL:

SELECT "3253" AS 3253, CS_Transactions.Item, Sum([CS_Transactions.QTY]*IIf([Type]='Credit',1,-1)) AS CS_CurrentOnhand, "0" AS [QTY Backordered], "0" AS [QTY On Order], Null AS [Next Available Date], IIf([Master_Child].[CS_Active]=Yes,"0","1") AS Discontinued, Master_Child.CS_ProductName
FROM Master_Child INNER JOIN CS_Transactions ON Master_Child.CS_Child = CS_Transactions.Item
GROUP BY "3253", CS_Transactions.Item, "0", "0", Null, IIf([Master_Child].[CS_Active]=Yes,"0","1"), Master_Child.CS_ProductName;

Ultimately, I need to export this query using this code"

DoCmd.TransferText acExportDelim, "CSNInventory", "CS_CurrentOnhand", "Y:\Rochelle\Inventory Uploads\CSN Stores\" & Format(Now(), "YYYY_MMDDHHMMSS") & "_CSN_Inventory.txt"

The export specs "CSNInventory" spell out that this needs to be exported as a Pipe ("|") delimited file with no text qualifiers.

I am receiving one of two messages. either 1) "Permission Denied" or 2) "Run-time error '3011': The Microsoft Jet database engine could not fin the object '2009_1007163622_CSN_Inventory.txt'. Make sure th eobject exists and that you spell its name and the path name correctly".

When I run the export, the file is actually created and placed in the specified directory, however no data is placed on the exported file. Also, the exported file remains locked until I close the entire access database.

Has anyone ever seen this type of problem? I have 5 other queries that have very similar logic and none of them have the same problem.

Thanks in advance for any help.

 

[DCrake]

You could do this in code using the Open File For Input As #1 method.

First run the query as a recordset then enumerate through the recordset writing each line to the txt file, as such

Dim Rs As DAO.Recordset
Dim strString As String

Set Rs = CurrentDb.OpenRecordset(<<YourQuerySQL>>)

If Not Rs.EOF And Not Rs.BOF Then
    Open "Y:\Rochelle\Inventory Uploads\CSN Stores\" & Format(Now(), "YYYY_MMDDHHMMSS") & "_CSN_Inventory.txt" For Output as #1
    Do Until Rs.EOF
        strString = ""
        For i = 0 To Rs.Fields.Count -1
             strString = strString & "|" & CStr(Nz(Rs(i),""))
        Next
        strString = Mid(strString,2)
        Print #1, strString
        Rs.MoveNext
    Loop
    Rs.Close
End If
Set Rs = Nothing
Close #1

This will first open the recordset, then test for any records, if so it opens the target txt file specified by yourself. Then it goes to the first records and loops through each field found in the query and pipe delimits it, remembering to place a blank string in the place or null fields.

It then drops rthe leading pipe and writes the completed line to the txt file. It then moves on to the next records and repeats until it get to the end of the recordeset. It then closes the txt file and closes the recordset.

This is all aircode and is untested.

David

 

[benkingery]

Certainly seems like a viable solution. Since I'm not extremely VBA savvy, I was hoping for my less complicated solution to work. if I can't get mine to work I'll try this. does anyone else know why my query would have difficulty exporting? Its almost like there is something wrong with the file name. The query works just fine if I display within Access. It will also export just fine to Excel, but it won't export in delimited file formats.

 

[Scooterbug]

Well, if you are getting Permission Denied, check to make sure you have Modify permission for that folder. Since the actual file is created, it's safe to assume that you have Write permissions.

 

[benkingery]

Permissions on file are fine. I checked all of that. I actually have another query similar to this one that uses data from more than 1 table. That query also has the same problem. So, for some reason, I think its on queries that are comprised of more than 1 table. I don't know enough about the intricacies of access to know why this would be a problem upon export.