delete table in backend from frontend

[Rachael]

Hi,

Could anyone tell me the code to delete a table in a backend db from the frontend.

I have successfully mastered copying tables from the fe to the be using docmd.transferdatabase but I need to delete a table and its relationships.

Hope this is an easy one,

Thankyou,

Rachael

 

[Bello]

Rachael,

Try this:

DoCmd.RunSQL "DROP TABLE YourTableName"


Regards,
Bello

 

[Rachael]

Thanks Bello for your response,

What I want is to make the table delete in the on-open event of my splash where there is an if statement that checks the version number and does the appropriate things according to the version number of the users BE.

So, in this if statement there are usually lots of openqueries and transferdatabase bits to re-organise data and add tables to backend, but

How do I use the above code? How am I telling it where the data file is? I have tried it with my other code and to no avail.

What I had come up with so far was (and could only get to work sometimes (?) in an on-click on cmd button on a dummy form I made for testing purposes is:

Dim db as DAO.Database, i as Integer
Set db = opendatabase ("C:\Program Files\Filename.mdb")
for i=db.relations.count -1 To 0 Step -1
if (db.relations(i).Table = "Vintage") then
db.relations.delete (db.relations(i).Name)
end if
Next i
db.tabledef.delete ("Vintage")

I thought I was on the right track, but!!! I don't know!!! Can someone help? Bello, can your code replace this??


Thankyou, Rachael

 

[Jack Cowley]

Hiya Rachael!

First a caveat... I have been into a Sauvignon Blanc (wish it were an Aussie one as it is just a bit sweet)....

Sounds like you are upgrading an existing deployed database so I am not sure that I can help, but I will do what it can... I assume that you need to delete the table because it is being replaced by a table with more fields. Is that correct? What about adding the necessary fields to the existing table? Would that work?

I have some ADO code (I believe you have Access2000 or higher, rigth?) floating around here somewhere that adds a field(s) to an existing table so if that might help you just let me know and I will go on a hunt...

I hope you and your family are in top shape and that you are getting the right amount of rain for a change....

Jack

 

[Rachael]

Hi Jack,

The New Zealanders do a good job of Sauv Blanc, I personally think its a great wine but it hasn't really taken off here as it should have, don't know why! In our cool climate we grow great Sauv Blanc. The rain, well, we are getting some but still well below average and the winter so far is very mild (no snow yet!).

Anyway, yes, I am upgrading existingly (is that a word) deployed dbs. I was unaware (i think) that you could add fields to backend tables, I wish I'd known this along time ago. What I have been doing is creating a new temp table in FE, running an append, then copying the temp table to the BE via docmd.transferdatabase. My situation is now an old redundant table in the backend that had ref integrity set years ago is causing problems, so I need to get rid of it.
The code I posted earlier I have I think managed to get to work but am still anxious as I'm still learning and hope not to destroy a customers BE.

What I did was change the set db line to

set db =OpenDatabase ("VADataFile.mdb")

and then had to add the line at the bottom
set db = Currentdb

Getting rid of the full path seemed to work.

I would be very interested to know how to add fields though, hope its not too much trouble.

Thanks, the family are well, we have another baby on the way 3 months to go.

Rachael

 

[Jack Cowley]

Rachael -

GREAT NEWS! Congratulations on the happy upcoming event! I am very happy for you!!!!

Sorry about the stinkin' weather, but what is one to do? I will do a modified rain dance (the full blown one might bring too much rain to your parched vineyard) soon as this bottle of Sauvignon Blanc is done so check the rain guage in a couple of days....

Hard to find NZ Sauvignon Blanc here let alone the stuff from Australia. (Does Rosemount have a SB?) I don't know what is the matter but we are used to a less dry wine and do not know about the wonderful SB's from you and NZ. I am doing my part though!

Here is some ADO code to add a field to an existing table....

Dim Cn As ADODB.Connection, Cat As ADOX.Catalog
Dim objTable As ADOX.Table

Set Cn = New ADODB.Connection
Set Cat = New ADOX.Catalog
Set objTable = New ADOX.Table

Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDb.mdb"
Set Cat.ActiveConnection = Cn
objTable.Name = "tblMyTable"

objTable.Columns.Append "NewField", adInteger

' clean up objects
'Set objKey = Nothing
Set objTable = Nothing
Set Cat = Nothing
Cn.Close
Set Cn = Nothing

This works with a table in the existing db but I have tried it to change a table in the backend. I am sure you will need to get the path and put that in the Data Source line of the code above. I am too wobbly to fiddle with it now but when I get back from the library tomorrow I will give it a go for you.

Stay well and I have my fingers crossed that the right amount of rain is on the horizon....

Jack

 

[Rachael]

Thanks Jack,

I thought there was some problem with ADO stuff if you had previously been using DAO. Shows how much I know about things, I'm flat out knowing what they stand for, but have used them in the past to make things work. Anyway, thankyou for your efforts, this code is added to my collection for use next time I need a new field.

As I fly by the seat of my pants with all this code stuff, can you tell me what the For i=db.relations.count -1 to 0 step -1 means in english. All the previously posted code now seems to work in my splash but, now, I need to modify it to check if the table exists first as it won't work if the table doesn't exist (I have a few other redundant tables that I can now clean out of customers BE, but as I had been deleting them with each new version, there are different compliments of tables out there). Hope that makes sense, tricky stuff?? I periodically have heartattacks when I do upgrades hoping all will work.

Cheers, Rach

 

[Jack Cowley]

Rachael -

Why bother removing the unnecessary table(s)? It shouldn't hurt to just leave them there. They will not be growing so it seems to me that it is more trouble to try to remove them than to just let them be.... I understand your desire to be tidy, but is it worth one myocardial infarction after another to try to remove unneeded tables?

For i=db.relations.count -1 to 0 step -1

Assume a count of 21 relationships in your db. The code above says, for i = 21 minus 1 (or 20) to 0 step (move) -1 each loop so on the first loop i = 20 and the next step (loop) we subtract 1 (-1) so the count goes to 19. It is just like For i = 1 to 10 but in your code is it is saying from 20 to 0. It is just counting backwards. The code, as far as I know, could have read

For i = 1 to db.Relations.count - 1

That would have been the same thing... I hope I explained it so that it makes sense...

I will check my code files to see if I have any code to see if a table exists in a db, but if it were me I would be VERY tempted to forget about the tables and carry on....

Let me know what I can do to help....

Jack

 

[Rachael]

Jack, thankyou.

I had been ignoring old tables until one had started to cause trouble. It had referential integrity set in the BE (from years ago before the DB was split) and is now giving me the #%&* because it wont allow new data to be added eventhough the link is deleted and it is not referenced anywhere, so the only option is to get rid of it. I thought while I was at it I would have a clean up as I had just learnt this new trick but maybe you're right I just ignore the others!

Rach

 

[Jack Cowley]

Rach -

With the bad table in the backend how about having the user email you the backend where you can fiddle with it easily and then return it to them fixed? Seems easier than trying to do it by offering them software to do it when there is no real guarantee that it will cure the problem. I should think that as clever as you are it shouldn't take more than a few hours so that should not interupt them terribly.

Just a thought as I always want to do everything the easy way! And I have every confidence that you will sort it all out, but I just hate for you to have to work too hard...

I believe you have my email address so email me directly if it will be easier for you....

Jack

 

[Rachael]

Jack,

Easier said than done, I have 100 clients and a fair majority don't even know where to find files on there computer, some don't have email, and most get very confused with the whole situation, asking for a file to be emailed would be like asking for them to climb Mt Everest.

Anyway, hopefully the code listed above will work, it works on my computer, now I start upgrading one then a few at a time to get there feedback.

Fly by the seat of your pants! that seems to be my game.

Rach

 

[Jack Cowley]

Rach -

And flying very well I might add!

I understand your predicament with your clients so I will stand by and if I can be of assistance just let me know.

Continued success with your database and congratulations again on the upcoming addition to your family!!!

Jack