import problem 1

[fule12]

Hi All,

I create spec. to import text file in Access database.This import working ok if i import new record in db. But if i try to import record who already exist in table I get error. :
"Microsoft Access wos unable to append all data to a table.
The contents of fields in 0 record(s) were deleted,and 2 record(s) were lost due to key violations......
So what can be solution ?

Thanks

Fule

 

[jwhjr]

That is the reason for the primary key, to not allow duplicate data. If this is not the intention, you need to reexamine your table structure.

JR

 

[fule12]

ok yes I have PK in table with No Duplicate option,and some time will happen that i receive record in text file wich exist in table.So how to compare record from text file vs records in table and if record exist in table to change old one with new from text file ?

Thanks

Fule

 

[jwhjr]

Fule,
It sounds like your primary key needs to change if there are some fields that can change while others (your current PK) stay the same. You may need to add more fields to your PK in order to make the records unique. Can you send the table structure and what the PK or PK's are?

JR

 

[MakeItSo]

To me this sounds like a code issue:
You should write a module for the import which compares each record from the text file with existing ones, updates if found, else adds new to the table.

Something like
[blue]
Dim a as integer, str as String, rs as recordset, txt()

a=Freefile
Open "full path to text file" for input as a
set rs = currentdb.openrecordset("your table", dbopensnapshot)
Do while not EOF(a)
Line Input #a, str
txt=split(str,chr(9))
rs.findfirst "ID=" & txt(0)
if rs.nomatch
rs.edit
rs.addnew
assign all text fields to the respective table fields (cycle to the txt-array, which contains all fields separated)
rs.update
else
rs.edit
rs!field1=txt(1)
overwrite all old fields with new values
rs.update
end if
rs.close
close a
[/blue]
Hope, you're a bit familiar with vba. I can give assistance, if you wish.

Good luck,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[fule12]

thanks jwhjr and MakeItSo for your replay.
MakeIiSo your solution is what i need.
If my Text file look like this :
--------------- Start ------------------------------
"FRN-0013","FRN","Reefer","Ice Fern",13,12/27/2003 0:00:00,1,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,1,1,11,114,12/10/2003 0:00:00,12/25/2003 0:00:00,2/9/2004 0:00:00,12/26/2003 0:00:00,,,,,"Port",12/10/2003 0:00:00,,"The MPM planned maintenance system has not been backed up to preserve data entered.",,,,,,,,,,,,,,,,,
"FRN-0006","FRN","Reefer","Ice Fern",6,12/27/2003 0:00:00,1,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,1,1,11,122,12/10/2003 0:00:00,12/25/2003 0:00:00,2/9/2003 0:00:00,12/24/2003 0:00:00,,,,,"Port",12/10/2003 0:00:00,"Procedures relating to the maintenance of ISM critical equipment have not been fully implement.","Jobcards relating to critical equipment have not been updated in line with DFM requirement.","Import DFM issued Critical Equipment jobcards when re-sent to the ship.Implement and follow requirements recording inspections and test in MPM system.",,,,,,,,,,,,,,,,

------------------------------- end -------------
FRN-0006 and FRN-0013 are PK.
So now about VBA Code.I'm not so good with VBA Code still learning.

So my table have 78 field. How to map fields in text file with fields in Table ?

Thanks

Fule

 

[MakeItSo]

78 fields???? WOW!
Definitely sounds like your table could need some normalization. ;-)

So, what I need from you is either
- table name and field names
or
- a db containing a (empty) table with your structure, mailed to the address below.

Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[MakeItSo]

Bingo! Seems to run smoothly ;-)

For all those interested - here's the import code.
Note: I have an additional Shell function "BrowseForFolder" to get the import path.
Required references: DAO, Microsoft Shell Automation

[blue]
Sub TxtImport()
Dim a As Integer, i As Integer
Dim str As String, pat As String, fil As String
Dim rs As Recordset, txt As Variant
txt = Array(80)
a = FreeFile
Select Case MsgBox("Before you select the import folder" _
& vbCrLf & "make sure it only contains one file." _
, vbOKCancel Or vbExclamation Or vbSystemModal Or vbDefaultButton1, "Only one report file!&quot
Case vbCancel
Exit Sub
End Select

pat = BrowseForFolder("Specify import folder&quot & "\"
fil = pat & "Report*.txt"
[green]'Dir - function allows opening files with wildcards...[/green]
fil = pat & Dir(fil)
Open fil For Input As a
Set rs = CurrentDb.OpenRecordset("tblCombined_Report", dbOpenDynaset)
rs.LockEdits = False

Do While Not EOF(a)
Line Input #a, str
[green]'Strip text file from "[/green]
str = Replace(str, Chr(34), "&quot
[green]'Split file stream into array[/green]
txt = Split(str, ",&quot
[green]'Search for ID[/green]
rs.findfirst "Report_ID='" & txt(0) & "'"
If rs.NoMatch Then
rs.Edit
rs.AddNew
[green]' assign all text fields to the respective table fields (cycle to the txt-array, which contains all fields separated)[/green]
For i = 0 To UBound(txt)
If txt(i) <> &quot;&quot; Then rs.Fields(i) = txt(i)
Next
rs.Update
Else
rs.Edit
[green] ' overwrite all old fields with new values[/green]
For i = 0 To UBound(txt)
If txt(i) <> &quot;&quot; Then rs.Fields(i) = txt(i)
Next
rs.Update
End If
Loop
rs.Close
Close a
Call MsgBox(&quot;Import finished!&quot;, vbInformation Or vbSystemModal Or vbDefaultButton1, &quot;Success!&quot
End Sub

[/blue]

Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[fule12]

Thanks Andreas, you got STAR und danke schoen :

Fule