Update / Delete linked text file data 
Author Message
 Update / Delete linked text file data

Again, apologies for my lengthy explanation...
Using Access 2000:
I have a monthly process set up that uses data from linked
text files.  The text files are leads lists that, during
the process, get modified and appended to existing tables
(dependent on which campaign they are for).  The text
files are replaced when there is new data, but not every
campaign has new data every month, so if there is no new
data, I don't want the existing records reappended
(duplicated).
Right now I am manually running the process only for the
campaigns with new data.  However, I need to pass this off
to less experienced users and it needs to be as simplified
as possible...
I can't just "unmatch" and add the new records because one
household can receive several mailings within a single
campaign, and there is no unique identifier with the leads
(I build a matchkey for the purpose of linking to sales,
but it is still not always unique).
Access doesn't allow deletion or modification of data in
linked text files, but I need to have the data deleted so
that the process can still run; there just won't be any
records to append if there are no new leads, which works
fine.
I'm planning to resort to programming the code to open the
text file in Word, select all, and deleting, then saving
and closing the document as a text file, but I was hoping
someone would have a more efficient solution...
PS: importing the data instead of linking won't resolve
this because the text file has to exist for the process to
work, and again, the data might already be in the compiled
lead table.  
Thanks in advance for any thoughts!


Sun, 17 Apr 2005 02:58:01 GMT  
 Update / Delete linked text file data
What we do in situations like this is track the date/time of each file that
has been processed. then before we import the data we look at the date/time
of the file and compare it to the last time we processed this file. If the
date/time is different, then we know we are dealing with a new file and we
process it. Otherwise we skip it.

Another way is to establish a history folder for these files. After you
process a file, then move it to the history folder. then we new files arrive
in the regular folder, you know they are new. Again after you process these,
move them to the history folder.

-------------------------------------------------------------

Scott N. Weber, MCP
www.AccessYourBiz.Com
Customizable Accounting Software Written in Microsoft Access
Weber Systems Inc.
Microsoft Certified Partner


Quote:
> Again, apologies for my lengthy explanation...
> Using Access 2000:
> I have a monthly process set up that uses data from linked
> text files.  The text files are leads lists that, during
> the process, get modified and appended to existing tables
> (dependent on which campaign they are for).  The text
> files are replaced when there is new data, but not every
> campaign has new data every month, so if there is no new
> data, I don't want the existing records reappended
> (duplicated).
> Right now I am manually running the process only for the
> campaigns with new data.  However, I need to pass this off
> to less experienced users and it needs to be as simplified
> as possible...
> I can't just "unmatch" and add the new records because one
> household can receive several mailings within a single
> campaign, and there is no unique identifier with the leads
> (I build a matchkey for the purpose of linking to sales,
> but it is still not always unique).
> Access doesn't allow deletion or modification of data in
> linked text files, but I need to have the data deleted so
> that the process can still run; there just won't be any
> records to append if there are no new leads, which works
> fine.
> I'm planning to resort to programming the code to open the
> text file in Word, select all, and deleting, then saving
> and closing the document as a text file, but I was hoping
> someone would have a more efficient solution...
> PS: importing the data instead of linking won't resolve
> this because the text file has to exist for the process to
> work, and again, the data might already be in the compiled
> lead table.
> Thanks in advance for any thoughts!



Sun, 17 Apr 2005 04:03:30 GMT  
 Update / Delete linked text file data
Hi, Scott-
Thanks for the info.  I'm basically already doing your
initial suggestion manually, by skipping the import
process if it's already been imported.  Do you have a way
of doing this with code?  If so, would you be willing to
share it with me?
Bottom line is, I don't want the user to have to deal with
the text files, other than saving the updated data as that
file (which is linked to the dbs).  For example, we
receive, via diskette, a file named 'ORNBM10apr.txt'
(naming conventions are not always consistent).  That file
would be saved in a folder and renamed 'AutoX.txt'.  
When we receive the diskette the following month with the
text file named 'ORNBM10apr.txt' (for example), we would
again save that file as 'AutoX.txt', which would allow the
linked table to stay intact at all times (moving the text
files to a history folder would prevent this).  
Saving the text file is the extent of what I want the user
to be dealing with.  After that, they would go into the
Access db and click a button which builds a temporary
table, makes the needed data updates, and appends the new
leads to the existing lead table, then deletes the data in
the temporary table.  So, I'm looking for a way to also
delete the data in the linked table, or exclude it if it's
already been processed to the existing lead table.  

Does that help to clarify?
Thanks again!

Quote:
>-----Original Message-----
>What we do in situations like this is track the date/time
of each file that
>has been processed. then before we import the data we

look at the date/time
Quote:
>of the file and compare it to the last time we processed
this file. If the
>date/time is different, then we know we are dealing with
a new file and we
>process it. Otherwise we skip it.

>Another way is to establish a history folder for these
files. After you
>process a file, then move it to the history folder. then
we new files arrive
>in the regular folder, you know they are new. Again after
you process these,
>move them to the history folder.

>----------------------------------------------------------
---

>Scott N. Weber, MCP
>www.AccessYourBiz.Com
>Customizable Accounting Software Written in Microsoft
Access
>Weber Systems Inc.
>Microsoft Certified Partner



>> Again, apologies for my lengthy explanation...
>> Using Access 2000:
>> I have a monthly process set up that uses data from
linked
>> text files.  The text files are leads lists that, during
>> the process, get modified and appended to existing
tables
>> (dependent on which campaign they are for).  The text
>> files are replaced when there is new data, but not every
>> campaign has new data every month, so if there is no new
>> data, I don't want the existing records reappended
>> (duplicated).
>> Right now I am manually running the process only for the
>> campaigns with new data.  However, I need to pass this
off
>> to less experienced users and it needs to be as
simplified
>> as possible...
>> I can't just "unmatch" and add the new records because
one
>> household can receive several mailings within a single
>> campaign, and there is no unique identifier with the
leads
>> (I build a matchkey for the purpose of linking to sales,
>> but it is still not always unique).
>> Access doesn't allow deletion or modification of data in
>> linked text files, but I need to have the data deleted
so
>> that the process can still run; there just won't be any
>> records to append if there are no new leads, which works
>> fine.
>> I'm planning to resort to programming the code to open
the
>> text file in Word, select all, and deleting, then saving
>> and closing the document as a text file, but I was
hoping
>> someone would have a more efficient solution...
>> PS: importing the data instead of linking won't resolve
>> this because the text file has to exist for the process
to
>> work, and again, the data might already be in the
compiled
>> lead table.
>> Thanks in advance for any thoughts!

>.



Sun, 17 Apr 2005 05:43:17 GMT  
 Update / Delete linked text file data
Here is some code we used to handle a similar situation with csv files. Good
luck, let me know if it helps.

Function DownloadCSV()

    Dim dbs As Database
    Dim rstSys As Recordset
    Dim rst As Recordset
    Dim rstMaster As Recordset

    Dim strSearchChar As String
    Dim strPartNum As String
    Dim strField As String

    Dim lngPos As Long
    Dim lngPartNumLen As Long
    Dim lngPartNumPos As Long

    Dim strSearchFile As String
    Dim strFolder As String
    Dim strFullPath As String
    Dim strFileName As String
    Dim strArchive As String

    Dim blnProcessEnd As Boolean

    Set dbs = CurrentDb()
    Set rstSys = dbs.OpenRecordset("tblSystemMaster", dbOpenSnapshot)

    strFolder = rstSys![DataFolder]
    strArchive = rstSys![ArchiveFolder]
    strSearchFile = Dir(strFolder & "*.csv")

    blnProcessEnd = False

ProcessFiles:
    ' Search the specified directory for any files that have a .csv
extension.
    Do While strSearchFile <> ""
        strSearchFile = Dir(strFolder & "*.csv")

        If strSearchFile <> "" Then
            strFileName = Left(strSearchFile, Len(strSearchFile) - 4)
            strFullPath = strFolder & strSearchFile

            ' Link the .csv file to the database using the TextFiles import
specifications.
            DoCmd.TransferText acLinkFixed, "TextFiles", strFileName,
strFullPath, False

            Set rst = dbs.OpenRecordset(strFileName, dbOpenSnapshot)
            Set rstMaster = dbs.OpenRecordset("tblInventoryMaster",
dbOpenDynaset)

            strSearchChar = ","

            ' Loop through the entire Text file and search for Part Numbers
that match Part Numbers
            ' in the tblInventoryMaster table. If the Part Number is found
that add the Part Number from the
            ' Text file to the Inventort Master table in the PartNum field.
All the other informatio that is
            ' in the record before and after the Part Number will be added
to the Info field.
            ' On each record search for the first instance of the search
character. In this example the search character
            ' is a ";". When the first instance of this character is found
then take the left of the string minus the character.
            ' This is the first string of text that will search for in the
Inventory Master table. Then the process will truncate
            ' this text from the string and find the next search character.
This process will loop until the end of the record.
            ' Example - Type;PartNum;Manufacturuer;Qty
            ' Each piece of text of the example will be compared to the
Inventory Master items.
            With rst
                .MoveFirst

                Do Until .EOF
                    If Not IsNull(![Field1]) Then

                        lngPos = 1
                        strField = ![Field1]

                        Do Until lngPos = 0
                            lngPos = InStr(1, strField, strSearchChar)

                            ' When at the end of the string just set the
strPartNum variable to whatever is left over.
                            If lngPos = 0 Then
                                strPartNum = strField
                            Else
                                strPartNum = Left(strField, lngPos - 1)
                                strField = Mid(strField, lngPos + 1)
                                'lngPos = lngPos + 1
                            End If

                            ' Open the Inventory Master table and check to
see if this item exists. If so then add
                            ' the item in a two column format. The Part
Number is appended to the PartNum field and
                            ' all the information before and after the Part
Number in the record is appended to the Info field.
                            With rstMaster
                                .FindFirst "[PartNum]='" & strPartNum & "'"
                                ' If there is a match then add the part
number and info to the table.
                                If Not .NoMatch Then
                                    lngPartNumPos = InStr(1, rst![Field1],
strPartNum)
                                    lngPartNumLen = Len(strPartNum)

                                    .AddNew
                                    ![PartNum] = strPartNum
                                    ' Add only 255 characters to the info
field because it is of Text format and can only hold 255 characters.
                                    ![Info] = Left(Left(rst![Field1],
lngPartNumPos - 1) & Mid(rst![Field1], lngPartNumPos + lngPartNumLen + 1),
255)
                                    .Update

                                'Else
                                '    .AddNew
                                '    ![PartNum] = strPartNum
                                '    .Update
                                End If

                            End With

                        Loop
                    End If
                    .MoveNext
                Loop
            End With

            rst.Close
            rstMaster.Close

            ' Move the Text file to the Archive folder so the loop can
finish.
            Call MoveFile(strFullPath, strArchive & strSearchFile)
        End If
    Loop

    ' Check to see if both the Data and Email folders were searched.
    ' If so then exit the function else move onto searching the Email
folder.
    If blnProcessEnd = True Then
        Exit Function
    End If

    ' Search the Email folder for any CSV files.
    strFolder = rstSys![EmailFolder]
    strArchive = rstSys![EmailArchive]
    strSearchFile = Dir(strFolder & "*.csv")
    blnProcessEnd = True
    GoTo ProcessFiles

End Function

Function MoveFile(strFullPath, strHistoryPath)
' Moves the Data file to the History Directory.

    Dim fs, f
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(strFullPath)
    f.Copy strHistoryPath, True
    f.Delete

End Function


Quote:
> Hi, Scott-
> Thanks for the info.  I'm basically already doing your
> initial suggestion manually, by skipping the import
> process if it's already been imported.  Do you have a way
> of doing this with code?  If so, would you be willing to
> share it with me?
> Bottom line is, I don't want the user to have to deal with
> the text files, other than saving the updated data as that
> file (which is linked to the dbs).  For example, we
> receive, via diskette, a file named 'ORNBM10apr.txt'
> (naming conventions are not always consistent).  That file
> would be saved in a folder and renamed 'AutoX.txt'.
> When we receive the diskette the following month with the
> text file named 'ORNBM10apr.txt' (for example), we would
> again save that file as 'AutoX.txt', which would allow the
> linked table to stay intact at all times (moving the text
> files to a history folder would prevent this).
> Saving the text file is the extent of what I want the user
> to be dealing with.  After that, they would go into the
> Access db and click a button which builds a temporary
> table, makes the needed data updates, and appends the new
> leads to the existing lead table, then deletes the data in
> the temporary table.  So, I'm looking for a way to also
> delete the data in the linked table, or exclude it if it's
> already been processed to the existing lead table.

> Does that help to clarify?
> Thanks again!
> >-----Original Message-----
> >What we do in situations like this is track the date/time
> of each file that
> >has been processed. then before we import the data we
> look at the date/time
> >of the file and compare it to the last time we processed
> this file. If the
> >date/time is different, then we know we are dealing with
> a new file and we
> >process it. Otherwise we skip it.

> >Another way is to establish a history folder for these
> files. After you
> >process a file, then move it to the history folder. then
> we new files arrive
> >in the regular folder, you know they are new. Again after
> you process these,
> >move them to the history folder.

> >----------------------------------------------------------
> ---

> >Scott N. Weber, MCP
> >www.AccessYourBiz.Com
> >Customizable Accounting Software Written in Microsoft
> Access
> >Weber Systems Inc.
> >Microsoft Certified Partner



> >> Again, apologies for my lengthy explanation...
> >> Using Access 2000:
> >> I have a monthly process set up that uses data from
> linked
> >> text files.  The text files are leads lists that, during
> >> the process, get modified and appended to existing
> tables
> >> (dependent on which campaign they are for).  The text
> >> files are replaced when there is new data, but not every
> >> campaign has new data every month, so if there is no new
> >> data, I don't want the existing records reappended
> >> (duplicated).
> >> Right now I am manually running the process only for the
> >> campaigns with new data.  However, I need to pass this
> off
> >> to less experienced users and it needs to be as
> simplified
> >> as possible...
> >> I can't just "unmatch" and add the new records because
> one
> >> household can receive several mailings within a single
> >> campaign, and there is no unique identifier with the
> leads
> >> (I build a matchkey for the purpose of linking to sales,
> >> but it is still not always unique).
> >> Access doesn't allow deletion or modification of data in
> >> linked text files, but I need to have the data deleted
> so
> >> that the process can still run; there just won't be any
> >> records to append if there are no new leads, which works
> >> fine.
> >> I'm planning to resort to programming the code to open
> the
> >> text file in Word, select all, and deleting, then saving
> >> and closing the document as a text file, but I

...

read more »



Sun, 17 Apr 2005 06:30:30 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Help: updating text file-Updating Data in a linked table not supported by this ISAM

2. Deleted records when text file is linked as table (#deleted)

3. Vb 4.0 Problems updating Text Boxes from Combo Box linked to Data Control

4. Stop link update in linked source file

5. Text file linked to data aware controls?

6. Updating Links in Word 2002, Edit, Links, Select Update Method

7. Updating a userproperty with data in a text file

8. Updating Data in a Self Joined Many-to-Many Data Structure (Previous Post Omitted Text)

9. Deleting text from a range in a text file

10. Data Adapter Configuration Wizard does not generate Update and Delete

11. how i can use the dbgrid with database {edit ,addnew,update or delete the data}

12. How do you ADD, UPDATE, and Delete a record in a recordset using Data Environment Designer

 

 
Powered by phpBB® Forum Software