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

Mark.Sreeves@Softlab.co.uk
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