I need some assistance from those of you smarter than I.
I have two database files (not two tables in the same database file) one represents information delivered from an external interface, and the other is used by the internal interface associated with the processing half of my application. Data is received (i.e. fields are populated) from the external interface to one database file and that data is then copied to another database file where the information will be processed by the internal interface. This is being done because I am worried about file access issues that might occur if two different interfaces (external & internal) try to access the same file at the same time (without dealing with semaphores). I am not too worried about the sanctity of the data since each record supplied from the external interface should not effect any of the data being used by the internal interface. So the first question is , am I being to primitive in my thinking assuming that I would run into file access issues if I were to just use two tables in the same database file for the same purpose ?
My second question is related to my current implementation where I have two separate database files representing the external and internal interface data. Currently when a new set of data is entered into the External Interface Database file, an alert is issued, and another task copies the data from that file into Internal Interface Database, then the data is deleted from the External Interface Database file. My question is related to the syntax that I am using to provide the described functionality. I am currently using a INSERT with a subquery to the External Interface Database file i.e. INSERT INTO InternalDB FROM ExternalDB.ExternalTable WHERE Order = '06306049'; .
My question is when I run this query with both database files in same directory I get an error stating that the ODBC driver can not find the ExternalDB.ExternalTable in the Parent Directory of the subdirectory where the files are actually located. The system DSN is set up correctly for the ExternalDB, and if I move it to the parent directory the query works correctly, my question is why would I have to put the two database files in seperate directories for this to work and is there a way to avoid this ?