Please Help Me!!!!!!!!!!!!!

[General_Jack_As]

Ok here is the deal. I have an excel spreadsheat that automatically updates from a program here at work. I need to pull that information into an access database via a linked table. However I cannot, either by ADO or Data Enviroment, use that linked table or a query based on some of that information. If anyone can help me I would really appreciate it.

Thanks,
Tim

[anonaconda]

Are you working with a fixed filename? (linking to a variable filename with VB is possible but tedious... best to find another solution).

Is your excel up-to-date with the remote data source? Working with a linked file from Access will not trigger an update of the XLS

If fixed filename, you should be able to link (file/get external data/link ...advanced, delete all the find "criteria" to get to *.*, find, dbl-click your file, follow the wizard with field definitions etc, and you are linked). This should be straightforward with .CSV or a single-sheet .XLS

Once you are linked, you should be able to do anything relating to retrieving data from your linked file as it was when last excel-saved, but you WILL have issues if you do anything (in code or via menu) that Access interprets as an attempt modify the structure of the linked table.

Please give more data: what you are trying to do and where/how exactly it goes wrong.

'conda

[General_Jack_As]

This is it, I have a file that is continously updated from another program at work here. That program is made by UPS Logistics and is called DTS - Syntelic. It updates to the .csv file. I can successfully link the file into access but it is reading that file into vb that is the problem. Neither does the data enviroment or msflex grid or ADO nor does DAO recognize it. It just isn't listed as a data source. For some reason as far as I can tell VB doesn't like the linked table. It also does not like queries based on that linked table. I hope I am making myself clear about all this. If you are able to help me I would greatly appreciate it.

Thanks,
Tim

[General_Jack_As]

Also its a fixed file name.

[anonaconda]

Interesting!

A lot of boring diagnostic steps follow.

Having linked your CSV:

1. Can you see the linked CSV in Access table view?
2. ...and do you get something with a right-click/properties on the linked file?

2. Can you open the linked CSV in Access table view?
3. ...and does it look kosher (column headers are reasonably simple text, text values are text, numerics are numbers with thou separators and decimal markers to match your settings, dates are dates, etc)?

4. In Query view, New, DesignView, ShowTable can you see your linked CSV?
5. ...and can you ADD it (then close the ShowTable dialog)
6. ...and can you double-click the * in your CSV
7. ...and can you "!" (run) the query and get the same stuff as you got in 3.

If (you got stuck somewhere on this list) then
goto 8
else
goto 9
endif

8. Make a dumb copy of your "continously updated" CSV under a new name and in a different subdirectory. Verify that this copy is really and truly dumb (i.e. it doesn't update when your real CSV updates), and then try 1...7 again. If you still get stuck, goto 10

9. Repeat 4, 5, 6, then Query/MakeTableQuery... and give the new table a name of some sort, and "!". Assuming this gives you the new table, try your manipulations on this new table instead of the linked CSV. If you still get stuck, goto 10

10. File/GetExternalData/Import the file instead of linking it. And (you guessed it) try 1...7 again. If this is OK, try your manipulations on the imported file.

Let me know.

'conda

I'm Access-2000 SR-1 & Win2000 SP-2: I hope this doesn't make too much difference vs your setup.

[anonaconda]

:o

For 8. you need to link to the new dumb-copy CSV first before you repeat the diagnostics.

'conda

[General_Jack_As]

Yes everything in access works the way it should. The file links properly, i can make queries from it and everything. It all works fine. I can do all that you listed it is a normal linked table. and it isn't just that linked table. I tried the dummy copy of it to see if it was something in the .csv file. It isn't its any linked table or information (i.e. queries). If I do the import then it works, however, it defeats the purpose of the automatically updated .csv file. Everytime the csv file is updated then I would need to reimport it. I have a solution and I think it will work. I am going to try to put a macro on a button. If you have any solutions with either of these then I really appreciate it. Thanks in advance for all your help.


Tim

[anonaconda]

WORKAROUND 1
If you can successfully run the queries from Access query mode: create & save three queries in query view:
-MakeTable * from your linked CSV to tbl.yourtable
-Delete * from tbl.yourtable
-Append * from your linked CSV to tbl.yourtable
You need the Make once only to set up the table structure.
A button_click or autoexec runs Delete then Append to refresh your data.

WORKAROUND 2
More space-efficient is to import from CSV to table. Do a one-time manual import first to set-up yourTable, Then button_click or autoexec the code:
Sub grabCSV()
DoCmd.DeleteObject acTable, "yourTable"
DoCmd.TransferText TransferType:=acImportDelim, TableName:= "yourTable", FileName:="c:\path\file.CSV", HasFieldHeadings:=True
End Sub
The specificationname parameter of the TransferText method (see help) will be needed if you are changing field names or specifying types.

NOTE that delete/import will screw up any relationships, so if you have some, you will need to rebuild them in code. The canned-query approach will preserve relationships (but take care with referential integrity settings)

For either workaround, be sure to populate your code with lots of error trapping: sooner or later you will try to refresh your data at the same time as the external app is updating the CSV. Hopefully the external app also has error trapping and will not die if your app is grabbing the CSV when it tries to update!!!!!

I thought your problem related to you and the remote app both accessing the CSV: "multi-user" access to a text file is always bad news. Now you tell me that you can't work from code with the linked dumb copy or any opther linked file. Needs investigation!

'conda

[General_Jack_As]

Thanks for all your help. I appreciate it. I tried doing the linked file in the code also. didn't work. I tried manually importing the sql from the query into the msflexgrid and that didn't work either.

I will keep trying thanks.


Tim

[anonaconda]

OK, after this one last comment, I give up.
Good luck with your research.

You said earlier: "If I do the import then it works, however, it defeats the purpose of the automatically updated .csv file. Everytime the csv file is updated then I would need to reimport it."

WORKAROUND-2 automates the import.

I don't think you are going to succeed with real-time updates from the CSV into Access: on-demand updates should be achievable.

'conda

[General_Jack_As]

Thanks for all your help. I think your right in the way of thinking that the updates have to be updated and can't be from a linked file. I appreciate the help. What I am going to do is write a macro that takes the information from that linked table and rewrites it into another table. This table will be able be linked. the thing is that I have to figure out how to run macros from VB. If you can help me with that then I would greatly appreciate it. If not I am sure that I will figure it out.

Thanks again,

Tim