Programming Modules in Access

[Mark Sreeves]

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.

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

[JHausmann]

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...

[Clunietp]

Hi Mark

This code works fine for me in an Access module:

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

[Mark Sreeves]

Thanks for that Clunietp

[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