I have been trying unsuccessfully to automate an export file from a query. The current manual export process consists of the following steps.

1. Run a saved Query
2. Use the file export and save the file as a CSV file with the name always as
    the date, for example “15-OCT-2003.csv”. The export details are always a
    comma delineated file and the text delimiters are set to none (no quotes)
3. Then split the file smaller files where each smaller file is a 499 line chunk
    of the larger file with the names with an appended letter denoting the file
    order for example the first would be: “15-Oct-03-a.csv” then “15-Oct-03-b.csv”
    etc…
4. Add a row of header text which is always the same text at the top of each
    file making each file 500 lines long.

It can be a tedious process and it seems like something that the DB should be able to produce. Any help or advice on this would be much appreciated.

-Roni


Roni Estein
[email protected]
https://www.e-drugsCanada.com

__________________
Roni Estein
[email protected]
https://www.e-drugsCanada.com

How many records in the query?
You may be able to write to a file from an ADO recordset. Do you know how to use ADO and some VB/VBA to write to a file?

It is worth the effort.


Sal

Hi Sal,

I have been trying to do that with some VBA yet I cannot get past the syntax for the export and file setup as dumb as that is. I found some information on a couple of websites but they don't seem to be correct.

If you happen to know a correct website or code to test:
1. if a file exists
2. create a text file that can be written to
3. close and save the file

That would be great.
-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

tO CHECK IF A FILE EXISTS
object.FileExists(filespec)
file spec is the name of the file, path and file name

I have some code at home for this. I will check tonight



Sal

Thanks Sal,

Much apppreciated :D

-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

Dim strRecord as String

open "Text1.csv" For Output As #1 " instead of "Text1.csv" you can use a string value of yor date
Do until rs.EOF
    strRecord = rs.Fields("Field1").Value & "," & rs.Fields("Field2").Value
    Print #1, strRecord
    rs.MoveNext
Loop
Close #1 'saves the file

put all this in a loop for each file and use a string value from the date to replace "Text1.csv"

Sal

Thanks sal,

It worked perfectly. I really appreciate it.

-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

OOPS,

Apparently I'm wrong.
I applied the file output to a test case which went fine.
Some how I am getting the weirdest error now:

Run-time error '-2147467259 (80004005)'
Method 'Open' of object'_Recordset' failed

It's so bizarre becuase there is a saved query which runs fine. Can one access the recordset of a saved query the same way you can access a live form?

[Queries]![QueryName].RecordSet or RecordSetClone?

It is using absolutely identical SQL as the saved query.
Perhaps I should post this in a new thread or is that cross or multi posting or some other religious taboo?

Here is the exact code from the sub
Sub XXXXXXX_export()
   Dim header As String
   Dim sSQL As String
   'sSQL = "SELECT Simplicity.drug_Ptr, Simplicity.pack_ptr, Simplicity.din, DrugName.brand, DrugName.chem, DrugName.american, " _
        & "calcStr([Drug.hasComplexStrength],nz([Drug.strengthMagnitude]),nz([Drug.strengthForm]),nz([Drug.stringStrength])) AS stre, " _
        & "Simplicity.size, Drug.formUnits, po([prescription]) AS rx, Simplicity.pOrderNumber, Simplicity.price1, " _
        & "regNum([sPrice]) AS sellingPrice, Drug.generic, Simplicity.csize " _
        & "FROM DrugName INNER JOIN (Simplicity INNER JOIN Drug ON Simplicity.pOrderNumber = Drug.pONum) ON DrugName.drugNameID = Drug.nameID;"
   sSQL = "SELECT Simplicity.drug_Ptr, Simplicity.pack_ptr, Simplicity.din, DrugName.brand, DrugName.chem, DrugName.american, calcStr([Drug.hasComplexStrength],nz([Drug.strengthMagnitude]),nz([Drug.strengthForm]),nz([Drug.stringStrength])) AS stre, Simplicity.size, Drug.formUnits, po([prescription]) AS rx, Simplicity.pOrderNumber, Simplicity.price1, regNum([sPrice]) AS sellingPrice, Drug.generic, Simplicity.csize FROM DrugName INNER JOIN (Simplicity INNER JOIN Drug ON Simplicity.pOrderNumber = Drug.pONum) ON DrugName.drugNameID = Drug.nameID;"
   Debug.Print sSQL
   header = "drug_ptr,pack_ptr,DIN,name1,name2,name3,strength, packSize,form,P/O,orderNumber,AAC,sellingPrice,generic,supplier size"

   Dim cn As ADODB.Connection
   Set cn = CurrentProject.Connection

   Dim rs As New ADODB.Recordset
   'open the greenridge export query
   rs.Open sSQL, cn, adOpenForwardOnly, adLockOptimistic



   'if there is no problems with it loop through the recordset and create a new file name for each 499 line block
   If Not rs.EOF Then

      Dim fileName As String
      Dim filePath As String
      Dim suffix As Byte
      Dim strRecord As String

      suffix = 96
      filePath = "C:\data\projects\edrugsCanada\priceUpdates\"
      fileName = Date

      rs.MoveFirst
      Do Until rs.EOF
         suffix = suffix + 1 ' set to a for initial file
         Open (filePath & fileName & "-" & Chr(suffix)) For Output As #1
         Print #1, header
         Dim i As Integer

         For i = 0 To (498 Or rs.EOF)
            Print #1, rs![drug_ptr] & "," & rs![pack_ptr] & "," & rs![din] & "," & rs![brand] & "," & rs![chem] & "," & rs![american] _
                      & "," & rs![stre] & "," & rs![Size] & "," & rs![formUnits] & "," & rs![rx] & "," & rs![pOrderNumber] & "," & rs![price1] _
                      & "," & rs![sellingPrice] & "," & rs![generic] & "," & rs![csize]
            rs.MoveNext
         Next
         Close #1
      Loop
   End If
End Sub

Sorry it doesn't come out so clean but I didn't wat to tamper with it

-Roni


Roni Estein
[email protected]
https://www.e-drugsCanada.com

Save your SQL code as a query and use the query as you would use a table for the recordset.

By the way, you have the top sSQL variable assignment commented on the first line, but it is formated to span several lines, Did yoy use the incorrect one this time around?

Anyway, use a query instead to ease troublewhooting.

Sal

Make sure you close your recordset at the end of your sub to ensure that you are not trying to double open it. Set it to nothing.

Sal