Hello. Is it possible in Attain to read/modify/delete records from a table in an Access-DB ? Perhaps with automation controller ? If yes, can someone give me a short example of the program code ? Thanks Martin
don’t think it will work without automation controlls…
You can use ADO or the older DAO, depending on the platform you work with. ADO is pretty easy and based on SQL-statements, which means that you have to open a connection, find your record or recordset and modify it. ex. Select * from Customer where Customer_ID = ‘1000’ or delete * from Customer where Customer_ID = ‘20000’ As a matter of fact ADO works great as an automation. I’ve done several integrations to Access. The best thing is that you directly can convert the hole stuff to connect and replicate with SQL-server too. Here is a codeexemple: IF ReplTableSetup.“Access Table Name” = ‘’ THEN EXIT; IF NOT DatabaseConnectet THEN ConManager.ConnectDatabase(Conn,DatabaseConnectet); IF ISCLEAR(RS) THEN CREATE(RS); Window.UPDATE(1,‘updating webdatabase’); Window.UPDATE(2,ReplTableSetup.“Table No.”); ReplFieldSetup.SETRANGE(“Table ID”,ReplTableSetup.“Table No.”); ReplFieldSetup.SETRANGE(Replicate,TRUE); RecRef.OPEN(ReplTableSetup.“Table No.”); IF RecRef.FIND(’-’) THEN REPEAT SQL := ‘’; SQL1 := ‘’; ManyFields := FALSE; RecID := RecRef.RECORDID; IF NOT InetReplLog.GET(RecID) THEN BEGIN Window.UPDATE(3,RecID); IF ReplFieldSetup.FIND(’-’) THEN REPEAT FRef := RecRef.FIELD(ReplFieldSetup.“Field No.”); Fields.SETRANGE(TableNo,ReplFieldSetup.“Table ID”); Fields.SETRANGE(“No.”,ReplFieldSetup.“Field No.”); IF NOT Fields.FIND(’-’) THEN BEGIN ConManager.CloseDatabase(Conn,DatabaseConnectet); IF NOT ISCLEAR(Conn) THEN CLEAR(Conn); IF NOT ISCLEAR(RS) THEN CLEAR(RS); IF NOT ISCLEAR(Field) THEN CLEAR(Field); IF NOT ISCLEAR(ADOStream) THEN CLEAR(ADOStream); ERROR(Text003,ReplFieldSetup.“Field No.”,ReplFieldSetup.“Table ID”,ReplFieldSetup.TABLECAPTION); END; IF ManyFields THEN BEGIN SQL := SQL + ', '; SQL1 := SQL1 + ', '; END ELSE BEGIN InetReplLog.RecordID := RecID; InetReplLog.“Last date modified” := TODAY; InetReplLog.INSERT; END; SQL := SQL + ReplFieldSetup.“Access FieldName”; SQL1 := SQL1 + FormatContent(Fields,FRef); ManyFields := TRUE; UNTIL ReplFieldSetup.NEXT = 0; IF (SQL <> ‘’) AND (SQL1 <> ‘’) THEN Conn.Execute(‘INSERT INTO ’ + ReplTableSetup.“Access Table Name” + ’ (’ + SQL + ‘) VALUES (’ + SQL1 + ‘)’); COMMIT; END; UNTIL RecRef.NEXT = 0; RecRef.CLOSE; CLEAR(RecRef); CLEAR(FRef); ---------------------------------------------------------------- have fun… if you want som objects, feel free to send me a message - I’ll send you more then…
Thanks guys, i think i found a solution.
If you are on SQL, then that is a much easier solution. Just set up a SQ view to the Access database, then Create the table in Navision, and then just treat your access database as a part of Navision. Just be aware of having a propper backup procedure defined.