Hello,

I am trying to automate the database update process. I created a macro to
delete all the tables in the database. However since there are linked tables
it stops part way through. From browsing other questions I found the code
below to delete all my relationships. I have no inkling whatsever on how to
use VBA. I opened a module and pasted this code in there. But when I try to
use the runcode in my macro this string is not available. How do I get this
into a macro?

Sub KillAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim inti As Integer
Set db = DBEngine(0)(0)
For inti = db.Relations.Count - 1 To 0 Step -1
Set rel = db.Relations(inti)
Debug.Print "Deleting relation "; rel.Name, rel.Table,
rel.ForeignTable
db.Relations.Delete rel.Name
Next inti
End Sub

Thanks in advance,

--
Denise

Sorry there are no linked tables, I meant to say the code won't run because
of the existing relationships.
--
Denise


"Denise Pollock" wrote:

> Hello,
>
> I am trying to automate the database update process. I created a macro to
> delete all the tables in the database. However since there are linked tables
> it stops part way through. From browsing other questions I found the code
> below to delete all my relationships. I have no inkling whatsever on how to
> use VBA. I opened a module and pasted this code in there. But when I try to
> use the runcode in my macro this string is not available. How do I get this
> into a macro?
>
> Sub KillAllRelations()
> Dim db As DAO.Database
> Dim rel As Relation
> Dim inti As Integer
> Set db = DBEngine(0)(0)
> For inti = db.Relations.Count - 1 To 0 Step -1
> Set rel = db.Relations(inti)
> Debug.Print "Deleting relation "; rel.Name, rel.Table,
> rel.ForeignTable
> db.Relations.Delete rel.Name
> Next inti
> End Sub
>
> Thanks in advance,
>
> --
> Denise

Why would you need to delete all of the tables? Simply create a new
database.

I'm suspecting that your application isn't split into a front-end
(containing the queries, forms, reports, macros and modules), linked to a
back-end (containing the tables and relationships) as it should be.

With that setup, you can delete the linked tables from the front-end
database: they're simply pointers to the real tables in the back-end.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Denise Pollock" <(E-Mail Removed)> wrote in message
news:3C566FE6-A34E-4D6B-B7E7-(E-Mail Removed)...
> Hello,
>
> I am trying to automate the database update process. I created a macro to
> delete all the tables in the database. However since there are linked
> tables
> it stops part way through. From browsing other questions I found the code
> below to delete all my relationships. I have no inkling whatsever on how
> to
> use VBA. I opened a module and pasted this code in there. But when I try
> to
> use the runcode in my macro this string is not available. How do I get
> this
> into a macro?
>
> Sub KillAllRelations()
> Dim db As DAO.Database
> Dim rel As Relation
> Dim inti As Integer
> Set db = DBEngine(0)(0)
> For inti = db.Relations.Count - 1 To 0 Step -1
> Set rel = db.Relations(inti)
> Debug.Print "Deleting relation "; rel.Name, rel.Table,
> rel.ForeignTable
> db.Relations.Delete rel.Name
> Next inti
> End Sub
>
> Thanks in advance,
>
> --
> Denise

I already tried to split it to a front end back end database. It lagged up
the database so bad it took 5 mins to open each form. So instead of linking
the tables. I want to create one button that I can press that will delete
all the relationships, delete all the tables then open the import window. If
you can tell me how to make the linked database work without taking so long
to open forms I am fine with that too. Its being accessed over a server by
over 50 members. It was fine when I was testing it on my desktop but as soon
as I moved it to the server it was too slow.
--
Denise


"Douglas J. Steele" wrote:

> Why would you need to delete all of the tables? Simply create a new
> database.
>
> I'm suspecting that your application isn't split into a front-end
> (containing the queries, forms, reports, macros and modules), linked to a
> back-end (containing the tables and relationships) as it should be.
>
> With that setup, you can delete the linked tables from the front-end
> database: they're simply pointers to the real tables in the back-end.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Denise Pollock" <(E-Mail Removed)> wrote in message
> news:3C566FE6-A34E-4D6B-B7E7-(E-Mail Removed)...
> > Hello,
> >
> > I am trying to automate the database update process. I created a macro to
> > delete all the tables in the database. However since there are linked
> > tables
> > it stops part way through. From browsing other questions I found the code
> > below to delete all my relationships. I have no inkling whatsever on how
> > to
> > use VBA. I opened a module and pasted this code in there. But when I try
> > to
> > use the runcode in my macro this string is not available. How do I get
> > this
> > into a macro?
> >
> > Sub KillAllRelations()
> > Dim db As DAO.Database
> > Dim rel As Relation
> > Dim inti As Integer
> > Set db = DBEngine(0)(0)
> > For inti = db.Relations.Count - 1 To 0 Step -1
> > Set rel = db.Relations(inti)
> > Debug.Print "Deleting relation "; rel.Name, rel.Table,
> > rel.ForeignTable
> > db.Relations.Delete rel.Name
> > Next inti
> > End Sub
> >
> > Thanks in advance,
> >
> > --
> > Denise
>
>
>

Tony Toews has some suggestions for performance improvement at
http://www.granite.ab.ca/access/performancefaq.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Denise Pollock" <(E-Mail Removed)> wrote in message
news:408B231E-703F-4712-9A07-(E-Mail Removed)...
>I already tried to split it to a front end back end database. It lagged up
> the database so bad it took 5 mins to open each form. So instead of
> linking
> the tables. I want to create one button that I can press that will delete
> all the relationships, delete all the tables then open the import window.
> If
> you can tell me how to make the linked database work without taking so
> long
> to open forms I am fine with that too. Its being accessed over a server
> by
> over 50 members. It was fine when I was testing it on my desktop but as
> soon
> as I moved it to the server it was too slow.
> --
> Denise
>
>
> "Douglas J. Steele" wrote:
>
>> Why would you need to delete all of the tables? Simply create a new
>> database.
>>
>> I'm suspecting that your application isn't split into a front-end
>> (containing the queries, forms, reports, macros and modules), linked to a
>> back-end (containing the tables and relationships) as it should be.
>>
>> With that setup, you can delete the linked tables from the front-end
>> database: they're simply pointers to the real tables in the back-end.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Denise Pollock" <(E-Mail Removed)> wrote in
>> message
>> news:3C566FE6-A34E-4D6B-B7E7-(E-Mail Removed)...
>> > Hello,
>> >
>> > I am trying to automate the database update process. I created a macro
>> > to
>> > delete all the tables in the database. However since there are linked
>> > tables
>> > it stops part way through. From browsing other questions I found the
>> > code
>> > below to delete all my relationships. I have no inkling whatsever on
>> > how
>> > to
>> > use VBA. I opened a module and pasted this code in there. But when I
>> > try
>> > to
>> > use the runcode in my macro this string is not available. How do I get
>> > this
>> > into a macro?
>> >
>> > Sub KillAllRelations()
>> > Dim db As DAO.Database
>> > Dim rel As Relation
>> > Dim inti As Integer
>> > Set db = DBEngine(0)(0)
>> > For inti = db.Relations.Count - 1 To 0 Step -1
>> > Set rel = db.Relations(inti)
>> > Debug.Print "Deleting relation "; rel.Name, rel.Table,
>> > rel.ForeignTable
>> > db.Relations.Delete rel.Name
>> > Next inti
>> > End Sub
>> >
>> > Thanks in advance,
>> >
>> > --
>> > Denise
>>
>>
>>