Okay, I have a table into which I import a comma delimited text file each day. Right now this whole process is performed manually by end users in an Access front end. I want to automate the process using a Job to import the data and disperse it to the work flow tables without the end users having to do anything.
Although I still have to work out how to teach SQL to find the exact file, I have someone working with me on that one.
But I am stuck on the import/inserting of the data. I have spent hours looking through Bulk Insert and Tasks and other options. I cannot find anyway to import this data directly into the table, every thing I find has a CREATE TABLE line to start the whole process. The import table itself is emptied each day after the import, but since I was working from the front end initially, and (based on experience, not so much knowledge) Access does not seem to like temporary table. The queries to manipulate the data and disperse the data to other tables, so the table exists.
So far, I am kind of stuck. I would rather not have to rebuild every one of the steps in my Job to accommodate a temp table
Should my work to automate this process change the process in its thinking to using a temp table, would that save me a lot of frustration?
Also, I set up a task using the Task Editor, but I have no idea where it went. I can't find the Task anywhere. It is a one-time kind of thing? If not, where can I find the task and can the file to import name contain wild cards? The report drops every day with a static first part of the name, underscore, date in MMDDYY, and time in HHMMSS. Since the time is unnecessary, could the file name be something like: \networkLocation\Folder\StaticFileName_(GetDate())%.txt? There would probably be some format statements in there I am guessing to make sure SQL knew it should look for today's date in the MMDDYY format.
Thanks everyone!
