Inserting into MS Access from Navision

sbhamidi
sbhamidi Member Posts: 22
edited 2005-03-22 in Navision Financials
I'm trying to insert data from Navision 2.60 E into MS Access. I'm using the following code: 
// DataConn is an Automation datatype and is a 'Microsoft ActiveX Data Objects 2.7  Library'.Connection
// DataRSet is an Automation datatype and is a 'Microsoft ActiveX Data Objects 2.7 Library'.Recordset
 
CREATE(DataConn);  
DataConn.ConnectionString := 'DBQ=F:\247 Deliverable\Testdb.mdb;DRIVER={Microsoft Access Driver (*.mdb)}';
CREATE(DataRSet);
DataConn.Open;
Window.UPDATE(1,'Customer');
strSQL := 'INSERT INTO Customer_Service_Level(Customer_No_,Service_Level_Code,Default,Service_Level_Description) ';
strSQL := strSQL + 'VALUES("2464212230","2","-1","SLOW")';
RecordsAffected := '';
Options := 0;
DataRSet := DataConn.Execute(strSQL,RecordsAffected,Options);
When I try to run this test, I get the error:
=================================
"The call to member Execute failed. Microsoft OLE DB Provider for ODBC Drivers returned the following message:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4."
=================================
I was doing a simple DataConn.Execute at first, but now used the empty variables RecordsAffected and Options as parameters to overcome this error. But still the error persists.
I would appreciate it if anyone could point me in the right direction.
TIA

Comments

  • ErictP
    ErictP Member Posts: 164
    I think the the SQL-statement must be

    strSQL := 'INSERT INTO Customer_Service_Level SELECT '2464212230' as Customer_No_, '2' AS Service_Level_Code, '-1' AS Default, 'SLOW' AS Service_Level_Description'
  • Faberman
    Faberman Member Posts: 24
    We also use Navision 2.60G -

    I have a test mdb connecting Navision using DAO and C/ODBC.
    The connection is made without a problem.
    I can even access data from a table i.e. "Item" table using a recordset and the data can be displayed in an Access form.

    i.e.

    StrSQL = "SELECT * FROM Item WHERE Item.No_='24351189595'"
    Set rst = Conn.OpenRecordset(StrSQL, dbOpenDynamic)

    me.txtbox=rst![Description]

    When I attempt to do the same with another table i.e. "Purchase Line" table, I get "ODBC - call failed" error messages.

    StrSQL2 = "SELECT * FROM [Purchase Line] WHERE [Purchase Line].[No_]='24351189595'"

    Set rst = Conn.OpenRecordset(StrSQL2,dbOpenDynamic)

    Can anyone tell me why it will work with one table and not another?
  • Faberman
    Faberman Member Posts: 24
    I have (since my last post) achieved what I needed to do.
    I will post examples of what I did to hopefully help anyone else who's had the same problem -

    1) I installed C/ODBC from our Navision disk.

    2) I set up a System DSN with minimal info in it

    i.e. DSN name
    Description
    Path to the server program folder (a mapped drive)

    I left all else blank and I left the Options as default.

    3) I began a new module called "NavisionConnection"

    4) I put in the following two procedures :

    Public Sub OpenNavisionConnection()

    Dim NavName As String 'User credentials for Navision
    Dim NavPass As String

    NavName = DLookup("NavUserName", "Users", "UserName='" & Forms!Login!Uname & "'")
    NavPass = DLookup("NavPassWord", "Users", "UserName='" & Forms!Login!Uname & "'")

    Set WrkODBC = CreateWorkspace("", "", "", dbUseODBC)

    '****** connection made here ******

    Set Conn = WrkODBC.OpenConnection"", , , "ODBC;DSN=dsnname;CSF=Yes;;
    DRIVER={C/ODBC 32 bit};SName=servername;NType=tcp;
    CN=companyname;UID=" & NavName & ";PWD=" & NavPass & ";OPT=Text;QTYesNo=Yes;QT=60;IT=a-z,A-Z,0-9,_;
    RO=No;CC=Yes;BE=Yes;")

    End Sub



    Public Sub CloseNavisionConnection()

    '******Close the Navision connection******

    WrkODBC.Close
    Set Conn = Nothing

    End Sub


    They are both public and, therefore, accessible so they can be reused.

    I have a table in my Access DB for user details etc. - I used the DLookup calls to retrieve the appropriate details to use in the connection string.


    5) In a form, I can call upon the conn object (i.e. with a button) :

    Private Sub whatever_DblClick(Cancel As Integer)

    Dim StrSQL As String
    Dim rst As DAO.Recordset

    '******Check Inventory******

    StrSQL = "SELECT * FROM Item WHERE Item.No_='" & Thing & "'"

    Set rst = Conn.OpenRecordset(StrSQL, dbOpenDynamic)

    '***and so on using the normal recordset operations***


    '***when you're done***

    Set rst = Nothing
    CloseNavisionConnection

    End Sub

    I don't think I've missed anything out - if I have, apologies.
    This method of connecting, then disconnecting when finished also helps keeps the number of concurrent sessions to a minimum.

    Have fun.
  • Faberman
    Faberman Member Posts: 24
    Oh yes, I did forget -

    The answer to my post dated : 17/03/05 was that with the ... IT=a-z,A-Z,0-9,_; ... setting in the connection string, all non-alphanumeric characters need to be replaced with an underscore.

    i.e.

    Dim StrSQL as String
    Dim rst as DAO.Recordset

    StrSQL = SELECT * FROM Purchase_Line WHERE Purchase_Line.Qty__to_Receive >0 '(the dot notation to specify fields remains)

    set rst = conn.OpenRecordset(StrSQL, dbOpenDynamic)

    refer to fields in the recordset as usual (retain underscores) :

    rst![Qty__to_Receive]

    etc. etc.

Categories