Encounter 'File Does Not Exist' error on ALTER Table command

[Phil M]

In running an upgrade app for our software on a client's system, there is a process to check the table structures and build an Alter Table statement to execute on the table for adding/updating structure details.

On this client's system, when the Alter Table statement is executed, an error message of "File Does Not Exist" is encountered. The alter table statement generated to be executed is:

ALTER TABLE SCH ADD COLUMN PID Char(24) ADD COLUMN SchId Integer​

The statement is stored to a variable (per building the command) so it only needs to be executed once when multiple fields are to be added/altered. It is then executed via a macro statement : &lc_altercmd

[ul]
[li]The process for building/executing the alter command is applied to data schemas for up to 13 other modules prior to this particular module/table at issue. In the case of this client, there are three modules.[/li]

[li]We have created a scaled-down, module specific copy of the upgrade app so that it only performs the schema update for the specific module. When that app is executed, no problems are encountered. (though unlikely, this may indicate issue is influenced by something in full app prior to the current module)[/li]

[li]We have copied the client's data set to our system for troubleshooting and when the full application is run on the data, no issue is encountered.[/li]

[li]The upgrade app has been run on multiple client systems without issue.[/li]
[/ul]

The VFP help on the Alter Table command states "ALTER TABLE might rebuild the table by creating a new table header and appending records to the table header".
This may be where the dbf file is 'deleted'.

The client is using an old(er) server. We suspect that the issue is greatly influenced by the (slow/lack of) response by the system while the alter command is executed. It could also be influenced by whatever has been installed on the server per security/configurations etc. (our system person is out of office today, so I can't find out if we have an older server available to test with)

Any suggestion of what else to look at will be helpful.

Phil

 

[Mike Lewis]

Phil,

My first thought is that this is a permissions issue. If VFP needs to delete and recreate the entire file (not just the header), and if the file is in a folder withouth the relevant permissions, that might possibly explain why it doesn't work, although it wouldn't explain that particular error message.

The other possibility is that the table literally does not exist - at least, no in the location where VFP is looking for it. If the table is contained in a database, the database must be in the VFP search path. If it is a free table, the DBF must be in the search path.

You can check that by inserting a MessageBox just before the ALTER TABLE, displaying the result of FILE("Sch") and FULLPATH("Sch").

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Phil M]

I like the permissions suggestion....

The table is in a DBC. If the DBC for the module does not exist, we exit the method immediately to move on to the next module.

The DBC is opened with full drive/path specification. Then the table is opened exclusively with the 'dbc!table' specification. So we definitely know the dbc and dbf file exist and the files are opened from the correct location on server. Additionally, in viewing table structure and looking at the details in the Table tab, it shows the correct drive/path and name of the DBC.

We do an AFIELDS() command to get current structure details to determine which fields need to be included in the Alter Table command, if any, so we know that is not root cause of the issue.

All these details are captured into a log file via the error handler we implemented. The log file shows all drive/path for the tables, SET PATH specification, etc. are correctly referencing the production location.

Thanks

 

[Mike Lewis]

It looks like you've got the location issue pretty well covered. From what you said, it's clear that VFP is indeed able to locate the table. So the next step is to check the permissions - unless anyone has another idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

Any chance there's a space somewhere in the file path and that your code isn't wrapping that path in quotes?

Tamar

 

[Phil M]

No. That is one of the first things we checked. Additionally, as I stated previously, there are a couple other modules which have been processed with the same methodology prior to the module at issue with no issues encountered in those modules.

(I have made a version of the app to test run on client's system with function to capture memory / environment info to a text file immediately prior to the problem section of code. Will let you know what comes of it)

Phil

 

[Nigel Gomm]

Phil,

see if you get an error when you try to putfile() to the same folder. It maybe someone has switched on windows anti-ransomware.

see Link

hth

n

 

[Chris Miller]

I don't know if it's worth to make a shot into the dark, but do you open the DBC exclsive, too, or just the table you want to alter?

Chriss

 

[Mike Lewis]

do you open the DBC exclsive, too, or just the table you want to alter?

I wondered about that too. But I just did a quick test, and it worked OK.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike,

makes sense. I know several things about a dbf are stoed in a dbc, like long field names, but that's all just metadata and can of course also be changed if the dbc is only used shared.
I just wonder, if in some circumstances you get into problems like when using viewsfrom a shared dbc can cause error 1709 - Database object is being used by someone else.

I know this is about ERROR 1, 'thedbfname'. But it might have the same root reasons and is not always happeining.

I just do use a DCB excludive first, which is a first step to know I am in the VIP area of working alone on the database and then also all related dbfs. If you add database alterations to an application itself that's only a good idea for a local datbase you know is not shared, like local data of each POS cashbox, for example.

Chriss

 

[Phil M]

and now, the rest of the story.......

In tracing the code, I found that the text string used in specifying the path of the dbc to be opened contained a double backslash in the string, i.e. 'data\\module.dbc'. Although this did not appear to be fatal, it may have contributed to it. Upon correcting that line of code, the app ran completely without issue.

(note to Tamara's space question, to allow clients to specify a UNC for data path's (per security), which frequently include spaces, we had to change use of drive/path macro from using the ampersand to enclosing such text in parenthesis)​

Per Mike's suggestion for the messagebox(), we having a tool to be able to gather data/environment details to a log file when a hot-key is pressed. I implemented that function in the app to be executed immediately prior to the execution of the Alter Table command, via the Keyboard command. The details provide confirmation that the DBC and DBF files opened with the correct drive/path specification.

(To answer Chris' question, we only open the table exclusively)
Additionally, we have a mechanism that while the upgrade app is running, users can not get into the main application.​

Thanks for your help.

Phil

 

[Mike Lewis]

Good to hear that you found the solution, Phil. And thanks for letting us know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads