Info from linked TXT file

[Mirica_Victor]

Hy!

I have a linked *.txt file and a qwery that returns the top 1 record from it. (The ideea is to check a value from one column of the file).

Both the linked file/table and the query run corectly, but:
I have a vba code on the on open of a form that is checking the query above mentioned (the form is on the Startup option of the database) and when I need to use the Dcount of the Query "

 DCnt = DCount("Den_Fis", "Data_R_Mod_U$G9707014")

I get the "Runtime error 3625: the text file specification..." doesn't exist etc.

I pres debug and test the query, I get the "Unknown Jet Error".

I press Stop in the VBA and open the Qry - it shows what is was ment to show.

I know the workaround is to make a maketable with the needed info and the Dcount on the local table, but it would add one query and one table to the database. Is this the only way? I am prety shure there is a more... "simple" method.

Anny Ideas?

 

[Guus2005]

You could open the textfile using the

Open strFilename for input as #1

command
and the read the first line.

Dim strTextLine as string
Open strFilename For Input As #1 ' Open file. 
Line Input #1, strTextLine ' Read line into variable. 
Close #1 ' Close file.

Now the first line is stored in strTextLine
You can do whatever you want with it.

HTH

BTW: Use the command Freefile to get a free filedescriptor instead of using #1

 

[vbaInet]

...and since you're using DCount() if you want to count the number of lines in the file, use a Do While loop and increment a Long variable using Guus' code.

 

[Mirica_Victor]

@Guus2005: Probably your ideea works, but it would generate manny vba extra lines.... My workaround generates only 1: the maketable.

@vbaInet: As I mentioned, it is a Select top 1, because if the date in one line is correct, they all are (for one column), so the dcount checks if the 1 record has the correct info. The query has the criteria, and if it is correct returns 1 record, if not it doesn't return annything.

My problem is why the link specifications are not seen at on open?

 

[vbaInet]

If you're returning a value from one of the fields, why are you using DCount? So if all you're doing is checking one field value, there's no benefit in linking to it.

I would imagine the problem is that the macro runs before even the file is re-linked.