Can someone post some code how to connect to a Navision database using ODBC in a Visual Basic v6 application?
I created a ODBC DataSource using the Control Panel (named Navision) and use the following code in VB:
Dim connNavision As New ADODB.Connection
connNavision.ConnectionString = "DSN=Navision"
connNavision.Open
connNavision.Close
At run-time, VB gives an error "[Navision Software a/s][Navision Financials ODBC Driver]ISAM error" on the connNavision.Open line.
No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
0
Comments
Set connNavision = New ADODB.Connection
connNavision.Open "DSN=Navision"
Set rsNavision = New ADODB.Recordset
For i = 1 To 100
strSQL = "INSERT INTO ""MessageQueue Input"" (ID,Sequence,Key,Value) VALUES (" & i & "," & i & ",'Key" & i & "','Value " & i & "')"
rsNavision.Open strSQL, connNavision
Next i
Set rsNavision = Nothing
Set connNavision = Nothing
After record 24, following error is displayed "Run-time error 80010108; Method 'Open' of object '_Recordset' failed". The records (24) are created in the database.
If so are you trying to open 100 connections / recordsets to your database ? What is you license count ?
Would you not wnat to open rs.
then loop
then close, disconect?
Bruce
set connection
open recordset
for i = 1 to 100
rs.addnew
set connection
open recordset
for i = 1 to 100
rs.addnew
set fields as required using (i)
rs.update
rs.movenext
next i
rs.close
set all to nothing
open recordset
<HR></BLOCKQUOTE>
How do I open the recordset when I use rs.addnew? Say I want to add records to the table Item, how do I write "rs.Open ..." ?
Try this
dim rs as new ADODB.Recordset
rs.open "Select * from items", DSN="YOURNavisionDSN",
rs.movefirst ''Goto the first record
for i = 1 to 100
rs.addnew 'add the new record
rs.("description")="item description"
rs.update ' update the record
rs.movenext
next i
rs.close
set rs = nothing
forgive me if i'm teaching you to suck eggs, but the link below from the MS website should show you the way...Assuming this isn't a navision Bug. BTW your DSN is a system object is it.
http://support.microsoft.com/support/kb/articles/Q168/3/36.asp
HTH
Bruce
search on mdac_typ.exe
Bruce
this code is a complete example showing connection string, commands etc... this should work for you, please amend as required with your data for input, you will neeed to change the connection string and table name
Public Sub AddNewX()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strID As String
Dim strFirstName As String
Dim strLastName As String
Dim booRecordAdded As Boolean
' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"
''PUT YOUR CONN STRING IN HERE INSTEAD
cnn1.Open strCnn
' Open Employee table.
''PUT YOU TABLE NAME HERE
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable
' Get data from the user.
strID = Trim(InputBox("Enter employee ID:"))
strFirstName = Trim(InputBox("Enter first name:"))
strLastName = Trim(InputBox("Enter last name:"))
' add the record
rstEmployees.AddNew
rstEmployees!emp_id = strID
rstEmployees!fname = strFirstName
rstEmployees!lname = strLastName
rstEmployees.Update
booRecordAdded = True
'close the recordset
rstEmployees.Close
'close the connection
cnn1.Close
End Sub
I have just tried it on a stand alone copy of 2.01 and it works no problem. If you want to send me your eamil address i can send you a VB class / project which you can try ?
Bruce
Brucem@aston-chemicals.com