Programming Modules in Access

OK, I need some guidance here.

I wrote a VB frontend to an Access database part of which writes data to files from the database.

I am now trying to put that code into the database itself. Can someone pleas etell me how to change this so it works.

Code:

---

Public Function WriteDIS() As Long
Dim strSQL As String
Dim rstTemp As Recordset
Dim FNum As Integer

On Error GoTo ErrHandler


FNum = FreeFile

'DIS_FILE is a const declared elsewhere
'eg c:\dis.txt
Open DIS_FILE For Output As FNum

'''S10 type
strSQL = "SELECT * FROM tblSupplier;"
Set rstTemp = db.OpenRecordset(strSQL)

While Not rstTemp.EOF
  Print #FNum, "S10|" & rstTemp!SupplierCode & "|" & rstTemp!CompanyName
  rstTemp.MoveNext
Wend
rstTemp.Close

'''''S3 type
'strSQL = "SELECT tblSupplierTP.SupplierCode, tblSupplierTP.TPCode, tblSupplier.ContactName" & _
'" FROM tblSupplierTP INNER JOIN tblSupplier ON tblSupplierTP.SupplierCode = tblSupplier.SupplierCode;"

'this has been altered to cater for contact names at buisness level
strSQL = "SELECT * FROM tblSupplierTP;"

Set rstTemp = db.OpenRecordset(strSQL)

While Not rstTemp.EOF
  Print #FNum, "S3|" & rstTemp!SupplierCode & "DIS" & rstTemp!TpCode & "|" & rstTemp!ContactName
  rstTemp.MoveNext
Wend
rstTemp.Close


'''S30 type
strSQL = "SELECT * FROM tblSupplierTP;"
Set rstTemp = db.OpenRecordset(strSQL)

While Not rstTemp.EOF
  Print #FNum, "S30|" & rstTemp!SupplierCode & "DIS" & rstTemp!TpCode & "|" & rstTemp!ccode & Trim0(rstTemp!acode) & rstTemp!FaxNo
  rstTemp.MoveNext
Wend
rstTemp.Close

WriteDIS = 0

WriteExit:
  Close #FNum
Exit Function
ErrHandler:
  WriteDIS = Err.Number
  GoTo WriteExit
Close #FNum

End Function

---

Many thanks!

------------------
Mark Sreeves
Analyst Programmer

[email protected]
A BMW Group Company

I don't know that you can. You'll need a component/library that doesn't exist in Access (and I can't find a way to add one like you can in VB).

I could be wrong...

Hi Mark

This code works fine for me in an Access module:

Code:

---

Private Sub DoSomething()
   
    Dim db As Database
    Dim rs As Recordset
   
    Set db = Access.CurrentDb
   
    Set rs = db.OpenRecordset("Select * from Customers")
   
    Open "C:\test.dat" For Output As #1
   
    Do Until rs.EOF = True
        Write #1, , rs.Fields("CUSTOMERID").Value
        rs.MoveNext
    Loop
   
    Close #1
   
    rs.Close
    Set rs = Nothing
   
    db.Close
    Set db = Nothing
   
End Sub

---

Thanks for that Clunietp

No problem Mark

be sure to check out your other post too, you'll need both pieces of the puzzle...
http://www.vb-world.net/ubb/Forum3/HTML/002809.html