Remove table from database within project

[gregjohn]

Over the past couple of years, the number of tables within a database has grown (probably about 50). I would like to clean it up, but as I try to delete tables from the database (either through modify database or deleting from my project), it seems to take forever for the table to be deleted (like 1/2 hour). Eventually, it is deleted. I don't know whether it matters, but the data is stored on a network drive. Any ideas on why it takes so long and if I can reduce the time it takes to delete a table?

Thanks

 

[rgbean]

Deleting a table shouldn't take anytime - unless it's part of a persistant relationship for RI in the database. Are you sure that the table and the database are not currently in use? Both they and any related tables must be able to be opened exclusively to allow these changes to happen. What OS is the network fileserver running?

Rick

 

[gregjohn]

No persistent relationships between tables. Not sure what you mean by "RI in the database". Windows NT is the server OS. Within my preferences, my open exclusive box is checked so, I believe, the tables and database are exclusively opened.

Even so, I opened the project and issued close tables all and close databases all and tried to delete a table with the same type of delay.

Prior to issuing the command, the window in the toolbar that lists the open database was blank. Once I issued the command the window changed to include the database name of the table that was being deleted (I assume this is "correct&quot.

 

[rgbean]

Oops, sorry RI is Referential Integrity, the main reason you setup persistant relationships. NT (hopefully 4.0 SP6a), I believe has a default configuration that has a timeout on files opened in it's cache, so when you close them shared, it's a while before you can really reopen them exclusive.

As a test, copy the database and tables (or at least a developer sized version of them) to your local system, and try the delete table - I believe you'll find it won't take any time at all!

Another question, are you using any source control on the project (e.g. Visual SourceSafe)?

Rick

 

[gregjohn]

No source control on the project. Also, I have already tried copying the project from the network to a local workstation to delete the table. Same (long wait) result. Local workstation uses win2000 as its OS. Server is NT 4.0 (not sure what service pack).

Not sure if this will be helpful but when I first issue the modify project command, it can take a while (maybe a minute or so) before the project is loaded and is available for manipulation (I am in a 2 person office). Then, if I want to go to the data tab, I have to wait some more while the project manager figures out what tables are included in the database. I would think this would be quicker.... To your knowledge, is there any limit to a project size (either in mb or number of objects?)

 

[AllanB1]

GregJohn:

I've seen this problem also. I'll bet the table and/or database is already open, while there's no good way to determine who has it open.

Al

 

[gregjohn]

Al,

I was trying to integrate crystal reports with database tables and basically could not do it because foxpro did not close the database "completely" with the close database command (ended up having to use free tables). No one else in the office shoud be accessing this database but me. Although, I bounce around several computers and if the database does not close properly on computer A maybe that causes the problem with computer B. I would think rebooting the server would solve the issue, but it has not.

 

[rgbean]

Unless you are using source control, I've never seen a good reason to include databases in a project - except for documentation! It just adds a lot of records to the project it's got to read in each time you open the Project, so it can display them if you click on that tab.

Do you include any of the tables in the .EXE? (i.e. They aren't marked exclude in the project.)

Rick

 

[gregjohn]

I am not creating an exe. The project is simply used so that I can keep programs, forms, reports, etc, organized. Keeping the databases in the projects is nice so I can easily view the tables, but, I think to your point, that is where I am having the "problem". While I do not necessarily have a lot of tables, each table may have 5-6,000 records. Could 300,000 records be the cause? However, why, if I removed the database from the project and tried to remove tables programically, would that make the table deletion go faster. Through your experience, any idea what the "issue" might be between the project and the database?

 

[rgbean]

Well I did some checking, and since the project file table (.PJX) is only increased by one record when you add a database (.DBC), I'm going to have to revise some of my thinking in the DBC vs. Project area. I still have no explanation as to why it takes so long to remove a table from the database.

Do you use long field names for your table columns? This would require some renaming of fields if multiple columns were named the same for 10 or more characters.

Have you cleaned up the project and/or database? (These effectively open the "tables" exclusive and PACK the deleted records.)

Why aren't you creating an .EXE? What do you use? Is the application only run from developer installed copies of VFP? (On a large network, this can be very expensive!)

Rick

 

[wgcs]

What mechanism do you use for deleting the table? DROP TABLE, or DELETE FROM tablename?

 

[gregjohn]

Yes, I use long field names. My original plan was to copy the "old" tables into a "olddata" database. That took a long time as well, so I went to removing the tables from the database.

As far as cleanin up the database, there really wont be any deleted but not packed records. There may be a few extra tables, but the table records are usually kept "cleaned up".

As far as your exe question, I am in a two person office (with me being the sole pgmr) so I have not found the need to create exe's.

WGCS:
I have tried to ways to remove the tables. The first is to simply go to the data tab in the project manager, highlight the table and select remove. The message about converting long to short names pops up, I hit ok and then I wait.

I have also tried to do this programically with the same (waiting) result.

my code is generally similar to:

if indbc(testtable, "table&quot = .T.
remove table testtable delete
endif

I have not tried the drop table or delete from.

Greg

 

[wgcs]

Ok, there's your problem: If you are "removing" but not killing the table, then it will COPY the entire DBF file (which is what takes all the time) in order to change the format from VFP (w/ long field names) to a VFP (w/o).

If you use the command window, and issue the commands:
SET SAFETY OFF
DROP TABLE "tablename"

See if that happens quicker. (the DBF still won't get deleted, and it may still end up copying... but we can only try!)

 

[wgcs]

Actually, I just checked, and the SET SAFETY setting isn't necessary, and I'm pretty sure the table will get deleted outright, not copied.

 

[gregjohn]

tried the drop table and still did not work quickly. After about 1/2 I killed the program. Also, isnt remove table delete the same as drop table?

 

[wgcs]

I would assume it's the same, But it was just another approach to try.

 

[gregjohn]

Good news, I was able to resolve the issue. Thanks for your help. I was doing some more checking on the microsoft site and they had a couple of ideas I did not try.

1.) vaidate database exclusive recover
2). project - cleanup project (including rebuild option).

I think the cleanup project is the one that "solved" the issue (ie when I noticed the change), but one might have to validate database prior -- probably a good idea anyway.

Greg