file extensions, jet & registry


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: file extensions, jet & registry

Hybrid View

  1. #1
    Join Date
    Jun 2004
    Posts
    34

    file extensions, jet & registry

    I created a program which uses dao35 & jet. It attaches text files, (data files which are used by the program) to an access database created in code and then from the attached files, imports the data into the database. The attached files are updated daily with new data so each time the person runs the program, they re-import the data that the attached files have updated. (The attached files are updated by a separate program created by a data vendor)

    This all worked fine until last year when I had to do a work around so that I didn't have to distribute mdac_type2.5 since XP uses dao360 and doesn't include jet and certain data files, with unusual extensions would not attach.

    The work around worked for a while but now it has stopped working for those data files again. My 98 windows program must have received some auto update which changed it because the same build which used to work will no longer work.

    Now the problem: The program attaches data files with known extensions such as .csv or txt or asc etc., but unrecognized file types with extensions such .lng or whatever other extensions some data vendors may use will only attach if I go into the windows registry, under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Text\DisabledExtensions=!txt,csv,tab ,asc,tmp,htm,html"
    and add the unknown file extension to that list:
    =!txt,csv,tab,asc,tmp,htm,html,lng" on 98 machines and
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions=!txt,csv,tab ,asc,tmp,htm,html"
    to XP machines.

    I was told by one microsoft tech that this feature was created to help security and control viruses.

    I could give instructions to people using these unknown file types on how to edit their registry (which people don't like to do) but there would still be 1 problem. There are some data types for this type of data that have NO extensions, just a file name. So in that case, there is no way to edit the registry to allow those files.

    Can anyone help me with what I can do with my program to allow these ASCII files to attach regardless of the file extension or lack of file extension?
    Thanks

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Essentially you have two choices if you are using data access and both can probably be done dynamically via code. One is the registry modification you mentioned. The other involves adding or changing the file extension.

    ACC2000: Importing or Linking a Text File Fails for a File That Does Not Have a Valid File Name Extension
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jun 2004
    Posts
    34
    Thank you much! That describes my situation perfectly. Now I will have to figure out where in my attachment routine to put this in my program.

    Do you know if those attached files will continue to be updated each day with the new file extensions?

    Chris

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    Originally posted by Chris8
    Thank you much! That describes my situation perfectly. Now I will have to figure out where in my attachment routine to put this in my program.

    Do you know if those attached files will continue to be updated each day with the new file extensions?

    Chris
    Yes, the file with the valid (new) file extension will be the linked file. Any changes to the file outside of Access will be reflected when viewing or querying the linked table in Access.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Jun 2004
    Posts
    34
    The code given in the link you gave which I pasted below, is to create a macro in Access I think.

    If I am inserting this in my program, would I save this code in a "class module" or a ".bas module"? I am a neophyte so this is not easily understood by me.

    Thank you
    Chris

    >> from web:

    Create a new function:
    Open the sample database Northwind.mdb.
    Click the Modules tab.
    Click New.
    Type or paste the following code in the module:Public Function ImportNonTXT()
    Dim fs, Fn, FLength, Fext, FDot, FOrig

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set Fn = fs.Getfile("C:\Employees")

    ' This holds the file's original name for rename later.
    FOrig = Fn.Name
    ' Get the length of the file name.
    FLength = Len(Fn.Name)
    ' Set this value = to the last four characters of the file name.
    Fext = Right(Fn.Name, 4)
    ' Set this = to the first character.
    FDot = Left(Fext, 1)

    ' If there is a dot in the fourth from the last position...
    If FDot = "." Then
    '... and the extension is not .txt.
    If Fext <> ".txt" Then
    ' Remove the extension from the file name.
    Fn.Name = Left(Fn.Name, (FLength - 4))
    ' Add the .txt to the file name.
    Fn.Name = Fn.Name & ".txt"
    End If
    Else
    ' If there is not a dot in the fourth position
    ' add the .txt extension.
    Fn.Name = Fn.Name & ".txt"
    End If

    ' Transfer the file to a new table.
    DoCmd.TransferText acImportDelim, "", "Employees_Text", "C:\Employees.txt", False, ""

    ' After the file is transferred, rename it back to
    ' its original name.
    Fn.Name = FOrig

    End Function

    Save the module as Import_Text.
    Create a new macro that has the RunCode action.
    In the Function Name property, enter ImportNonTXT().

    NOTE: "ImportNonTXT" is equivalent to the function name.
    Save the macro as mcrImport-Text.

  6. #6
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538

    Re: file extensions, jet & registry

    Chris:

    You would add it in a .BAS module.


    HTH,

    -Andrew

  7. #7
    Join Date
    Jun 2004
    Posts
    34
    I tried altering the code at the microsoft link in the procedure where I add the text file to my database. That code uses a DoCmd.TransferText method which doesn't work for vb. So I tried another procedure which replaced the name of the txt file I was working with in its original location which is no good of course since the original file in its locatin needs to remain unchanged.

    Can someone tell me what the DoCmd.TransferText equivalent procedure in VB would be that i should use?

  8. #8
    Join Date
    Dec 2003
    Posts
    2,750
    OK, I thought you were just linking text files to an Access database. Are you trying to import them into a native Access table as well?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  9. #9
    Join Date
    Jun 2004
    Posts
    34
    I created an access table in code, then attach some text files to it, which are then later imported into the table. Each day those text files are updated, I re-import the data into my database of the files that i've attached to my database. I don't have the Access program software.

    I'm not sure what language docmd is.

    Thanks

  10. #10
    Join Date
    Dec 2003
    Posts
    2,750
    DoCmd is part of the Microsoft Access object model.

    I'm still not sure what you mean by *import*. You have linked tables to these text files in Access. Are you then moving this data into native Access tables? Is that what you mean by *import*?

    If this is the case the you should be able to use SQL Insert queries to append the data from the linked tables to the native Access tables.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  11. #11
    Join Date
    Jun 2004
    Posts
    34

    Post

    Hi:

    I do use an insert statement. That's not my problem. I may not be using the right language here. I'll try to better explain:

    In plain English, I use a technique that attaches or links some text files to my database. It creates a schema.ini file in the original location folder where those original text files are stored.

    If the files are properly attached, (when the program works correctly) you can open my database, (which is in its own file folder/location) and see the several files which are attached to my database. The original location of those text files are in another folder, updated by a separate program, unrelated to my program. All my program does is place a schema.ini file in their folder.

    Each time those text files are updated by that other program, (daily) the files I've attached to my database are also updated in my database.

    My program each day then moves the data, (as I use the term *import*) from those *attached tables*, (not from the original location) into my database by using an insert statement:

    db.Execute "Insert Into ...."

    I cannot get past the part of my code where I *add* certain files to my database however since the windows jet program changed to exclude certain extensions. The program still works fine with .csv, txt., etc., but not with unique extensions. When I broke, to see where the code was failing, it was at the procedure below:
    ------------------------- code from program:----------------

    Function AddTextTable(TableName) As Boolean
    On Error GoTo BadAddTextTable
    '=======================================
    '
    AddTextTable = True
    Dim MyTD As TableDef
    'attach the text table

    Screen.MousePointer = vbHourglass
    If TableName Like "*.*" Then
    Set MyTD = db.CreateTableDef(Left(TableName, Len(TableName) - 4))
    Else
    Set MyTD = db.CreateTableDef(Left(TableName, Len(TableName) - 0))
    End If
    MyTD.Connect = "TEXT;DATABASE=" & File1.Path & "/;"
    MyTD.SourceTableName = TableName
    db.TableDefs.Append MyTD

    '=======================================
    ExitAddTextTable:
    'exit code here

    Screen.MousePointer = vbDefault
    Exit Function
    BadAddTextTable:
    MsgBox Err.Description, vbCritical, "AddTextTable"

    AddTextTable = False
    Resume ExitAddTextTable
    End Function
    '-----------------------end
    Chris

  12. #12
    Join Date
    Dec 2003
    Posts
    2,750
    Since you now have code to rename the files with the appropriate file extension (as was posted in the MS KB article) and then use your existing code to create new linked tables to these files, I'm not exactly sure where the hang up is.

    I thought we had resolved the file extension issue???
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  13. #13
    Join Date
    Jun 2004
    Posts
    34
    It now tells me it can't find object and list the new file name. Its attaching a file to the database with the name I want, and keeping the original with the same name, but the data isn't there.

    I'll work on it some more before I post more code.

    Thanks,

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center