Changing Databases in VB using the C/ODBC Driver

I recently tried to write a VB Program (VB 5.0) to synchronize a navision DB and another (HP UX Database connected over ODBC in WIndows). The program code I wrote: Dim navConn As Connection Dim hpConn As Connection Dim wrkODBCNav As Workspace Dim wrkODBCHP As Workspace Dim rsQueryNav As Recordset Dim rsHP As Recordset Set wrkODBCNav = CreateWorkspace(“NAV”, _ “”, “”, dbUseODBC) Set wrkODBCHP = CreateWorkspace(“HP”, _ “”, “”, dbUseODBC) Set navConn = wrkODBCNav.OpenConnection(“NavConn”, _ dbDriverNoPrompt, False, _ “ODBC;DSN=VOBISNAV;READONLY=FALSE”) Set hpConn = wrkODBCNav.OpenConnection(“HPConn”, _ dbDriverNoPrompt, False, _ “ODBC;DSN=VOBISHP”) Set rsQueryNav = navConn.OpenRecordset( _ “SELECT Nr_,Name,Saldo FROM Kreditor”, dbOpenDynaset) Set rsHP = hpConn.OpenRecordset(“Kreditor”, dbOpenDynamic) ’ show recordset status If rsHP.Updatable <> True Then msg = "HP: " + Format(rsHP.Updatable) + _ " | NAV: " + Format( rsQueryNav.Updatable) MsgBox (msg) etc. The problem is, whatever I use to open the recordset the recordsets property ‘Updatable’ which allows to use methods like AddNew, Edit, Update etc. is set to FALSE after the call of OpenRecordSet(). I tried the code with a native connection (no ODBC, just used an access database) and it worked fine. Could it be that the ODBC Driver doesn’t allow any writing ? Could anybody imagine how to get an recordset able to be updated ? Thanks in advance for any useful reply, Arno Pucher

Are you connecting as a superuser? If not, perhaps the security does not permit write access? Jim Hollcraft NCSD, NCSP, MCP, MST aka Skater - Unauthorized Navision News


Originally posted by Jim Hollcraft: Are you connecting as a superuser? If not, perhaps the security does not permit write access? Jim Hollcraft NCSD, NCSP, MCP, MST aka Skater - Unauthorized Navision News

No not really, for test purposes I used an local access database and connected to it using ODBC. I use Win98SE as OS and there are no ‘superusers’. Also I tried connecting to the database with the predefined “Administrator” in the access database (which I checked in access". All the same: reading yes, updating/modifying nop.

Arno, If you are connection to a Navision database using the C/ODBC driver, you must have permission to write to the database. I don’t really understand your response to my first question. Are you saying that you are not connecting to a Navision database with the wrkODBCNav object? What are the properties in your DSNs?

Hello Jim, Yes, I connect to the navision database with the wrkODBCNav Object. The wrkODBCHP connects to an access database over ODBC. To the setting of the DSN: Datasource: VOBISNAV Connection: Local Database Name: D:\Data\tools\vb\vobis\database 260.fdb Company: CRONUS AG USERID: empty password: empty Commit Cache: Yes Query Timeout: Yes Enable BLOB Fields: Yes Read Only: No (sic!) Identifiers: All except DOT Option Field Type: Text The other options are not set. Also in the Navision database there are no users defined. Best Regards, Arno

User-Id and Password should be filled in ; if you make jour connection but jou don’t log on, then you don’t have any rights; fill in the username and password I think that may help

I think that if there are not any users defined, then all connections should have super user powers. However, you probably won’t actually use your tool without users defined, so you might as well test it with some users. This might not help, but it is worth a try. Make sure you test with a user that has the SUPER role.