Linked CSV file wont use link specification

[Kained]

Hi there,

Am hoping someone can shine a light on my problem as this has me ripping my hair out.

first off I've set up a subform in my main form to display a query using a linked table as the data(a excel exported csv file). nothing special.

however, I have had to use a link specification to determine how the table is linked. i.e. what fields should be defined as double (as access treats them as integers for some reason even though the numbers are too big!) and which fields need renaming.
without this link specification, the data will not be handled correctly.
ok no problem. I've had this working. The table links fine.

When i came to roll out the database, I set up the security in order that multiple resources can use it at the same time.

Now when I log in and use the form I get the following error.

'Text file specification <filename> does not exist. You cannot import, export or link using the specification'

Arrgh. I used the specificition to link the table in the first place! I've tried refreshing the link using the Link Manager, I've tried re-linking it and I've also tried linking the table and creating new specifications, all with no luck. I get the same message each time.

My next step is to import the data instead(again I'd need to use a link specification). But I'd really like to solve this as I can see no reason why it shouldnt work.

Can anybody help?

 

[john471]

I have spent hours/days/weeks/months ripping my hair out over the same error message.

I found the following kludge solved my problem.

1) Create a dummy linked table that uses the same import specification, but links to a different (also dummy) file.
2) Open that table in table view, before trying to (otherwise) use the import spec.

        'Having a table open with the same import specification
        'seems to work-around the error
        'The text file specification 'MyImportSpec' does not exist.  You can't import, export, or link using the specification.
        DoCmd.OpenTable "tblDummy"
        DoCmd.Minimize

        'Do the things you need to do,
        'then, after you are done...
        DoCmd.Close acTable, "tblDummy"

Hope this helps. Let me know.

Regards

John.

 

[Kained]

Thanks for the advice.

Straight after I log into the DB it gives me the error. So, am not sure what event I can hook it to. The error is before my form opens...

cheers,

 

[john471]

Hmmmmm, interesting.

I'm assuming the main form is your start-up form, and if you set the DB to have no start up form you wouldn't get this error ?? Pls confirm.

I think the sub-form loads before the main form.

With the code I supplied above, try putting the open table and minimize lines in the OnLoad event of the Subform, and the close table in the Unload event.

Let me know how you go.

Regards

John.

 

[Kained]

Hi, thanks for your persistance...

yes. I load the main form on startup. So it must try and re-establish the links at startup using the spec.

Why?

I dont know... because if i didnt use a spec am guessing it wouldnt matter and access would just use the data as is, i.e. as specified when it was linked in the first place.

I tried the code you supplied with no luck. I also tried it with macros, again with no luck.

I figured I'd check the system tables out and see if there was an error somewhere.

The MsysObjects table specifies the correct link spec.
The MsysIMEXSpecs table has the correct header.
The MsysIMEXColumns has all the correct components with the correct ID's for the header.

Ahhgg!!... its not like its relying on a schema having to be in the same directory... (or is it!!)


---

I've just tried importing my data instead of linking to the CSV file, using the exact same specification.

This works fine. My only problem is that I have to import the data instead of just replacing the external .csv file. I wanted to avoid this to so that other users didnt have to use access to update the data. While not ideal, this will do for now.