Delete and Replace Access Table in VB6?

[ausmoran]

I am a complete novice and in serious of need of help. I have to figure out how to delete a table in an access database and replace it with a new one using Visual Basic 6. I would be very appreciative if someone could give me a hand or point me in the right direction on how to do this.

The table has a field that is linked to another table, so I also need to reestablish the link once I replace the old table with the new one.

Thanks in advance,
Austin

 

[MikeCox]

When you say 'linked to another table' do you mean a relationship? If so, I don't think it's possible to add/remove a relationship through code, but then again I haven't seen everything yet. You can use SQL queries to create a table, and if I hadn't left my book at home today I'd be able to verify that there's an SQL command to delete a table (actually it archives the records into another table, which is effectively the same thing). Dig around the inet for some SQL info, you should find what you're looking for. If you just need to migrate existing data into another table, you can use this code as an example after you make the new table:
--------------------------
'Using 2 Adodc data controls
Dim Ctr As Long 'Field counter
AdoOld.Recordset.MoveFirst

Do While Not AdoOld.Recordset.EOF
AdoNew.Recordset.AddNew
For Ctr = 0 To AdoOld.Recordset.Fields.Count - 1
If Not IsNull(AdoOld.Recordset.Fields(Ctr).Value) Then
AdoNew.Recordset.Fields(Ctr).Value = AdoOld.Recordset.Fields(Ctr).Value
End If
Next Ctr
AdoNew.Recordset.Update
Loop

HTH!

~Mike
Now and then it's good to pause in our
pursuit of happiness and just be happy.

- Guillaume Apollinaire