Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: My "Read-Only" text file really isn't ...

    Hello,

    Using Access 2003 running in Access 2000 mode. Trying to link to a text file data source to import some data.

    Using:
    Code:
        Dim adoCON As New ADODB.Connection
        Dim rsSrc As New ADODB.Recordset
        adoCON.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";Extended Properties=""text;HDR=NO;FMT=Delimited;"";"
        rsSrc.Open "Select * From " & sFile, adoCON, , , adCmdText
    Getting a message:
    Cannot update. Database or object is read-only.

    The error occurs at the Open command. Verified file name and path. Tried several different tab delimited text files, none of which are actually read-only.

    I just want to read the data ...

    Am I missing something?

    tc

    PS: I did notice a slight variation in the syntax between Microsoft sources and other online sources for the connection string. Both syntaxes were accepted by Access and both yielded the same result:
    Code:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";Extended Properties=""text;HDR=NO;FMT=Delimited;"";"
    vs
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";Extended Properties='text;HDR=NO;FMT=Delimited'"

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Another method, same result:
    Code:
        Dim dbQry As New DAO.QueryDef
        dbQry.SQL = "SELECT * FROM [" & (sFile) & "] IN '' [Text;HDR=NO;DATABASE=" & (sPath) & ";FMT=Delimited;]"
        dbQry.Name = "qXRF_Raw"
        CurrentDb.QueryDefs.Append dbQry
    Same error message when I try to open the query (the code shown above runs without incident).

    Now I'm confused ...
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ooh - hetergeneous queries - haven't seen for a while.

    Could you just try creating a spreadsheet on the file system and use the below syntax (changing the path etc):
    Code:
    SELECT *
    FROM [TheWorksheet$] IN '' [Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\SomeStuff.xls;TABLE=TheWorksheet$];
    This is tested and works - see if there is something going on with the fielsystem.

    Meantime, I'll play around with flat file syntax

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Works for CSV. Having my tea now
    Code:
    SELECT *
    FROM [Text;HDR=NO;DATABASE=D:\;].test.txt;

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok. Can't get the syntax. Tested your first code sample - no error though it does not pick up the tab delimiter.

    Perhaps:
    http://www.mikesdotnetting.com/Artic...x?ArticleID=67

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Aha!
    http://www.dotnet247.com/247referenc.../13/66383.aspx

    What you passing to the code as sPath and sFile then young man?
    Anyhoo - looks like you will need to use the pesky schemas or use the less exotic CSV.

    Let us know how you get on.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok ok - last spam post - I promise.

    This works for me just fine:
    1) Tab delimited text file called test.txt on D.
    2) File called D:\schema.ini containing:
    Code:
    [test.txt]
    Format=TabDelimited
    3) Called with the below code:
    Code:
    SELECT *
    FROM [Text;HDR=YES;DATABASE=D:\;].test.txt;
    More dets:
    http://msdn2.microsoft.com/en-us/library/ms974559.aspx

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Problem Solved - read text files into recordets at last

    Pootle, as always, you hit the nail on the head (thanks!)

    And the article referenced in your last post was exceptionally helpful (a rarity from Microsoft):
    http://msdn2.microsoft.com/en-us/library/ms974559.aspx

    Two issues here:

    1) My file is Tab Delimited.
    I assumed that tab delimited was one of the defaults. Turns out, there is only 1 default for text files in Jet, and it's comma . One option is to update the registry to use tab as the default, run the query, then update it back. Code to do this in the above referenced link.

    The alternative is to simply use a schema file. So, my code is:
    Code:
        ' Set the Schema file to include this one with the tab delimiter
        Open (sPath & "Schema.ini") For Append As #1
        Print #1, ("[" & sFile & "]")
        Print #1, "Format=TabDelimited"
        Close #1
    It doesn't even matter if you duplicate a file name - the important thing is that the ini is in the same folder (pootle knows that - this hint is for others who read this).

    2) My file extension is .dat
    Another assumption of mine, I assumed that dat was a recognized extension for data files. In fact, I figured that if you specifically point the connection to a particular file that Jet would understand that you expect that file to be a data file, regardless of the extension.

    Turns out, this is not true either . The above referenced file has a very good description of my specific problem:
    What happens if you try to open a file with a different file extension, say, a file named Directory_service.log? In that case, you're probably going to get an error message like this:

    Microsoft JET Database Engine: Cannot update. Database or object is read-only.

    To be honest, this is a very misleading error message. Editorial comment: There's a huge surprise! The problem is not that the database or object is read-only; the problem is that the file extension (.log) is not automatically recognized by the Jet database provider. Don't spend your time (like I, er, I mean, like one of the other Scripting Guys did) setting and re-setting the read-only attributes of the file in question. Instead, start up Regedit, and find this value:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Text\
    DisabledExtensions

    Most likely DisabledExtensions will be set to this:

    !txt,csv,tab,asc,tmp,htm,html

    Double-click DisabledExtensions, and add a comma and the word log to the end of the value, like so:

    !txt,csv,tab,asc,tmp,htm,html,log

    Click OK and re-run your script. The Jet database provider should now recognize .log as a valid file extension for text databases.
    So, I error trap the recordset open command and use the the following function to fix the problem:
    Code:
    Function UpdateRegKey() As Boolean
    
        On Error GoTo setFailed
        
        Const HKEY_LOCAL_MACHINE = &H80000002
        Dim strKeyPath As String
        Dim strValue As String
        Dim strValueName As String
        Dim objReg As Object
        Dim strComputer As String
        
        strComputer = "."
        Set objReg = GetObject("winmgmts:\\" & strComputer & _
            "\root\default:StdRegProv")
        strKeyPath = "SOFTWARE\Microsoft\Jet\4.0\Engines\Text"
        strValueName = "DisabledExtensions"
        objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue
        objReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, (strValue & ",dat")
    
        UpdateRegKey = True
        Exit Function
        
    setFailed:
        MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly + vbCritical, ""
        Err.Clear
        UpdateRegKey = False
    End Function
    The only thing that does not seem to work is that after I update the registry, I clear the connection, reopen the connection and get the same error. But, any time I run the procedure again, it works! It seems the properties for Jet are pulled from the registry when the sub opens, not when the connection is made - a problem for another day.

    In any case, immediate problem solved, the drudgery may now continue

    PS: I imagine the heterogeneous query failed for the same reason, since it gives the exact same response and uses the same resources. I'll try it out when I have time.

    Thanks!



    Edit (5 minutes later): Tried "late binding" and it worked without running the procedure a second time. The adoCON was dim'd to Object and Set adoCON = New ADODB.Connection inserted above the open command. If error on opening the recordset, run the Reg Update function. If that works, set adoCON = Nothing and then repeat the New, Open, Open. Works like a charm.
    Last edited by tcace; 12-18-07 at 12:00.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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