Unanswered: Paradox Issue With Ado Connection - Only READ ONLY
I'm having a mental block, well it was Sunday after all, but I'm sure it's something simple, just can't see what I'm doing wrong! I have tried UtterAccess Forum, but no joy in solving the issue...
Me.txtDatbasePath is a file path without a trailing '\' ie "c:\data"
Field1 is a boolen field in the database paradox1.db
Paradox1.db has many fields but only 1 row, I'm only interested in looking at the value for the one field, called Field1
And toggling the value.
So using ADO (enabled in addins) I thought the follow would work, but the code is failing at "rst!Field1 = True" or "rst!Field1 = False" based on the starting value.
with the message "Cannot Update. Database or object is read-only" (error -2147217911)"
yet I am clearly openning for updates?!
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Me.txtDatbasePath & _
";Extended Properties=Paradox 5.x;"
rst.Open "SELECT Field1 FROM paradox1.db", cnn, adOpenDynamic, adLockOptimistic
'test for empty file
If Not rst.BOF Or rst.EOF Then
If rst!Field1 = False Then
rst!Field1 = True
rst!Field1 = False
'Action if no data in database
thanks for the reply, sadly the connection string should not containt the database name, just the path. with the database name in the path it generates an error with the path name.
I am elsewhere reading the file to test the value of the field, originally as read-only, though changed that to the same connection string as posted before incase the read-only part somehow had stuck! It had not, but left the code as it make no real difference to me.
The other code segment uses a plain "= false" test that works, never come accress "== false" in Access (running 2007), can you explain that to me?
If I try to put == the syntax checker turns the line red?
no, just like this code, it is opened and closed and cleaned up straight away.
I have tried a number of other connections strings, for 3.x 4.x and 5.x but no joy, have tried linking the table and then getting the connection properties - below - but that does not work either - mainly as I'm not sure what to do with them!
"Paradox 3.X;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=C:\;TABLE=par adox1#DB"
then again, tried to make changes in the linked file and that was not allowed either!
But if I use the Borland database desktop utility, i can go in and make changes, no problems?
Now I'm really stumped. Also checked version of Jet 4, and that seems to be latest version for my XP SP3 machine.
but first I need an empty database!, to do that I need to read the entire table structure and content of the 1 record (about 80 fileds and that may change without notice - so need to check each time), rename the old paradox file, recreate it, add the Primary Key and then put back the data with the 1 change!
It may be possible to run an append query ONCE to a paradox file, so could try the simplest, of read the data into access, delete the 1 row, append data BUT this might not work more than ONCE (according to some articles?!).
Either way, far more than I thought I'd need to do to write one value to aone field in a 1 record file!
First task is to read the structure of the paradox table to recreate it in access, any pointers on doing that from VBA code?!