Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 15th, 2003, 11:22 AM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default Automate Export

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
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
__________________
Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
 
Old October 15th, 2003, 04:03 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 15th, 2003, 04:40 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
 
Old October 15th, 2003, 05:10 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 15th, 2003, 05:21 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Sal,

Much apppreciated :D

-Roni

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
 
Old October 16th, 2003, 08:51 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 16th, 2003, 11:12 AM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks sal,

It worked perfectly. I really appreciate it.

-Roni

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
 
Old October 16th, 2003, 01:45 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
 
Old October 16th, 2003, 02:10 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 16th, 2003, 02:15 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
automate the fields anukagni Access 3 February 12th, 2007 08:45 AM
automate connection wizard olambe VB How-To 2 May 5th, 2005 03:54 PM
How to automate import with notepad timoma Access 20 February 11th, 2005 03:03 PM
Automate Export of Comma-Delineated file > Access? darinsee Access 1 February 10th, 2004 09:57 PM
Automate Queries Ben Access VBA 3 January 29th, 2004 08:50 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.