Anyone good with MS Access?

Savatage

Got a question. I have a nav tabel called "Posted Mail Packages" I have an Access Table called "Mail Pkg".

Our mail shipping software fills the Access table with information like "Order Number" & "Tracking Number".
We are not using SQL so A direct link is out.

When I run an Append query it moves the data from Access into Nav. Works fine until you try it the second time. Both tables have "Order Number" & "tracking Number" as their keys. So on the second run Nav throw an error that the records already exists.

So the bottom line question is how do I have Access look to see is the record exists and skip it if it does when trying to insert records using an Append Query?

I've thought about having my access table have an "Imported" field that gets updated to yes once the query is run but I don't know how to do that either ](*,)

All the posts I've read online..I'm having a hard time following.

mrsa

My english is not native english so I don't quite understund you question, but if you must read data from acess and put it in NAV, you can use ADO in NAV like this and manipulate vith data:

Name DataType Subtype
DataConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
DataRecSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
adoFields Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Fields

Code like this:

xDBPath:=
'C:\database path\Database.mdb';
ConStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + xDBPath ;

CREATE(DataConnection);
DataConnection.ConnectionString := ConStr;
DataConnection.Open;


xCmdTxt:=
'SELECT * FROM SOMETABLE'


DataRecSet:=DataConnection.Execute (xCmdTxt);

DataRecSet.MoveFirst;
WHILE NOT(DataRecSet.EOF)
DO
BEGIN
adoFields:=DataRecSet.Fields;
PopulateFields(adoFields,DnevnikRec);
DataRecSet.MoveNext;
END;

PopulateFields function is like:

Var Name DataType Subtype Length
Ne adoFields Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Fields


ForItmesCount:=1;
ItmesCount:=adoFields.Count;
WHILE ForItmesCount<ItmesCount+1 DO
BEGIN
adoField:=adoFields.Item(ForItmesCount-1);
ValueVariant:=adoField.Value;
ValueDec:=0;
ValueInt:=0;
ValueDate:=0D;
ValueText:='';
ValueBoolean:=FALSE;
IF ValueVariant.ISTEXT THEN ValueText:=FORMAT(ValueVariant);
IF ValueVariant.ISDECIMAL THEN ValueDec:=ValueVariant;
IF ValueVariant.ISINTEGER THEN ValueInt:=ValueVariant;
IF ValueVariant.ISDATE THEN ValueDate:=ValueVariant;
IF ValueVariant.ISBOOLEAN THEN ValueBoolean:=ValueVariant;
ForItmesCount+=1;
CASE adoField.Name OF
'FieldName' : BEGIN
someRec.Field:=ValueInt;
END;
END;
END;