Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2002
    Posts
    4

    Angry Unanswered: Refreshing Text Link in VB & VBA

    Refreshing Text Link in VB & VBA
    In my VB App or inside a VBA code how can I refreshing the table link to an extrernal text file without receiving the error:

    Runtime Error '3625': The text file specification 'xxx' does not exist. You can't import, export, or link using the specification.


    Obviously the DSName ("ARTC") specified in the CONNECT string exists in the MDB because I've just used it to link the old text file that now doesen't exists in the same path.

    Following you can find a code example:

    Dim ArtcDB As Dao.Database
    Set ArtcDB = OpenDatabase("Var.mdb")
    ArtcDB.TableDefs("ARTC").Connect="Text;DSN=ARTC;FM T=Fixed;HDR=NO;DATABASE=" & PathTmp
    ArtcDB.TableDefs("ARTC").RefreshLink

  2. #2
    Join Date
    Apr 2002
    Posts
    2

    Question

    Do you want to import the text into one of the table's fields or take lines out of the text file and parse them as data to be added to the table as a new record?

    David

  3. #3
    Join Date
    Apr 2002
    Posts
    4
    I want to refresh a link of a Linked Table in a Access MDB file using an Access DSN stored in the MSB file.

    I don't speak English well but in a poor words a DSN for a text file is a connection specification that Access store inside its MDB Database in an internal invisible table. A DSN for the Text Driver contains the link specs and it is always used to split a fixed text line record into fields.

    Because the linked file to the Access MSB can change name or path I want to refresh the link updating the Name of the Path of the file but using the same DSN specs.

    When I set the Connect property of the table, also with the same values, without changing anything, I receive the error:

    Runtime Error '3625': The text file specification 'xxx' does not exist. You can't import, export, or link using the specification.

    This can suggest me that before the Refresh operation I must register my database or telling DAO to find the DSN names inside the Access database.

  4. #4
    Join Date
    Jun 2002
    Posts
    6
    Did you ever find a resolution to this problem? I am now experiencing the problem and cannot find a way around it.

    Angela

  5. #5
    Join Date
    Apr 2002
    Posts
    4

    Angry

    No, at this time no one gives me hints to solve the problem. I've temporary avoid the problem using similar functions but inside the Microsot Access Database. Obviously you have to open it to update the links. At this time I've no solution to update the pathnames of the linked tables. Please tell me if you achieve to obtain an helpful hint.

  6. #6
    Join Date
    Jun 2002
    Posts
    6
    I am investigating using a Schema.ini file instead of the import/export file specification generated by the Linked Table Manager.

    For information on how to use the Schema.ini file, see the following articles on webset support.microsoft.com.
    Q190186: Errors Refreshing Linked Text Fiile via Linked Table Mgr.
    Q149090: How to Use Schema.ini for Accessing Text Data
    Q155512: How to Create a Schema.ini File programmatically.

    Note that in Q155512 they write code to create a schema.ini file. I used a different method, based on the original import/export file specification used by the Linked Table Manager . You will find the specification in table MSysIMEXColumns. Table MSysIMEXSpecs tells you which import/export file specification the columns belong to (in your database you should find one with SpecName= 'ARTC'). You will only see these tables if the Show System Objects box in Tools,Options is ticked. I copied the columns to an Excel file, constructed the column specifications as described in the Q155512, and then output the results to a schema.ini file.

    Initial results look good, but I have not fully tested this.


    Note that there is another article on the microsoft support site in connection with this problem, but it is does not include a VBA solution. The article is
    Q169917: Error Refreshing Text File with Linked Table Manager.

    Note that the article implies that the bug only affects Access 97. I tried my code on an Access 2000 machine, and the problem still happens. The only bit they have fixed is the GUI Linked Table Manager (which is useless if you are writing VBA!).

    Let me know if you try the schema.ini solution, and how you get on.

    Good Luck!!

    Angela

  7. #7
    Join Date
    Sep 2003
    Posts
    6
    Have you guys found a solution to that 3625 Error Message Problem?
    I have found out partially what's with that.
    Wenn I try to connect I get that error message unless I have been to the External Data -> Link Table Wizard before.
    If I open my Database and been there then my VBA Code works just fine.
    However I have played more around and found out the following. WLib Wizard I disabled by deleting the MDA. Then When I click that Menupoint I get the Officefiledialog, then I select a file and klick connect then I get an Error since the link table wizard isnt there anymore.
    Now my VBA Code works anyway. So its not the Wizard for Table linking doing something to the state of my Database but the Menupoint asking for the Officefiledialog, however after the dialog. Since when I cancel the dialog my VBA code doesn't work.
    So where is that code. I cant call that menupoint from within the VBA Code. Why not? It's not an intrinsic Menupoint, however it doesn't belong to the add-in entirely too. Since the Officefiledialog (which is also used heavily in access add-ins provided by MS) is called outside the add-ins and whatever else is done that registers or sets flags on the import specs. Which database property is that? What object is initialized? Well I tried to watch the currentdb object whilest doing the whole thing. However the Tabledefs aren't touched. Database properties I couldn't find one that is flagged differently. one thing I haven't tried is to refresh indiezes. Is it maybe possible that those tables containing the specs are indexed and whenever looking for those specs the index is searched? Well I checked. there are indiezes. But how can I check on their contents and their last renewal? Is it possible to renew indiezes ?
    Any other suggestions how to find out what the menupoint does after officefiledialog before calling the add-in?
    In "Macros" I can add a Command to call the LinkTables command which calls then the menupoints event or something.
    What is that command? Its not in any module nor in any wizard or add-in in the background. Did I miss something? Where can I look into to find that. I just wanna do as MS does. Schema.ini is unacceptable. I want everything in the DB. Schema.ini to provide temporarily is not good either since the DB user my not have write rights in the folder where the file is read from. to copy that and then...
    Its all just workaround but not really a problem solved.
    There is a solution I can smell it. Just the box is locked. Someone there with a key?
    Hidden Database properties, objects? Anything else not visible in the object model? Anything else undocumented like Syscmd 555? Doesn't help but maybe something like SysCmd 'xxx'? How can we find out?
    Please help me. I am running out of time but really need the perfect, fully automatic solution

  8. #8
    Join Date
    Sep 2003
    Posts
    6
    If so::::::

    Note that the article implies that the bug only affects Access 97. I tried my code on an Access 2000 machine, and the problem still happens. The only bit they have fixed is the GUI Linked Table Manager (which is useless if you are writing VBA!).

    :::then
    have a look inside that.
    The Linked Table Manager Code is in the MDE/MDA files of those wizards in the office directory.
    If they have fixed it there then its a piece of code we can use too.
    If you have those MDE/MDA files for Access 2000 please let us know what is new there in comparisson to Access 97
    Philipp

  9. #9
    Join Date
    Sep 2003
    Posts
    6

    Had a look into it again

    Unfortunately the MDEs for Access2k and AccessXP are locked and no source code is available.
    But one can see that they are not using the officefiledialog anymore but the commondialog for opening the files but that's it. However it answers one question. It is a problem solvable in VBA since the Table Link Manager is written totally in VBA and if its fixed now they've done it in VBA. I hope someone reads this and finds a way.

  10. #10
    Join Date
    Sep 2003
    Posts
    6

    SOLUTION IS A WORKAROUND

    '_________________________________________________ _
    '_________________________________________________ _
    '''''''''''''''''''''''''''''''''''''''''''''''''' '
    'Creates and uses a File, then opens the wizard. Aborts the wizard and deletes the file.
    'Now you can use the specs.
    'Works like a charm for me. I hope it helps you guys too.
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim filename1 As String
    Dim filepath1 As String
    filepath1 = "C:\"
    Dim ts As TextStream
    filename1 = "tmp" & Format(Now, "ns") & ".txt"
    filepath1 = filepath1 & filename1
    Set ts = fso.CreateTextFile(filepath1, True, False)
    ts.WriteLine ("0;0;0")
    ts.Close
    Set ts = Nothing
    SendKeys "%{d}xe" & filepath1 & "{Enter}{Esc}", True
    fso.DeleteFile filepath1, True
    Set fso = Nothing

    '''''''''''''''''''''''''''''''''''''''''''''''''' ''
    '_________________________________________________ __
    '_________________________________________________ __

  11. #11
    Join Date
    Sep 2003
    Posts
    6

    I have to add to the above solution

    This works only with the German Version of Access.
    For Englisch Versions you have to modify the sendkeys statement in a way that it opens the linking wizard under external data in the filemenu.

Posting Permissions

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