I am trying to connect to an Access database from NAV to insert new records into the access database whenever a new record is added to a particular table. I am trying to use Microsoft Access 12.0 Object Library to do this.
I cant just figure it out. Please if anyone has ever done it, pls help me. I have tried using Jet driver as the automation server, but it cant just insert the records.
Please also (do not be angry at a newbe), should i use a codeunit or a report to do this?
Please help me, as this is very :bug: ing me!
Thank you all in advance. Waiting eagerly for your help.
Melton
0
Comments
Thanks once again for the help.
Melton
Please look at ADO. You can look at waldo post in how to use ado to connect to access database and insert data from nav.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
viewtopic.php?f=23&t=32375&hilit=Select+cast
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
create new table type Link
use the wizard to link to the Navision table.
once the table is linked, you can then create a query with the table.
make it an append query, and append the records to your MS Access table.
this is the easy part. since it is a standard query.
the tricky part is: you have to have something in Navision that marks a record as already appended so you don't just keep appending records over and over again. so you will need another query for that. This query is an update query, it uses your MSAccess table that you appended the records to in the first step. the query then can be used to update a field in Navision with a marker, I used a field in Navision I created called "already appended"
if it has a yes, then my first query ignors it. if it is blank, but I have a record in my second query then it is updated to be "yes" I know kind of crude, but like I said I am not a programer. Just an end use with a need.
I then created a macro, that ran both queries, and then created a form, that starts up when the access database is opened, on the form I used the TimerInterval Property to run the macro.
TimerInterval Property
You can use the TimerInterval property to specify the interval, in milliseconds, between Timer events on a form.
Setting
The TimerInterval property setting is a Long Integer value between 0 and 2,147,483,647.
You can set this property by using the form's property sheet, a macro, or Visual Basic.
Note When using Visual Basic, you set the TimerInterval property in the form's Load event.
Remarks
To run Visual Basic code at intervals specified by the TimerInterval property, put the code in the form's Timer event procedure. For example, to requery records every 30 seconds, put the code to requery the records in the form's Timer event procedure, and then set the TimerInterval property to 30000.
so in summary, make all your different queries that you need, to update your records, both in Navision and in Access, put them in a Macro and run the marco on a form, using the timerInterval
Or learn how to program, get the appropriate Navision license and do it all in Navision.
First, i created a Glbal variable thus:
Name DataType Subtype
Access Automation 'Microsoft Access 12.0 Object Library'.Application
Then I created this code in the OnPostReport event of the Report Object.
CREATE(Access);
Access.OpenCurrentDatabase('C:\Users\Mark\Documents\Test.mdb');
It does open the database (If Access is already running), How do i progress from here to insert a few items into a table, say, Table1 in my database? The table is made up of FName and Address as the only columns. Please any contributions will be appreaciated.
Once again, thanks.
Melton