Text Files 4

[daybase]

I have two applications both of which use access to some degree and hold pretty much the same information but currently do totally different things. One is an application of my own and the other is a product supplied by a third party. I am looking to combine the databases by linking to their tables and I have successfully linked to their tables but have hit a problem. I am now able to access and process all the basic record details, names, addresses etc but the main details (which I hold in a memo field) are in individual text files named based on record reference p123456.txt etc. Photographs use a smilar system and it is easy to access them by adding a path string and ".jpg" to the reference in a query c:\photos\p123456.jpg etc. Any suggestions please for accessing the contents of the text files in a query (or even in a report)?

 

[genomon]

Take a look at the File System Object & its TextStream object. TYou should be able to parse out the data, insert it into a table, and retrieve it from there.

Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes.

 

[daybase]

Docmd TransferText would appear to do what I want but I am struggling with syntax - am I wasting my time?

 

[TomCologne]

I've just posted some code here: thread68-1409298

Maybe that helps to get you started.

TomCologne

 

[CautionMP]

daybase,
You can return the data from the text files with a simple function (since your filenames are structured). This type of function can be used as a calculated field in a Query or calculated control in Forms and Reports.

It allows you to keep the Memo information in the individual text files (where it probably belongs) without the overhead of having to store it in the database.

Public Function GetTextFromFile(Filename As String) As String
Static intFile As Integer
intFile = FreeFile
Open Filename For Input As #intFile
GetTextFromFile = input(LOF(intFile), #intFile)
Close #intFile
End Function

One warning about using this in a query, you may want to use filters to keep the record count low. The function can be a little slow for big record sets especially if the text files are large.

Hope this helps,
CMP



[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[daybase]

Once again valuable help greatly appreciated

 

[daybase]

I like this text file thing and I am looking at using it myself but I do have a query - I have successfully written data to a text file suitably named, opened amended or outputted as required. A problem I do have is that the contents of the text file have " before and after the content - any suggestions how to avoid this?

Public Function WriteTextToFile(thenewmemofile As String, thenewtxt As String)
Dim MyString, MyString2
Open thenewmemofile For Output As #1
Write #1, thenewpix
Close #1

 

[PHV]

Use Print# instead of Write#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[daybase]

Just discovered the answer - the help file doesnt actually tell you that important point. Great help once again thank you