DELETE info from text file - how ?

[Gina]

I cross posted it in public.access but this is probably the better choice

Hi.

I wrote a programm for a garage to collect customer, car and work
information.
Now data needs to be exchanged between standalone pc's at different
locations.
For ease of use for the customer I decided to use text files for the data
exchange. (saved to floppy)

So I have written export code that writes data to text files (one text file
per table) and the import code adds a new record when the key values are not
found and edits it otherwise.
all works well ....

apart from when a record on pc 1 has been deleted.
so far there is no code at the import to pc2 can handle this ....

question:
how could I receive the information about a non-existing record in the text
file (which gets imported) that the existing record on pc2 should be deleted
?
is there another way than creating temp tables and then compare the existing
and the temp table ?
Is there a way to still use my text based data ? - I feel lost here !

I am fairly new to acces this is my first program .... and I am new to this
file stuff as well
so any help is highly appreciated

Thanks,
Gina

 

[John Nurick]

Hi Gina,

You can use a query like this to delete records from one table that
don't have counterparts in a second table:

DELETE FROM Table1
WHERE Table1.ID NOT IN (
SELECT Table2.ID FROM Table2
)
;

If the second table is a text file, you can use an FROM clause like this
to access the text file without using a temporary table:

FROM [Text;HDR=No;Database=C:\My Folder\;].MyFile#txt

 

[Gina]

John,

Thanks so much for your answer!!
your second suggestion is what I was after
I have one more question to it:

FROM [Text;HDR=No;Database=C:\My Folder\;].MyFile#txt
what does the HDR exactly mean ? ( think it means 'not in')
what does it stand for?

Gina

Hi Gina,

You can use a query like this to delete records from one table that
don't have counterparts in a second table:

DELETE FROM Table1
WHERE Table1.ID NOT IN (
SELECT Table2.ID FROM Table2
)
;

If the second table is a text file, you can use an FROM clause like this
to access the text file without using a temporary table:

FROM [Text;HDR=No;Database=C:\My Folder\;].MyFile#txt

I cross posted it in public.access but this is probably the better choice

Hi.

I wrote a programm for a garage to collect customer, car and work
information.
Now data needs to be exchanged between standalone pc's at different
locations.
For ease of use for the customer I decided to use text files for the data
exchange. (saved to floppy)

So I have written export code that writes data to text files (one text file
per table) and the import code adds a new record when the key values are not
found and edits it otherwise.
all works well ....

apart from when a record on pc 1 has been deleted.
so far there is no code at the import to pc2 can handle this ....

question:
how could I receive the information about a non-existing record in the text
file (which gets imported) that the existing record on pc2 should be deleted
?
is there another way than creating temp tables and then compare the existing
and the temp table ?
Is there a way to still use my text based data ? - I feel lost here !

I am fairly new to acces this is my first program .... and I am new to this
file stuff as well
so any help is highly appreciated

Thanks,
Gina

 

[Gina]

I hit the send button too quickly ...

John,
I read my files as follows
Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" For Input
As #1

what would I have to write instead of 'MyFile#.txt':
- strTablename & ".txt"
- strTablename & "1.txt"
- "1.txt"

Big thanks,
Gina

Hi Gina,

You can use a query like this to delete records from one table that
don't have counterparts in a second table:

DELETE FROM Table1
WHERE Table1.ID NOT IN (
SELECT Table2.ID FROM Table2
)
;

If the second table is a text file, you can use an FROM clause like this
to access the text file without using a temporary table:

FROM [Text;HDR=No;Database=C:\My Folder\;].MyFile#txt

I cross posted it in public.access but this is probably the better choice

Hi.

I wrote a programm for a garage to collect customer, car and work
information.
Now data needs to be exchanged between standalone pc's at different
locations.
For ease of use for the customer I decided to use text files for the data
exchange. (saved to floppy)

So I have written export code that writes data to text files (one text file
per table) and the import code adds a new record when the key values are not
found and edits it otherwise.
all works well ....

apart from when a record on pc 1 has been deleted.
so far there is no code at the import to pc2 can handle this ....

question:
how could I receive the information about a non-existing record in the text
file (which gets imported) that the existing record on pc2 should be deleted
?
is there another way than creating temp tables and then compare the existing
and the temp table ?
Is there a way to still use my text based data ? - I feel lost here !

I am fairly new to acces this is my first program .... and I am new to this
file stuff as well
so any help is highly appreciated

Thanks,
Gina

 

[Gina]

John,

I get a compiler error 'external name not defined'
and following line is highlighted:

--> [Text;HDR=No;Database= CurrentProject.Path & "\Trans\";]

Here's what I have got so far -
maybe you could have a look at it - I don't know how to grab this text thing
.....

_____

Public Sub AfterImport()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim arWords As Long

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"

Set db = CurrentDb

Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" For Input
As #1

Do While Not EOF(1)

Line Input #1, Line
arWords = Split(Line, "|")
keyFieldText = " & arWords(0) & "

strSQL = "DELETE FROM " & strTablename & " WHERE " & strTablename &
"." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & " FROM " &
[Text;HDR=No;Database= CurrentProject.Path & "\Trans\";] & strTablename &
".txt"

db.Execute (strSQL)
Loop
Close #1

Set db = Nothing
End Sub

_____

Thanks for your help,
Gina

Hi Gina,

You can use a query like this to delete records from one table that
don't have counterparts in a second table:

DELETE FROM Table1
WHERE Table1.ID NOT IN (
SELECT Table2.ID FROM Table2
)
;

If the second table is a text file, you can use an FROM clause like this
to access the text file without using a temporary table:

FROM [Text;HDR=No;Database=C:\My Folder\;].MyFile#txt

I cross posted it in public.access but this is probably the better choice

Hi.

I wrote a programm for a garage to collect customer, car and work
information.
Now data needs to be exchanged between standalone pc's at different
locations.
For ease of use for the customer I decided to use text files for the data
exchange. (saved to floppy)

So I have written export code that writes data to text files (one text file
per table) and the import code adds a new record when the key values are not
found and edits it otherwise.
all works well ....

apart from when a record on pc 1 has been deleted.
so far there is no code at the import to pc2 can handle this ....

question:
how could I receive the information about a non-existing record in the text
file (which gets imported) that the existing record on pc2 should be deleted
?
is there another way than creating temp tables and then compare the existing
and the temp table ?
Is there a way to still use my text based data ? - I feel lost here !

I am fairly new to acces this is my first program .... and I am new to this
file stuff as well
so any help is highly appreciated

Thanks,
Gina

 

[Nikos Yannacopoulos]

Gina,

My $0.02's worth: instead of actually deleting records, you could just
add a Deleted (Yes/No) field, and check it instead of deleting; filter
your "working" records for Deleted = false. The Deleted field (actually
marked for deletion) will propagate from one copy of the db to the
other(s) just like any other field, and will also give you an easier way
to reverse an accidental "deletion".
You can periodically delete (actually!) marked records on all copies of
the .mdb if required.
To set the record straight, I'm not re-inventing the wheel here, I'm
merely 'stealing' the idea from the way ERP's handle "deletions" (so
users are never allowed to actually delete a record, under any
circumstances).

HTH,
Nikos

 

[Gina]

Hi Nikos,

well ... yes ... I think you are right ... re the deletions and users ...
but I don't have time anymore ....
I just wanted to handle a deleted record and thought that there must be a
way other than temp tables and Not In query.

Your marking records for deletion is a clever & elegant way .... but I am
afraid I have to find an easy solution without having to amend all tables
and queries and file stuff ....

well .... maybe you can give me a hint on my re-inventing the wheel code ;-)
.... I won't re-invent it in the future ... that's for sure
I get a message from JET telling me that the file

'DELETE FROM ArbeitsDetails WHERE ArbeitsDetails.Detail_ID NOT IN (SELECT 4
FROM [Text;HDR=No;Database=C:\DB\Trans\].ArbeitsDetails.txt);'

in the meantime it executes .... with the result so far .... it deleted
simply all the records in the table ArbeitsDetails ....
oh dear .... oh dear ...

Thanks for any idea ....
Gina
___

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"
keyFieldText = 0

Set db = CurrentDb

Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" For Input
As #1
extFolder = CurrentProject.Path & "\Trans\Del"
extFile = strTablename & ".txt"


'Do While Not EOF(1)

Input #1, Line
arWords = Split(Line, "|")

keyFieldText = arWords(0)
strSQL = "DELETE FROM " & strTablename & " WHERE " & strTablename &
"." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & " FROM
[Text;HDR=No;Database=" & extFolder & "]." & extFile & ");"
Debug.Print strSQL
DoCmd.RunSQL (strSQL)

'Loop
Close #1


Set db = Nothing


___

 

[Nikos Yannacopoulos]

Hi Gina,

lok for my comments below.

Regards,
Nikos

Hi Nikos,

well ... yes ... I think you are right ... re the deletions and users ...
but I don't have time anymore ....
I just wanted to handle a deleted record and thought that there must be a
way other than temp tables and Not In query.

Your marking records for deletion is a clever & elegant way ....

Well, I 'stole' it!

but I am
afraid I have to find an easy solution without having to amend all tables
and queries and file stuff ....

I didn't expect you to, just threw an idea on the table for future
occasions.

well .... maybe you can give me a hint on my re-inventing the wheel code ;-)
... I won't re-invent it in the future ... that's for sure
I get a message from JET telling me that the file

'DELETE FROM ArbeitsDetails WHERE ArbeitsDetails.Detail_ID NOT IN (SELECT 4
FROM [Text;HDR=No;Database=C:\DB\Trans\].ArbeitsDetails.txt);'
in the meantime it executes .... with the result so far .... it deleted
simply all the records in the table ArbeitsDetails ....
oh dear .... oh dear ...

I suspect the problem here is the subquery returns no records for some
reason, therefore all records in the table are deleted!
My best guess is: wrong field selectipn in the subquery SELECT clause;
if you ar eindeed trying to select the 4th field then it should be
SELECT F4 FROM etc. not SELECT 4 FROM etc. Try to change your strSQL
code to:

strSQL = "DELETE FROM " & strTablename & " WHERE " & strTablename _
& "." & keyFieldTable & " NOT IN (SELECT F" & keyFieldText _
& " FROM [Text;HDR=No;Database=" & extFolder & "]." & extFile & ");"

If the aim was not to select the 4th field in the text fiel, then check
the value assignment to keyFieldText, as there is something obviously
wrong there.

Thanks for any idea ....
Gina
___

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"
keyFieldText = 0

Set db = CurrentDb

Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" For Input
As #1
extFolder = CurrentProject.Path & "\Trans\Del"
extFile = strTablename & ".txt"


'Do While Not EOF(1)

Input #1, Line
arWords = Split(Line, "|")

keyFieldText = arWords(0)
strSQL = "DELETE FROM " & strTablename & " WHERE " & strTablename &
"." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & " FROM
[Text;HDR=No;Database=" & extFolder & "]." & extFile & ");"
Debug.Print strSQL
DoCmd.RunSQL (strSQL)

'Loop
Close #1


Set db = Nothing


___

Gina,

My $0.02's worth: instead of actually deleting records, you could just
add a Deleted (Yes/No) field, and check it instead of deleting; filter
your "working" records for Deleted = false. The Deleted field (actually
marked for deletion) will propagate from one copy of the db to the
other(s) just like any other field, and will also give you an easier way
to reverse an accidental "deletion".
You can periodically delete (actually!) marked records on all copies of
the .mdb if required.
To set the record straight, I'm not re-inventing the wheel here, I'm
merely 'stealing' the idea from the way ERP's handle "deletions" (so
users are never allowed to actually delete a record, under any
circumstances).

HTH,
Nikos

choice

data

file

not

text

deleted

existing

this

 

[Nikos Yannacopoulos]

If the aim was not to select the 4th field in the text fiel, then check
the value assignment to keyFieldText, as there is something obviously
wrong there.

Silly me... of course the aim was to select the 4th field! There is no
header in the file, so you couldn't possibly select by field name, could
you?

 

[Gina]

Nikos...

that is going to make me somehow mad ... I can feel it !!!!!!

I am reading (or selecting the first field of the textfile separated by '|'
-> arWords = Split(Line, "|")

then I read it into
-> keyFieldText = arWords(0) ' 0 for first column

on debug.print keyFieldText ... i can see it run through all the 642 records
..... pooh ....

I deleted record with ID 643 on another machine .... exported it ....
imported it into my pc .....

well what it does is .. stopping the loop after record 642 ....
.... not do I have to loop thrtough the text file ... I have to go further as
the text file has records .... I have to somehow loop through the table
I am missing here something and cannot figure out what .....

Lots of thanks for your help .... I highly appreciate it !!!

Gina
____
Public Sub AfterImport()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, strSQL_SEL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim arWords
Dim extFile, extFolder As String
Dim fso As Object

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"
keyFieldText = 0

Set db = CurrentDb
Set fso = CreateObject("Scripting.FileSystemObject")

Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" For Input
As #1
extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"


Do While Not EOF(1)

Line Input #1, Line
arWords = Split(Line, "|")

keyFieldText = arWords(0)
strSQL_SEL = "SELECT " & strTablename & "." & keyFieldTable & " FROM
" & strTablename & " WHERE " & strTablename & "." & keyFieldTable & " NOT IN
(SELECT " & keyFieldText & " FROM [Text;HDR=No;Database=" & extFolder & "]."
& extFile & ");"
Set rst = db.OpenRecordset(strSQL_SEL)
Debug.Print keyFieldText
If rst.RecordCount <= 0 Then
MsgBox keyFieldTable
strSQL = "DELETE FROM " & strTablename & " WHERE " &
strTablename & "." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & "
FROM [Text;HDR=No;Database=" & extFolder & "]." & extFile & ");"
Debug.Assert rst.RecordCount <= 0
Debug.Print strSQL
db.Execute (strSQL)

End If


Loop
Close #1


Set db = Nothing

'FROM [Text;HDR=No;Database= " & CurrentProject.Path &
"\Trans\";].MyFile#.txt
End Sub


____

 

[Gina]

Nikos,
what I am trying to do is get the 1st field (or column) of each line of the
text file (assign it to keyFieldText) and to compare it with the 1st field
in the table (keyFieldTable)
which works - somehow -
..... but when the file is at the last line it stops .... while there is
still a record with value '643' in keyFieldTable in the table and this one
should now be [ please] deleted .....
but this most important bit doesn't want to work ....

Gina

 

[Nikos Yannacopoulos]

Gina,

Are you confused, or am I? Maybe we both are. I was too preoccupied with
the tree and failed to see forest in my previous reply. John's
suggestion involved running the query *once*, while you are doing this
inside a loop! I'm not sure what you are trying to achieve with the
loop; is it all just in order to delete those records?

At any rate, the DELETE query should only run once, but note: it will
only work if the link to the text file works correctly with a
pipe-delimited file - which I doubt (never tried it). If it does work
fine; your code should be something like:

Public Sub AfterImport()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim extFile, extFolder As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"

extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN "
& "(SELECT F1 FROM [Text;HDR=No;Database=" & extFolder _
& "]." & extFile & ");"
Debug.Print strSQL 'optional
CurrentDbdb.Execute (strSQL)

End Sub


In the event Access fails to read the pipe-delimited text file properly,
you will need domething like:

Public Sub AfterImport()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim arWords
Dim extFile, extFolder As String
Dim vIn As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"


Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" _
For Input As #1
extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
vInClause = vInClause & arWords(0) & ","
Loop
vInClause = Left(vInClause, Len(vInClause)-1)
Close #1
Debug.Print vInClause
strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN (" _
& vInClause & ")"
Debug.Print strSQL
CurrentDb.Execute (strSQL)

End Sub

The trick in the code above is that it reads the PK values in the text
file one by one in puts them in vInClause like 1,2,3 etc so they are
used in the subquery. Note that the code assumes numeric values; if they
are text then the loop should be changed to:

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
vInClause = vInClause & "'" arWords(0) & "',"
Loop
and then vInClause must be truncated by two characters at the end:
vInClause = Left(vInClause, Len(vInClause)-2)

HTH,
Nikos

Do While Not EOF(1)

Line Input #1, Line
arWords = Split(Line, "|")

keyFieldText = arWords(0)
strSQL_SEL = "SELECT " & strTablename & "." & keyFieldTable & " FROM
" & strTablename & " WHERE " & strTablename & "." & keyFieldTable & " NOT IN
(SELECT " & keyFieldText & " FROM [Text;HDR=No;Database=" & extFolder & "]."
& extFile & ");"
Set rst = db.OpenRecordset(strSQL_SEL)
Debug.Print keyFieldText
If rst.RecordCount <= 0 Then
MsgBox keyFieldTable
strSQL = "DELETE FROM " & strTablename & " WHERE " &
strTablename & "." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & "
FROM [Text;HDR=No;Database=" & extFolder & "]." & extFile & ");"
Debug.Assert rst.RecordCount <= 0
Debug.Print strSQL
db.Execute (strSQL)

End If


Loop
Close #1


Set db = Nothing

'FROM [Text;HDR=No;Database= " & CurrentProject.Path &
"\Trans\";].MyFile#.txt
End Sub


____

Silly me... of course the aim was to select the 4th field! There is no
header in the file, so you couldn't possibly select by field name, could
you?

 

[Gina]

ok .... Nikos, yes .... running it once that makes real sense ... after all
my digging in the ground with bare hands!!!

but how can I assign the value from the first column of the text file to the
sub query ????

Line Input #1, Line
arWords = Split(Line, "|")
keyFieldText = arWords(0)

you suggested F1 and I don't know how to puzzle it together !!????

anyhow to show you how grateful I am for your support ... from now on ...
the AfterImport sub is renamed as follows ;-) ...
(and it will remain in my code!!!!! - that I promise)


Gina
______
Public Sub AfterImport_Nikos()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim extFile, extFolder As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"

extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN " _
& "(SELECT F1 FROM [Text;HDR=No;Database=" & extFolder _
& "]." & extFile & ");"
Debug.Print strSQL
CurrentDbdb.Execute (strSQL)

End Sub

Gina,

Are you confused, or am I? Maybe we both are. I was too preoccupied with
the tree and failed to see forest in my previous reply. John's
suggestion involved running the query *once*, while you are doing this
inside a loop! I'm not sure what you are trying to achieve with the
loop; is it all just in order to delete those records?

At any rate, the DELETE query should only run once, but note: it will
only work if the link to the text file works correctly with a
pipe-delimited file - which I doubt (never tried it). If it does work
fine; your code should be something like:

Public Sub AfterImport()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim extFile, extFolder As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"

extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN "
& "(SELECT F1 FROM [Text;HDR=No;Database=" & extFolder _
& "]." & extFile & ");"
Debug.Print strSQL 'optional
CurrentDbdb.Execute (strSQL)

End Sub


In the event Access fails to read the pipe-delimited text file properly,
you will need domething like:

Public Sub AfterImport()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim arWords
Dim extFile, extFolder As String
Dim vIn As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"


Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" _
For Input As #1
extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
vInClause = vInClause & arWords(0) & ","
Loop
vInClause = Left(vInClause, Len(vInClause)-1)
Close #1
Debug.Print vInClause
strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN (" _
& vInClause & ")"
Debug.Print strSQL
CurrentDb.Execute (strSQL)

End Sub

The trick in the code above is that it reads the PK values in the text
file one by one in puts them in vInClause like 1,2,3 etc so they are
used in the subquery. Note that the code assumes numeric values; if they
are text then the loop should be changed to:

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
vInClause = vInClause & "'" arWords(0) & "',"
Loop
and then vInClause must be truncated by two characters at the end:
vInClause = Left(vInClause, Len(vInClause)-2)

HTH,
Nikos

Do While Not EOF(1)

Line Input #1, Line
arWords = Split(Line, "|")

keyFieldText = arWords(0)
strSQL_SEL = "SELECT " & strTablename & "." & keyFieldTable & " FROM
" & strTablename & " WHERE " & strTablename & "." & keyFieldTable & " NOT IN
(SELECT " & keyFieldText & " FROM [Text;HDR=No;Database=" & extFolder & "]."
& extFile & ");"
Set rst = db.OpenRecordset(strSQL_SEL)
Debug.Print keyFieldText
If rst.RecordCount <= 0 Then
MsgBox keyFieldTable
strSQL = "DELETE FROM " & strTablename & " WHERE " &
strTablename & "." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & "
FROM [Text;HDR=No;Database=" & extFolder & "]." & extFile & ");"
Debug.Assert rst.RecordCount <= 0
Debug.Print strSQL
db.Execute (strSQL)

End If


Loop
Close #1


Set db = Nothing

'FROM [Text;HDR=No;Database= " & CurrentProject.Path &
"\Trans\";].MyFile#.txt
End Sub


____

If the aim was not to select the 4th field in the text fiel, then check
the value assignment to keyFieldText, as there is something obviously
wrong there.

Silly me... of course the aim was to select the 4th field! There is no
header in the file, so you couldn't possibly select by field name, could
you?

 

[Gina]

Nikos,

Think I make a major mistake in how I read the file .... I read it line by
line ... and while doing so ... after the first line is read it delets all
records except the one currently read from file

hours ago I had this kind of problem where all the records were gone ....
how can I read the first column of a file all at once not line by line (is
this at all possibel ????)
...... maybe I should go and use just a temp table ... and then run a query
..... but in a way .... there is the data in the text file ... it can/ should
be compared etc

but maybe I am to silly to achieve it.

_______________________________
Public Sub AfterImport_Nikos()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText, txtFile, arWords
Dim extFile, extFolder As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"

extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" For
Input As #1
Input #1, txtFile
arWords = Split(txtFile, "|")

keyFieldText = arWords(0)
Debug.Print keyFieldText

strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN " _
& "(SELECT " & keyFieldText & " FROM [Text;HDR=No;Database=" &
extFolder _
& "]." & extFile & ");"
Debug.Print strSQL
CurrentDb.Execute (strSQL)

End Sub

Gina,

Are you confused, or am I? Maybe we both are. I was too preoccupied with
the tree and failed to see forest in my previous reply. John's
suggestion involved running the query *once*, while you are doing this
inside a loop! I'm not sure what you are trying to achieve with the
loop; is it all just in order to delete those records?

At any rate, the DELETE query should only run once, but note: it will
only work if the link to the text file works correctly with a
pipe-delimited file - which I doubt (never tried it). If it does work
fine; your code should be something like:

Public Sub AfterImport()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim extFile, extFolder As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"

extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN "
& "(SELECT F1 FROM [Text;HDR=No;Database=" & extFolder _
& "]." & extFile & ");"
Debug.Print strSQL 'optional
CurrentDbdb.Execute (strSQL)

End Sub


In the event Access fails to read the pipe-delimited text file properly,
you will need domething like:

Public Sub AfterImport()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim arWords
Dim extFile, extFolder As String
Dim vIn As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"


Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" _
For Input As #1
extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
vInClause = vInClause & arWords(0) & ","
Loop
vInClause = Left(vInClause, Len(vInClause)-1)
Close #1
Debug.Print vInClause
strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN (" _
& vInClause & ")"
Debug.Print strSQL
CurrentDb.Execute (strSQL)

End Sub

The trick in the code above is that it reads the PK values in the text
file one by one in puts them in vInClause like 1,2,3 etc so they are
used in the subquery. Note that the code assumes numeric values; if they
are text then the loop should be changed to:

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
vInClause = vInClause & "'" arWords(0) & "',"
Loop
and then vInClause must be truncated by two characters at the end:
vInClause = Left(vInClause, Len(vInClause)-2)

HTH,
Nikos

Do While Not EOF(1)

Line Input #1, Line
arWords = Split(Line, "|")

keyFieldText = arWords(0)
strSQL_SEL = "SELECT " & strTablename & "." & keyFieldTable & " FROM
" & strTablename & " WHERE " & strTablename & "." & keyFieldTable & " NOT IN
(SELECT " & keyFieldText & " FROM [Text;HDR=No;Database=" & extFolder & "]."
& extFile & ");"
Set rst = db.OpenRecordset(strSQL_SEL)
Debug.Print keyFieldText
If rst.RecordCount <= 0 Then
MsgBox keyFieldTable
strSQL = "DELETE FROM " & strTablename & " WHERE " &
strTablename & "." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & "
FROM [Text;HDR=No;Database=" & extFolder & "]." & extFile & ");"
Debug.Assert rst.RecordCount <= 0
Debug.Print strSQL
db.Execute (strSQL)

End If


Loop
Close #1


Set db = Nothing

'FROM [Text;HDR=No;Database= " & CurrentProject.Path &
"\Trans\";].MyFile#.txt
End Sub


____

If the aim was not to select the 4th field in the text fiel, then check
the value assignment to keyFieldText, as there is something obviously
wrong there.

Silly me... of course the aim was to select the 4th field! There is no
header in the file, so you couldn't possibly select by field name, could
you?

 

[Gina]

Nikos .... wooooouuuuw

it works .... it works .... finally !!!!!! with the vInClause!!!!!
Big fat thanks from germany
to express my gratefulness!!!
AfterImport_Nikos()

Gina )

Gina,

Are you confused, or am I? Maybe we both are. I was too preoccupied with
the tree and failed to see forest in my previous reply. John's
suggestion involved running the query *once*, while you are doing this
inside a loop! I'm not sure what you are trying to achieve with the
loop; is it all just in order to delete those records?

At any rate, the DELETE query should only run once, but note: it will
only work if the link to the text file works correctly with a
pipe-delimited file - which I doubt (never tried it). If it does work
fine; your code should be something like:

Public Sub AfterImport()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim extFile, extFolder As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"

extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN "
& "(SELECT F1 FROM [Text;HDR=No;Database=" & extFolder _
& "]." & extFile & ");"
Debug.Print strSQL 'optional
CurrentDbdb.Execute (strSQL)

End Sub


In the event Access fails to read the pipe-delimited text file properly,
you will need domething like:

Public Sub AfterImport()
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim arWords
Dim extFile, extFolder As String
Dim vIn As String

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"


Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" _
For Input As #1
extFolder = CurrentProject.Path & "\Trans\Del\"
extFile = strTablename & ".txt"

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
vInClause = vInClause & arWords(0) & ","
Loop
vInClause = Left(vInClause, Len(vInClause)-1)
Close #1
Debug.Print vInClause
strSQL = "DELETE FROM " & strTablename & " WHERE " _
& strTablename & "." & keyFieldTable & " NOT IN (" _
& vInClause & ")"
Debug.Print strSQL
CurrentDb.Execute (strSQL)

End Sub

The trick in the code above is that it reads the PK values in the text
file one by one in puts them in vInClause like 1,2,3 etc so they are
used in the subquery. Note that the code assumes numeric values; if they
are text then the loop should be changed to:

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
vInClause = vInClause & "'" arWords(0) & "',"
Loop
and then vInClause must be truncated by two characters at the end:
vInClause = Left(vInClause, Len(vInClause)-2)

HTH,
Nikos

Do While Not EOF(1)

Line Input #1, Line
arWords = Split(Line, "|")

keyFieldText = arWords(0)
strSQL_SEL = "SELECT " & strTablename & "." & keyFieldTable & " FROM
" & strTablename & " WHERE " & strTablename & "." & keyFieldTable & " NOT IN
(SELECT " & keyFieldText & " FROM [Text;HDR=No;Database=" & extFolder & "]."
& extFile & ");"
Set rst = db.OpenRecordset(strSQL_SEL)
Debug.Print keyFieldText
If rst.RecordCount <= 0 Then
MsgBox keyFieldTable
strSQL = "DELETE FROM " & strTablename & " WHERE " &
strTablename & "." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & "
FROM [Text;HDR=No;Database=" & extFolder & "]." & extFile & ");"
Debug.Assert rst.RecordCount <= 0
Debug.Print strSQL
db.Execute (strSQL)

End If


Loop
Close #1


Set db = Nothing

'FROM [Text;HDR=No;Database= " & CurrentProject.Path &
"\Trans\";].MyFile#.txt
End Sub


____

If the aim was not to select the 4th field in the text fiel, then check
the value assignment to keyFieldText, as there is something obviously
wrong there.

Silly me... of course the aim was to select the 4th field! There is no
header in the file, so you couldn't possibly select by field name, could
you?

 

[John Nurick]

Hi Gina,

The HDR argument controls whether the Jet database engine assumes that
the first row of the text file does not contain field names. If it does
contain the names, use HDR=Yes.

You neeed to end up with something like this in the SQL string. The [ ]
around MyFile are needed if there is a possiblity that the file name
will contain a space:

FROM [Text;HDR=No;Database=C:\My Folder\;].[MyFile]#txt

So it would be something like this,

strSQL = blah
strSQL = strSQL & vbCrLf & "FROM [TEXT;HDR=Yes;Database=" _
& CurrentProject.Path & "\Trans\;].[" _
& strFileName & "]#" & strFileExt _
...
where strFileName contains the name of the file WITHOUT the extension,
e.g. if the file is
My Text File.txt
then you have to write code that strips off the ".txt" to leave just
My Text File
.. Likewise, strFileExt must contain the extension without the ".", i.e.
just
txt

John,

I get a compiler error 'external name not defined'
and following line is highlighted:

--> [Text;HDR=No;Database= CurrentProject.Path & "\Trans\";]

Here's what I have got so far -
maybe you could have a look at it - I don't know how to grab this text thing
....

_____

Public Sub AfterImport()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, strTablename, Line As String
Dim keyFieldTable, keyFieldText
Dim arWords As Long

strTablename = "ArbeitsDetails"
keyFieldTable = "Detail_ID"

Set db = CurrentDb

Open CurrentProject.Path & "\Trans\" & strTablename & ".txt" For Input
As #1

Do While Not EOF(1)

Line Input #1, Line
arWords = Split(Line, "|")
keyFieldText = " & arWords(0) & "

strSQL = "DELETE FROM " & strTablename & " WHERE " & strTablename &
"." & keyFieldTable & " NOT IN (SELECT " & keyFieldText & " FROM " &
[Text;HDR=No;Database= CurrentProject.Path & "\Trans\";] & strTablename &
".txt"

db.Execute (strSQL)
Loop
Close #1

Set db = Nothing
End Sub

_____

Thanks for your help,
Gina

Hi Gina,

You can use a query like this to delete records from one table that
don't have counterparts in a second table:

DELETE FROM Table1
WHERE Table1.ID NOT IN (
SELECT Table2.ID FROM Table2
)
;

If the second table is a text file, you can use an FROM clause like this
to access the text file without using a temporary table:

FROM [Text;HDR=No;Database=C:\My Folder\;].MyFile#txt

I cross posted it in public.access but this is probably the better choice

Hi.

I wrote a programm for a garage to collect customer, car and work
information.
Now data needs to be exchanged between standalone pc's at different
locations.
For ease of use for the customer I decided to use text files for the data
exchange. (saved to floppy)

So I have written export code that writes data to text files (one text file
per table) and the import code adds a new record when the key values are not
found and edits it otherwise.
all works well ....

apart from when a record on pc 1 has been deleted.
so far there is no code at the import to pc2 can handle this ....

question:
how could I receive the information about a non-existing record in the text
file (which gets imported) that the existing record on pc2 should be deleted
?
is there another way than creating temp tables and then compare the existing
and the temp table ?
Is there a way to still use my text based data ? - I feel lost here !

I am fairly new to acces this is my first program .... and I am new to this
file stuff as well
so any help is highly appreciated

Thanks,
Gina

 

[Nikos Yannacopoulos]

you suggested F1 and I don't know how to puzzle it together !!????

You have HDR=No which means there is no first line with field names
(Header) in the text file, therefore when connecting to the text file
Access assigns field names like F1, F2 etc. thus F1 for the first
field... though I'm afraid the link won't work with a pipe-delimited
text file.

anyhow to show you how grateful I am for your support ... from now on ...
the AfterImport sub is renamed as follows ;-) ...
(and it will remain in my code!!!!! - that I promise)

I'm honoured!

 

[Nikos Yannacopoulos]

Think I make a major mistake in how I read the file .... I read it line by
line ... and while doing so ... after the first line is read it delets all
records except the one currently read from file

Exactly! And then when it gets to the second record in the file, it
deletes the only one left in the table in the first iteration... the
rest 641 (or whatever iterations) are redundant, you have already
deleted all records in the table! It's the loop, it should not be done
in a loop.

..... maybe I should go and use just a temp table ...

That would certainly be an option, though not necessarily the best.

 

[Nikos Yannacopoulos]

GOOD! Is the trick clear to you? The whole point is you can't read a
text file like an Access table, you have to read line by line - unless
you link it, of course, which is an option we didn't consider at all!

Regards,
Nikos