Inserting into MS Access from Navision

sbhamidi

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

ErictP

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

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

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

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.