I’m trying to make a codeunit in Navision look at an acces database holding door entry data. Unfortunately, I cannot get my head around how to get the value of the fields in the access db. The table is called “log”. The field I require is called “name”. The code below complies and runs. I would like to get something like the highlighted code to work, if possible. //‘Microsoft ActiveX Data Objects 2.1 Library’.Connection CREATE(con); //‘Microsoft ActiveX Data Objects 2.1 Library’.Recordset CREATE(rst); db := ‘Door’; // odbc database setup con.Open(db); sel := ‘select * from log’; rst.Open(sel,con,1,1); REPEAT //this is the line I want to work // message(rst.Fields(“NAME”).value) ; rst.MoveNext; UNTIL rst.EOF; rst.Close; con.Close; Any pointers on ADO programming would also be welcome. Thanks Robin
Looking at the statements: … rst.Open(sel,con,1,1); message(rst.Fields(“NAME”).value) ; … I see nothing similar to a “rst.FindFirst” or “rst.MoveFirstRecord” (whatever the correct syntax is). Might that be the problem? Another idea: Instead of reading Access-Data from Navision, would it be a solution if Access is WRITING Data TO Navision? In Access you can access a Navision Table by defining a link to this table via ODBC and therefore read/write within access - and of course - from within Navision. Marcus
Thanks Marcus, Writing from the access database would be a great idea, unfortunately, I have no control over it. I was able to get to the fields as long as they didn’t contain variant types using the ADO field and fields objects. I now have another problem…Access seems to hold date and time in one field, the ADO objects only give me a date… Any Ideas. Robin
Hi all Date and time in Access are same field, but date id int(date), and time decimal part is time. In SQL instruction you can diferncence it and view date and time in Access.
Hi Robin It is required to declare fields type automation variable to get the fields name Try this… declare follwing variable fields automation ‘Microsoft ActiveX Data Objects 2.1 Library’.Fields //‘Microsoft ActiveX Data Objects 2.1 Library’.Connection CREATE(con); //‘Microsoft ActiveX Data Objects 2.1 Library’.Recordset CREATE(rst); db := ‘Door’; // odbc database setup con.Open(db); sel := ‘select * from log’; rst.Open(sel,con,1,1); //modified line <<<<<<< fields := rst.fields() //modified line >>>>>>> REPEAT //this is the line I want to work // message(rst.Fields(“NAME”).value) ; //modified line <<<<<<< MESSAGE(’%1’,fields.Item(‘name’).Value); //modified line >>>>>>> rst.MoveNext; UNTIL rst.EOF; rst.Close; con.Close; By this way will get the value of a particular column from access table. But when I use rst.addnew() the system gives an error message like ‘your provided is not supporting this feature’ Why ? What shall I do for inserting row into access table ? Thanks.
This may have something to do with the level of ODBC supported by the C/ODBC driver. Some features required by ADO are not (yet) supported by the C/ODBC driver. Kind regards, Jan Hoek Weha Automatisering BV Woerden - The Netherlands
quote:
Originally posted by fabian: Looking at the statements: … rst.Open(sel,con,1,1); message(rst.Fields(“NAME”).value) ; … I see nothing similar to a “rst.FindFirst” or “rst.MoveFirstRecord” (whatever the correct syntax is). Might that be the problem?
The code is correct. Since adForwardOnly is the default cursor type, moving to the first record is not even supported. Opening a recordset with this cursor type automatically positions the cursor at the first record (if any). Kind regards, Jan Hoek Weha Automatisering BV Woerden - The Netherlands
quote:
Originally posted by jhoek: Not (yet) supported by the C/ODBC driver.
C/ODBC Conformance level The C/ODBC driver supports all API Core and Level 1 functions. SQLAllocEnv Core Deprecated SQLAllocConnect Core Deprecated SQLAllocStmt Core Deprecated SQLBindCol Core ISO 92 SQLBindParm Core Deprecated SQLCancel Core ISO 92 SQLColAttributesCore ISO 92 SQLColumns Level1 X/Open SQLConnect Core ISO 92 SQLDescribeCol Core ISO 92 SQLDisconnect Core ISO 92 SQLDriverConnectLevel1 ODBC SQLError Core Deprecated SQLExecDirect Core ISO 92 SQLExecute Core ISO 92 SQLFetch Core ISO 92 SQLFreeEnv Core Deprecated SQLFreeConnect Core Deprecated SQLFreeStmt Core ISO 92 SQLGetConnectOption Level1 Deprecated SQLGetData Level1 ISO 92 SQLGetInfo Level1 ISO 92 SQLGetCurserNameCore ISO 92 SQLGetFunctions Level1 ISO 92 SQLGetStmtOptionLevel1 ISO 92 SQLGetTypeInfo Level1 ISO 92 SQLMoreResults Core ODBC SQLNumResultColsCore ISO 92 SQLParamData Level1 ISO 92 SQLPutData Level1 ISO 92 SQLPrepare Core ISO 92 SQLRowCount Core ISO 92 SQLSetConnectOption Level1 Deprecated SQLSetCursorNameCore ISO 92 SQLSetStmtOptionLevel1 Deprecated SQLSpecialColumns Level1 X/Open SQLStatistics Level1 X/Open SQLTables Level1 X/Open SQLTransact Core Deprecated All level descriptions are with reference to Microsoft ODBC version 2.01. Per.Bay@navigera.com Product Manager www.navigera.com
Hi
quote:
sel := ‘select * from log’; rst.Open(sel,con,1,1);
This may only read the data from Access. If we want to write data into Access use rst.Open(sel,con,1,2); Regards. Joseph Mathew