is it possible to link a .csv automatically?

[Nate1749]

I'm trying to do linking automatically through a macro. I'm running into 3 problems though.

1) the file is a .csv file, which does not seem to be supported, if I however change it to a .xls it solves this problem, but I would really like it to work with a .csv file because I'm trying to make it as user-friendly as possible.

2) access is automatically changing the field types (I changed csv to a xls to get this far). It seems to be guessing, the problem is it's messing with some of my formulas, plus I want them all to be text, except for two columns that I want to be numbers.

3) everytime I relink the file it's creating a whole new table, rather than re-writing the one I assigned it in the macro. I assigned it the name tblOBTReports, however, if I run it once it will name it that, running it again will create tblOBTReports2, and so on and so forth. I want it to just re-write over the tblOBTReports that I assigned it.

Is all of the above at all possible? I'm assuming it might be, but not through a macro...

-Nate

 

[Nate1749]

I also forgot that the csv file is obviously delimited by commas, and when I link it manually I set the qualifier as a quote, problem #4 would be setting the text qualifier as "

 

[psemianonymous]

1. As far as I know, there's no fix for the "unsupported file extension" problem. I'd recommend you rename the file instead to .TXT, because that's what it is--at least moreso than it is an XL file.

2. I don't know.

3. Aha! I don't think you're linking to the text file (as in directly reading from the CSV file when you need data), you're IMPORTING the data to an Access table. With this in mind, YES, I have an answer...


First: What you do is set up the table with your data types as you like them.

THEN, in your macro, run a query that deletes all items from the table before running the import. This way you empty the table, but keep the table structure as you like it (i.e. all text fields except for 2). Hopefully this will fix your problem.


--
If you are actually linking to the text file (as in ODBC->text file, comma-separated), then I can't help you, though I believe you can set up a File DSN to set up the fields as you want. But I'm not 100% on that.