Unanswered: Problems importing .tab file into Access 2002
I have an access database that needs to automatically import a file from a webserver into a table. The file is stored on the webserver as a .tab (text file). I have tried to open it in excel, and it has no problems. I can open the page in my browser and save the source with no problems, but when i try to import it in Access 2002 i get the following message, "connecting to web server" then after 5 seconds i get "internal internet failure".
I have looked in my registry and .tab is allowed as a text import file for access. I have ran all office updates.
I have two questions.
1. What would be some sample code for me to be able to automatically import the file in vba. I could put it into a module then run the import module at startup.
2.Anyone have any suggestions on how i can get the file down into access, what i can try? I would really hate to have to run a vb code outside of access, to open excel, pull down and save in excel, then to import into access, i would prefer to run all in access.
Thanks for all or any input and data.
Is the problem with the import method, or file access?
To check, place the file on your hard-drive, in say "C:\TempTest" and get it to work from there. Once you have the import method nailed, then work on accessing it remotely.
If the file is the same format everytime, you should be able to use a shema file and have Access automatically import it.
Several code methods would involve opening the file directly and parsing the data by looking for the tab characters on each line (can be a bit bulky, but reliable). Another option is to open it in Excel and then pulling the data from the worksheet (this can be done using Automation - the code uses Excel to open and convert the file). This moves the task of parsing the data to Excel and out of you code, but then you have to get the Automation working.
I'll generally use the latter - make Excel do the work for you.
Definitely move the file to your local drive to make sure there are no permission issues. But after that I would suggest either File/Get External Data/Link Tables or Import. Select the file then press the Advanced button, here you can tell Access the file is a tab delimited file. You can also give meaningful names to the fields and save the File Specification for future use. It will take you 10 minutes to figure out and code. The other options will take much longer. Once you have a feel for setting up the File Specification, look at the TransferText method of the DoCmd object. If you have a saved File Specification, then the code you need for importing or linking the file in the future is one line long.
If you are having trouble creating the code, try creating a macro first. Do the TransferText function, get the parameters setup and working, then convert the macro to code.