Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    4

    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?!


    Code:
    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
            Else
                rst!Field1 = False
            End If
    Else
        'Action if no data in database
    End If
    rst.Close
    cnn.Close
    Any help/ideas/Pointers VERY welcome!
    Thanks

  2. #2
    Join Date
    Dec 2009
    Posts
    50
    Haven't used a paradox table in years, but it looks like your connection and query are not right.

    What is the actual Table name in the Paradox database?

    Try this:

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "C:\Data\paradox1.db" & _
    ";Extended Properties=Paradox 5.x;"

    rst.Open "SELECT Field1 FROM TABLENAME", cnn, adOpenDynamic, adLockOptimistic

    Maybe completely wrong.. sry if thats the case. Also, noticed

    If rst!Field1 = False Then
    rst!Field1 = True
    Else
    rst!Field1 = False
    End If
    may need to be

    If rst!Field1 == False Then
    rst!Field1 = True
    Else
    rst!Field1 = False
    End If
    Again.. may be completely off base here, but thats what I've noticed that doesn't seem correct.

    Jim

  3. #3
    Join Date
    Feb 2011
    Posts
    4
    Hi Jim
    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.

    I took the connection string info from Paradox Connection String Samples - ConnectionStrings.com which shows that the v7.x driver is read only but the one I'm using should be good for read and write...

    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?

    Just as an update, looking at other connection strings, from ADO Connection String Samples where there is a ODBC string ...
    Code:
    cnn.Open "Driver={Microsoft Paradox Driver (*.db )};" & _
               "DriverID=538;" & _
               "Fil=Paradox 5.X;" & _
               "DefaultDir=" & Me.txtDatbasePath & ";" & _
               "Dbq=" & Me.txtDatbasePath & ";" & _
               "CollatingSequence=ASCII"
    the error then moves to the "rst.Update" statement, rather then the "rst!Field1 = True" statement, a bit further down the code, no further fowards :-(

    thanks anyway, any other ideas most welcome

  4. #4
    Join Date
    Dec 2009
    Posts
    50
    Is your other connection open at the same time you are trying to update the table with this connection? Try closing all other connections then doing the update.

  5. #5
    Join Date
    Feb 2011
    Posts
    4
    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.

  6. #6
    Join Date
    Feb 2011
    Posts
    4

    No solution but issue identified (I think!)

    Well after much playing, I have not fixed the issue, but think I understand the problem.

    I can read and write to other paradox files, it appears that THIS file might not have a "Primary" key, there is a .mb and .val file but not a .PX file.

    here are some articles that I found useful:

    "Cannot Define Primary" in Paradox Table
    ACC: "Operation Not Supported on Paradox Table" Error Message
    ACC2000: Error Occurs When You Try to Use Multiple Instances of a Linked Paradox Table
    CREATE INDEX for Paradox

    There is one article I found that had some ideas about creating a Primary Key:
    Primary Key woes with Paradox - vbCity - The .NET Developer Community

    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?!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •