Create access table based on filenames in a specified folder Results

Hi folks,

I have a requirement to automatically populate a table based on the contents of a specified folder.

The file-names follow a protocol of the below -

180936-556849.doc

The extension can be different, e.g. docx or pdf, so the action would be to

1 - Find the directory
2 - Clear the access table
3 - Use code to populate the table with the split out file-name in separated fields.

So I'd need an end result that looked like -

Field Names:
URN/Comm_Number/File_Extension

Data:
180936/556849/doc

I'd found a database entitled Browsing.mdb from a user (my apologies I've forgotten his name!) but it only displays the information as a report not as an auto populate to a table.

Any help gratefully received!

The environment

I have 1 application that runs on dBASE4
I have another application we subscribe to that runs online and requires tab delimited text file input nightly
The only front end we have to dBASE4 is through the application and they don't support scheduling queries with joins

Proposed solution

Since Access links to and imports from dBASE4 and we do have a front end to Access, it made sense to use it as a courier, so to speak, between the 2 applications.
One assumption here is that no programming would be required, just clicking on Access functions (I haven't programmed for many years)
All of this needs to be done automatically overnight

The problems are numerous, based on my lack of experience with Access

We are a school and have limited speed on our network. To minimize traffic and time there are a couple of options I wanted to consider
Question 1: Should I run this before or after the dBASE4 database is backed up and cleaned up? What factors should I consider?

Question 2: Should I link to the original tables, link to copies of the tables, import to the original tables, or import copies of the original tables? The database is not being used at night, but I thought the advantage of working with copies of the tables might be that, once the copies are made, the rest can process while the dBASE4 clean up is being done.

Question 3: Does it matter where the input tables, copied tables, Access application, output files are in relation to each other? Which must be in separate folders, which should be in separate folders, how far apart should they be and why? The output files need to be put on another server. Does Access have a problem with that or do I need another DOS step?

The rest of my questions are about fixing things done wrong...

Question 4: I created a macro to import the tables to Access using TransferDatabase actions.
But I keep on getting the message “The Microsoft Jet database engine cannot open the file ‘C:xxxxyyyy’. It is already opened exclusively by another user, or you need permission to view its data.”
Where ‘C:xxxxyyyy’ is the Database Name, i.e. the folder where the dBASE4 files for tables are found.
I can successfully import the tables by clicking File > Get_External_Data > Import using the same folder specifications and file names, however this would not be automatic and defeats the whole purpose of this effort.
How do I get around this problem?

Question 5: I created a macro to run the queries and file the results into a text file using TransferText actions.
The queries were all tested and were successfully run independently of the macro.
I specify the filename ‘C:xxxxyyyyzzzz.txt’
When the macro is run I get the message “Query must have at least one destination field”.
What is a destination field?
How do I get around this problem?

Question 6: Also, I can't figure out how to specify the SpecificationName to make the file tab delimited and not comma delimited. The file also has quotes which I don't want, but that may be the same problem.

I will probably find more problems once I get this part working.
I intend to go to the Control Panel Scheduled Tasks to invoke the macro once it works with a commands something like:
"c:program filesmicrosoft officeoffice11msaccess.exe" "c:xxxxAppIF.mdb" /x Run_Queries
First one delete the old tables in Access, second one to import or link to the new tables, third one to run the queries & update the output files

I hope this posting did not break too many rules
Thanks for your help
Bill@cunyprep


Not finding an answer? Try a Google search.