indusoft database issues

Hi,
I am having issues with any sort of write functions to a MS access db from indusoft
The code is simple SQL to insert, this works one day and then not the next (although nothing has changed ie db connection, file permissions etc). It is like some service is shutting off that is required to write? (I can read no problem). Either that or the file is locking itself.
Could anyone please advise what checks I should do or any tests I could do.
I tried the secure viewer on the server as well, this has the same issue.
The current error I am receiving back in the tab is "operation must use an updateable query"

Is there anything preventing you from upgrading MS Access to MS SQL? I've had so many problems with Access, and they disappeared when I convinced the client to upgrade. There was even one time their main server Access database became corrupted and inaccessible... they were lucky a repair tool recovered it!

Can you post the exact VBScript you are using ?

Does it look like the error message originates with Access, or with IWS ? Do you receive the error message in a tag that you designate as an argument when you execute the SQL command, or is it an error returned by IWS itself and shown in the Output window ?

Which driver are you using ? Which version of Access and which OS ?

I have a project where I'm trying to migrate from RSView SE to Indusoft and there's a MS Access 2003 database in the middle of it, so you've got my attention.

sigmadelta said:

Is there anything preventing you from upgrading MS Access to MS SQL? I've had so many problems with Access, and they disappeared when I convinced the client to upgrade. There was even one time their main server Access database became corrupted and inaccessible... they were lucky a repair tool recovered it!

Click to expand...

Thanks, I will give this a go, we do have some MS SQL connections already set up, I've had nothing but problems with this access

Ken Roach said:

Can you post the exact VBScript you are using ?

Does it look like the error message originates with Access, or with IWS ? Do you receive the error message in a tag that you designate as an argument when you execute the SQL command, or is it an error returned by IWS itself and shown in the Output window ?

Which driver are you using ? Which version of Access and which OS ?

I have a project where I'm trying to migrate from RSView SE to Indusoft and there's a MS Access 2003 database in the middle of it, so you've got my attention.

Click to expand...

$DBExecute( "databasename", "INSERT INTO table1 (EventDate,EventTime,Msg1) VALUES ('{todaydate}','{todaytime}',{msg1});","",1,"$teststr")

I have tried using the DBINSERT() as well (to no avail),
The problem I have is it sometimes works and sometimes doesn't which leads me to think its less of a code problem, I have also tried a dbexecute containing no tags and just Delete * from table1 and that also fails and gives the same error.

The error when I google it is quite widely found and only associated with 2 articles about indusoft so I don't think the error is from indusoft, however, the error is displayed in the indusoft output window (with database checkbox enabled) as well as the error tag $teststr. The original access file was created in 2003 and is .mdb extension. database connection is jet 4.0, no password with share deny none permissions.

To further note, I created a completely new access file, gave every security level full control, created a new db connection, restarted IWS and still not working, half an hour later it might work for 5 minutes if it feels like it then stop working again.

I will give MS SQL a go early this week and post back here to let you know how it goes.

Thanks for the details !

The fact that the error message appears in the tag "test_str" and in the Output window means that Access is getting the INSERT request, but rejecting it.

Usually when I see that, it means that I've mis-formatted my SQL statement. But if it works some of the time, that should mean you have good syntax.

The bit of Googling I'm seeing suggests that the problem is the *.ldb file, and that the database is being locked by another process.

https://www.mikesdotnetting.com/article/74/solving-the-operation-must-use-an-updateable-query-error

Is another program or process accessing that database, maybe to make reports or backups ?

Thanks, I will give this a go, we do have some MS SQL connections already set up, I've had nothing but problems with this access

Click to expand...

My client had a custom-written VB application, and it was the only application writing to MS Access and had been installed for at least 6 years before I came on the scene. I was constantly getting contacted to either close and open application, restart the computer, fix database records that were missed, repair the corrupted database... and it was happening at a frequency from once every 2 weeks to several days in a row.

Ever since I switched to SQL, none of those problems happened again. You can look at MS Access the wrong way and it croaks. So often I had to close the VB app, delete the MS Access ldb file (the "lock file"), and restart either the application or the computer.

I upgraded a second client from MS Access to SQL because of multi-user issues. In my opinion, MS Access is designed for a handful of people using it on the same computer one at a time, not for concurrent use over the network.

I can't see this ldb file, I tried typing in the address bar for it and changing folder to show hidden files.
Only indusoft accessing this file, only me testing making queries.
I also created a new access file in a different folder as I said and new connection, you should expect this would at least work for the first query but no.

What version of Indusoft are you using? There is a known issue with v8.1 SP3 database connections.

When a database command is executed, it creates a connection in Studio Database Gateway. In the application I was working on, this connection will prematurely close if another database command is executed in the background (e.g. a database worksheet). Or the connection seems to time-out after 30 or more seconds. This issue is happening on both MS Access and MS SQL Server databases. (possibly others but that's what I tested.)

I spent quite a bit of time with Indusoft support and they finally agreed this was an issue.
The latest response I received from Indusoft support is that this issue is currently in the product backlog and is not resolved in v8.1 SP4. They have however indicated that v8.1 SP1 is unaffected but I personally have not tested this yet. They also hinted at a hotfix now that SP4 is released but I've heard no further word on that.

sbaum said:

What version of Indusoft are you using? There is a known issue with v8.1 SP3 database connections.

When a database command is executed, it creates a connection in Studio Database Gateway. In the application I was working on, this connection will prematurely close if another database command is executed in the background (e.g. a database worksheet). Or the connection seems to time-out after 30 or more seconds. This issue is happening on both MS Access and MS SQL Server databases. (possibly others but that's what I tested.)

I spent quite a bit of time with Indusoft support and they finally agreed this was an issue.
The latest response I received from Indusoft support is that this issue is currently in the product backlog and is not resolved in v8.1 SP4. They have however indicated that v8.1 SP1 is unaffected but I personally have not tested this yet. They also hinted at a hotfix now that SP4 is released but I've heard no further word on that.

Click to expand...

7.1, not sure what SP

I would strongly recommend a database like MySQL or MSSQL Express (Free Versions).

I have had unbelievable issues with IWS7.1 and 8.0, I remember one time, we called in to tech support for various issues, we ended up adding the entire IWS installation directory to windows defenders exclude list. Windows defender was screwing with IWS during runtime, causing all types of havoc. The issues would start when defender started a scan, we spotted this by using windows event viewer and noticing a correlation between defender starting and our issues beginning.

I have been unsuccessful in locating a rant by an Indusoft employed programmer, funny stuff, the programming team was not well organized, and the software was in bad shape.

If at all possible use Ignition .
You can download the full software and run for two hours. You can reset the trial unlimited amount of times.
Visit Inductive University for full training videos.
Visit the full Ignition 8 Demo Here, you are online with a full SCADA system.

no hassle licensing, only pay for what you need.

This is probably verging on hijacking this thread...

I would agree with the above post that Indusoft seems to be prone to issues and I question the stability of their development team. Each new release seems to have something broken so I tend to stick with a version where I know what is broken and can program around it.

But can Ignition match the functionality of Indusoft at the same cost?
We mainly use Indusoft for 'HMI' on embedded systems. I did take a look at Ignition awhile back and when I stacked up all the built-in Indusoft features we use (alarming, databases, thin clients, PLC drivers, schedulers to name a few) I found we'd be looking at a whopping Ignition price tag compared to Indusoft.
In a SCADA application this would obviously be different but for a full-featured HMI software running on embedded systems, it didn't appear Ignition would be a good fit.

Mass89, Check out this MS Access post, it might be OS related.
https://stackoverflow.com/questions/19789709/operation-must-use-an-updateable-query-error-in-ms-access

Try disabling antivirus scans on the Indusoft directory, Also make sure Indusoft is running as a service with correct privileges.

Yea, Ignition can be costly compared to Indusoft but is still cheaper than Wonderware.
Ignition edge might be worth considering Ignition Edge for about $500-$1500. I have edge running on a Raspberry PI. I have also run the full-blown ignition gateway on a raspberry pi, but the pi lacks resources for a large SCADA system.

I think you need a gateway for database interaction. Or down and dirty script the database interaction in python.
https://docs.inductiveautomation.com/display/DOC79/Edge+Common+Tasks