Sql statements in VB Help Please!!

[pm709]

Hi All:

This is giving me a hard time... What I need is to export multiple queries to a different comma delimited txt files, based in different criterias using VB code with a touch of a button.. But I don't want to create such queries at all (about 185) which is a long trip, even using copy-paste. I understand that this can be done with SQL statements embbeded into the code. This is what I found in Access 2K Help:



DoCmd.TransferText acExportDelim, "Standard Output", _
"External Report", "C:\Txtfiles\April.doc"



Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate >#3-31-96#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)



What I want is to set a different criterias in a new statement for each file exported based only in one unique table.

Can somebody join those two pieces of cake above for me?...I'll appreciate it. Thanks.

 

[Jon K]

The following code assumes there is a CustNum text field in table tblInvoice and it is needed to export a comma delimited text file to C:\My Documents for each CustNum, using the CustNum as file names.

-----------------------------------------
Private Sub Command0_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim SQL As String
Dim FileName As String
Dim Criteria As String
Dim i As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select Distinct CustNum from tblInvoice")

Do While Not rst.EOF
Criteria = rst!CustNum
FileName = "C:\My Documents\" & Criteria & ".txt"

SQL = "SELECT * FROM tblInvoice WHERE CustNum = '" & Criteria & "'"

On Error Resume Next
dbs.QueryDefs.Delete "qTemp"
On Error GoTo 0

Set qdf = dbs.CreateQueryDef("qTemp", SQL)
DoCmd.TransferText acExportDelim, , "qTemp", FileName, True

i = i + 1
rst.MoveNext
Loop

MsgBox "Exported " & i & " files to C:\My Documents"
dbs.QueryDefs.Delete "qTemp"

Set rst = Nothing
Set dbs = Nothing
End Sub
-----------------------------------------

If CustNum is a numeric field, remove the single quotes in the SQL string:-
SQL = "SELECT * FROM tblInvoice WHERE CustNum = " & Criteria


Note. The code is written in Access 97, so DAO is used. When you write this code in Access 2000, you must make a reference to DAO (when the code window is open, choose menu Tools, References..., select the Microsoft DAO 3.6 Object Library from the Available References list).

Hope you can adapt it to suit your needs.

 

[pm709]

Thanks for the code. I made some mods but works fine.