PLEASE HELP!!!!! Connecting Nav to Microsoft Access database

coommark
coommark Member Posts: 17
edited 2009-03-13 in NAV/Navision Classic Client
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

Comments

  • themave
    themave Member Posts: 1,058
    Not being a programmer, I can't help from the Navision side of things, but I needed the same thing, so I did it all in Access. once the odbc connection is setup, I created an append query, the looked at the Navision data, and simple ran the query on a timer in access. So Access does everything, mine just pulls data every 15 minutes, If I ever need it immediately I just run the query manually, it seems to work fine.
  • coommark
    coommark Member Posts: 17
    Hi Themave, thanks for the quick reply. Hmmm that will be a nice one too, until i can get a solution from insode NAV. Please how did you do that? Do you mind sharing some step by step tutorial with me? I havent looked at VBA since the dawn of age (1999). Please help me if you can. I need this help like yesterday.

    Thanks once again for the help.

    Melton
  • ara3n
    ara3n Member Posts: 9,258
    Hello coommark
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3n
    ara3n Member Posts: 9,258
    Take a look at this post.

    viewtopic.php?f=23&t=32375&hilit=Select+cast
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • themave
    themave Member Posts: 1,058
    in general to do it in access, here are the procedures

    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.
  • coommark
    coommark Member Posts: 17
    Hello. Thanks for your replies. Let me share what i am doing, how far i have gone, and maybe someone with more experience will help me fine tune it.

    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

Categories