Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text Table Query (Access 97)

    Hi,

    I have a linked table that looks at a .txt file. one of the fields that it holds is a date field. I want to write a query that will report information on a monthly basis. I cannot however apply a criterias based prompt e.g. = [Which Month?] without an obscure error of :

    The text file specification 'Iaslog Link specification13' does not exist. You can't import, export or link using the specification. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    is there any way around this please?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text Table Query (Access 97)

    It seems like a specification was used at some point, and Access gets confused over it now.

    In the first place, I would try a compact and repair (if you have Jet 3.5 SP3, the Compact Database menu option is actually a compact and repair rolled into one).

    If that doesn't help, see what happens if you delete the linked table from the database (this won't affect the actual data, only the link), and then create a new link to the text file.

    If that doesn't work either, could you live with importing the table instead of linking it? You'd have to repeat this from time to time if the text file gets updated.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Table Query (Access 97)

    while i go and try the compact/repair - what is a specification? i didnt create the link to the txt file so i dont know exactly how it was carried out, i am guessing the wizard was used.

    yes the file is updated regularly, i was trying to avoid re-importing the data to refresh it!

    regards
    fatherjack

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text Table Query (Access 97)

    When you use the Import Text Wizard, you specify settings for the import such as the field separator, text delimiter, field names, field types etc. It is possible to store these settings as a "Specification" for later re-use. Instead of specifying all the settings again manually, you can open the existing specification. The same holds for exporting to a text file. If you try the Text Import or Export Wizard, look for the Advanced... button - it opens a dialog where you can set, store and retrieve specifications.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Table Query (Access 97)

    Right, i see what you have described - learning all the time

    i cannot however make a connection without a specification being created and the same error when i try to filter the results with a criteria that has a [prompt]. is it actually possible, do i have to make a specification with a parameter that will alllow it?

    or will it be easier to right the import routine in VBA?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text Table Query (Access 97)

    I don't understand. I can link a text file without creating a specification, and I have no problem creating a parameter query based on the linked text file.

    Someone else with a bright idea?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text Table Query (Access 97)

    If you've ever created a new database and copied the objects into it to cure some other problem, you may not have checked off the additional options that include import/export specifications. That would have lost the spec but doesn't explain why your database thinks it needs it to make a link. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> It's been a long time since I tried to link to a text file, but I seem to recall that that particular kind of link requires either a specification or a schema.ini file in order to know how to handle the contents of the file (i.e., field delimiters, data types, etc.).
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Table Query (Access 97)

    do you create the link with the new table wizard?

    when i do, i dont set any specification settings but when i go through the wizard again there is a previous specification that i created (even though before the first time i deleted them all). also when i design the table and vierw the properties the description contains the line :
    Text;DSN=Iaslog Link Specification;FMT=Delimited;HDR=NO;IMEX=2;DATABASE =192.168.1.17logslogfiles;TABLE=iaslog#txt

    so it seems to be thinking about a specification even if default settings are used.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Table Query (Access 97)

    thanks for the advice so far. i can see that i have stumbled into a minefield here.

    i think the bottom line is - can i put a prompt in a criteria where the data source is a text file, given that the link to the text file needs a specification?

    if it is not possible then i will get started on the data import routine.

    cheers
    fatherjack

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text Table Query (Access 97)

    You're correct that a specification is created on the fly; I hadn't noticed that. The description property of my linked table looks very much like yours, apart from the names of the specification and of the text file, the only difference is that in my case the "path to the "database" contains a drive letter or is un UNC path, not an IP address.

    As I mentioned, I can create a query with a parameter (that prompts the user for input) based on the linked table, and it works.

    Can you try it with a drive letter or UNC path? As a test, you could put a text file on your local hard disk.

Posting Permissions

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