Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Remove table from database within project

gregjohn (TechnicalUser)
26 Feb 03 15:39
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 (Programmer)
26 Feb 03 15:56
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 (TechnicalUser)
26 Feb 03 17:40
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").  

rgbean (Programmer)
26 Feb 03 17:50
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 (TechnicalUser)
26 Feb 03 18:47
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 (Programmer)
26 Feb 03 19:24
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 (TechnicalUser)
26 Feb 03 23:51
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 (Programmer)
27 Feb 03 13:38
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 (TechnicalUser)
27 Feb 03 17:06
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 (Programmer)
27 Feb 03 17:36
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 (Programmer)
27 Feb 03 17:44
What mechanism do you use for deleting the table? DROP TABLE, or DELETE FROM tablename?
gregjohn (TechnicalUser)
27 Feb 03 21:43
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") = .T.
    remove table testtable delete
endif

I have not tried the drop table or delete from.

Greg

wgcs (Programmer)
28 Feb 03 8:43
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 (Programmer)
28 Feb 03 8:44
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 (TechnicalUser)
28 Feb 03 16:14
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 (Programmer)
28 Feb 03 18:35
I would assume it's the same, But it was just another approach to try.
gregjohn (TechnicalUser)
2 Mar 03 16:30
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close