Building a Process in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to build a process in access that reads an
input file containing a string of alphanumeric characters
and writhem to an output file in a readable format.
 
Yes;

See below for example;

Sub TextStreamTestexample()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1,
TristateFalse = 0
Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile "PatronsOut.txt" 'Create
a file
Set f = fs.getfile("C:\Windows\Desktop\Patrons.txt")
Set ts = f.OpenAsTextStream(ForWriting,
TristateUseDefault)
'ts.Close
Set ts = f.OpenAsTextStream(ForReading,
TristateUseDefault)
s = ts.Readline
s = Mid(s, 21, 400)
'ts.Close
ts.Close
End Sub

This one just reads one record and writes on record. Here
is another one the reads the entire file in, but writes it
out to an access table.

Sub PartitionPatronSIFfile()
'This subroutine extracts bytes 21 - 421 from a patron
sif file
'This prepares a sif file for importing into Access
since the original
'SIF file record length is to long for access to import
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1,
TristateFalse = 0
Dim fs1, f, instream, inFile, Inrec
Dim Instring As String

Dim rst As Recordset

Dim BC As String
Dim Group As String
Dim InstID As String
Dim Num991 As String
Dim Last As String
Dim First As String
Dim Middle As String
Dim Name As String

Set fs1 = CreateObject("Scripting.FileSystemObject")
Set inFile = fs1.getfile
("C:\WINDOWS\DESKTOP\Patrons.txt")
Set Inrec = inFile.OpenAsTextStream(ForReading,
TristateUseDefault)
Instring = Inrec.Readline

Set rst = CurrentDb.OpenRecordset("PatronSIF",
dbOpenTable)
rst.AddNew

Do While Inrec.AtEndOfStream <> True
BC = Mid(Instring, 21, 11)
Group = RTrim(Mid(Instring, 46, 10))
InstID = RTrim(Mid(Instring, 239, 30))
Num991 = RTrim(Mid(Instring, 269, 30))
Last = RTrim(Mid(Instring, 311, 30))
First = RTrim(Mid(Instring, 340, 20))
Middle = RTrim(Mid(Instring, 360, 10))
Name = Last & ", " & First & " " & Middle
With rst
.Fields("Name") = Name
.Fields("PatronGroup") = Group
.Fields("InstitutionID") = InstID
.Fields("Barcode") = BC
.Fields("SSAN") = Num991
.Update
.AddNew
End With
Instring = Inrec.Readline
Loop

rst.Close
CurrentDb.Close
Inrec.Close

End Sub

Hope this helps!

Stan
 
See below for example;
Click to expand...

I recommend against using the FileSystemObject, as apparently this uses,
because many System Administrators ensure that the scripting runtime (of
which it is part) is disabled on all machines in their domain. They view it,
primarily, as the vehicle by which many viruses and worms are spread. Some
even include "search and destroy" software in the startup sequence in case
some "rogue application" has reinstalled it.

When it comes to reading and writing, you can do every thing with the
traditional builtin File I/O statements that can be done with the
FileSystemObject.

The following reads a name and address file and stores the data in a table.
It could just as well have used the Print # command to write the data to
another text file. You'll need to make certain the DAO reference is set, if
you use similar code to write to a table in the database.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strLine As String
Dim intFn As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPeople", dbOpenDynaset)
intFn = FreeFile
Open pstrFile For Input Access Read As intFn
Do While EOF(intFn) = False
Line Input #intFn, strLine
rs.AddNew
rs("PersonName") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonStreet") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonCityST") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonZIP") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonPhone") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonFax") = Trim(strLine)
rs.Update
Loop
rs.Close
Close intFn
Set rs = Nothing
Set db = Nothing

Larry Linson
Microsoft Access MVP
 
Back
Top