none
ERROR [42000] [Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only RRS feed

  • Question

  • When i tried to setup an ODBC data source to a text file the connection and read the file perfectly.

    when tried to set up an ODBC data source other then text file (Eg. *.p06), I am reciving an error that says, ‘ERROR [42000] [Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.’.   

    I read some where this is a security feature of the Microsoft Jet ODBC Text driver. The ODBC driver only allows you to read files with the following extensions txt, csv, tab, asc,  htm,  html.

    My question is: How can fix the ODBC issue if I want to process the file with extension file (Eg. *.p06).

    Quick reply is appreciable.

    a) I have tried below way to fix the issue but no luck.

    I have tried below changes as suggested some other blug. but below solution did not fix the issue either.

    1. Start the registry editor (regedit.exe).

    2. Navigate to the HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced registry subkey.

    3. Double-click HideFileExt and set it to 1 to hide known file extensions.

    4. Click OK.

    5. Close the registry editor.

    Thanks,

    Satya

    Tuesday, January 19, 2016 12:18 PM

Answers

  • Can you use a DSN-less connection string in your code? This will enable you change the file extension in your code:

            Dim TextConnection As System.Data.Odbc.OdbcConnection = New System.Data.Odbc.OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};" _
                                        & "Dbq=C:\Users\...\Documents\My Database\Text;")
    
            TextConnection.Open()
    
            Dim da As New System.Data.Odbc.OdbcDataAdapter("SELECT * FROM Customers.txt", TextConnection)
            Dim dt As New DataTable
            da.Fill(dt)
    
            '...
    
            TextConnection.Close()

    I have not found a way to use a non-standard file extension.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 20, 2016 2:24 PM

All replies

  • I would not modify the Registry. Just use Path.ChangeExtension to change the file name to a supported extension, such as .csv or .txt.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, January 19, 2016 2:11 PM
  • Hi Paul, Thank you for the reply, can you please let me know how what is the procedure to change the Path.ChangeExtension in windows 7 in ODBC settting!!

    Tuesday, January 19, 2016 3:02 PM
  • I'm not sure what you mean. Are you using an ODBC DSN? If so, try adding the extension through the Options for the Text driver in the ODBC setup.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, January 19, 2016 4:08 PM
  • Hi Parul,

    Yes you are correct, I meant to say ODBC DNS.

    I had added the new file extension *.p06 in similay way you have mentioned, however while reading the new extesion file via ODBC DSN, I get the error "ERROR [42000] [Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.’.".

    If I rename the file extension to *.txt, it does works perfectly, but this approach is not adoptable. 

    Can you suggest if you have any other solution for this to fix?

    Thanks,

    Satya

    Wednesday, January 20, 2016 10:06 AM
  • Can you use a DSN-less connection string in your code? This will enable you change the file extension in your code:

            Dim TextConnection As System.Data.Odbc.OdbcConnection = New System.Data.Odbc.OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};" _
                                        & "Dbq=C:\Users\...\Documents\My Database\Text;")
    
            TextConnection.Open()
    
            Dim da As New System.Data.Odbc.OdbcDataAdapter("SELECT * FROM Customers.txt", TextConnection)
            Dim dt As New DataTable
            da.Fill(dt)
    
            '...
    
            TextConnection.Close()

    I have not found a way to use a non-standard file extension.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 20, 2016 2:24 PM
  • Hi Paul,

    Application is able to read .txt file vis ODBC DNS but only creats a problem for a non-standard file extension.

    Thanks,

    Satya

    Wednesday, January 20, 2016 3:30 PM
  • Yes, I know, but I do not see that non-standard file extensions are supported.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 21, 2016 2:04 PM