Accessing Access Data in Navision.

aman_mbs

Dear All,
While accessing the data from Access i have written the following code in Navision i have two problems in the following code

1) when the data is pulled the first character of the first field is always missing like my first field is Empcode then if code is MP0001 then only P0001 is pulled same is for all the rows.

2) I have three fields in the table both in the Access and the navision table When Ever i write the query 'Select EmpCode, EmpName From Employee' the following error is flashed out "Call to the member Item Failed Fields written the following message: Item Cannot be found in the corresponding collection to the requested name." But it wrks properly when i select all the fields of the table.

My Code is

Constr := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Access To Navision\Amantest.mdb;' +
'User Id=admin; Password=;';
StrSql := 'Select EmpCode, EmpName From Employee';

CREATE(varRecordSet);
varRecordSet.Open(StrSql, Constr);
varRecordSet.MoveFirst;
RecordVar.OPEN(50001);
REPEAT
varFldCollection := varRecordSet.Fields;
RecordVar.RESET;
RecordVar.INIT;
FOR i := 1 TO RecordVar.FIELDCOUNT DO BEGIN
FieldVar := RecordVar.FIELDINDEX(i);
FieldVar.VALIDATE(varFldCollection.Item(i-1).Value);
END;
RecordVar.INSERT;
cFlag := 1;
varRecordSet.MoveNext
UNTIL varRecordSet.EOF = TRUE;
IF cFlag = 1 THEN
MESSAGE('Data Imported');
varRecordSet.Close;
RecordVar.CLOSE;
CLEAR(varRecordSet);
CLEAR(RecordVar);

Please Help... #-o

ara3n

have you tried to use connection string Microsoft Access ODBC Driver instead of Microsoft Jet OLE DB 4.0?

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;

ara3n

you could also change the Select statement and add a blank field to the begging of the fields.

aman_mbs

Thanks For the Reply, But i have tried the code u mentioned its not working. And for your second solution that i should create a blank field plz tell me is this a comman problem or my code is creating a problem...

Thanks,

ara3n

No this is not a common problem. This is the first time I've heard of it. ADO works pretty good.

Have you tried to do it in VB and see if you get the same problem?

aman_mbs

Thanks For the quick reply, I appreciate you for giving me the time...
Well that i will check and tell you.. But i am facing a serious problem while importing the Decimal fields it is saying that this data type is not supported by the C/Side is it that we cant pull the decimal values from the Access.. Please don't say no. I need 2 pull the decimal, Integer values from Access how could i do so..

garak

search also before the forum, there are some topics about Access.

viewtopic.php?f=5&t=2407

viewtopic.php?f=23&t=28733 <-- Ui, it's a post from you ......

aman_mbs

While accessing the decimal field the program gives the error Error "the datatype is not supported by C/SIDE You can Access data From anyone of the following datatypes VT_VOID,VT_I2,VT_I4,.....VT_BOOL". I have made the decimal field in Access but cannot pull its data in the Nav.. This error comes when i etract the decimal value....


FieldVar := RecordVar.FIELDINDEX(1);
FieldVar.VALIDATE(varFldCollection.Item(0).Value);
FieldVar := RecordVar.FIELDINDEX(2);
FieldVar.VALIDATE(varFldCollection.Item(1).Value);
FieldVar := RecordVar.FIELDINDEX(3);
FieldVar.VALIDATE(varFldCollection.Item(2).Value);
FieldVar := RecordVar.FIELDINDEX(4);
FieldVar.VALIDATE(varFldCollection.Item(3).Value);
RecordVar.INSERT;


This is really :bug:ing me....

ara3n

For this you need to change your sql statement cast it as float.

Select EmpCode, EmpName From Employee, cast(MyDecimal, as float) ';

aman_mbs

Hi,
I have the table known as Employee with salary as the decimal field so my query should be like this Select EmpCode, EmpName, Salary From Employee Cast(salary, As Float).
I tried this but still not wrking.

ara3n

Try this.

Select EmpCode, EmpName, Cast(salary, As Float) as Salary 
From Employee

aman_mbs

Hi,
I have already tried this but its wroking perfectly in sql server but when i run this command for the access or through my Navision program its showing the error " Syntax error (missing operator) in the query expression CAST(Salary AS Float)"

Thanks

aman_mbs

Hey !
Final its done actually as i am pulling data from access cast command was nt running properly then i used CDbl function in my query it worked

'Select EmpCode, EmpName, Cdbl(Salary), Check From Employee'; \:D/

thanks for the support...

ara3n

that's good to hear.