Delete all Records

[Eric]

I run append queries from a 4 temporary tables to 4 permanent tables. After
the append, I'd like to run a Delete Query after the append queries, that
will delete all records in all 4 temporary tables.

I've looked at some ideas and code from others, but I'm not needing any
fault attributes. I am sure of the append functions and am sure I want the
records deleted.

Here is some code I am trying with only two tables, but I get an error "you
can not delete from the specified tables"

DELETE [1ReviewHDR].*, [2ReviewBPS].*
FROM 2ReviewBPS, 1ReviewHDR;

Any help would be great.

 

[Dirk Goldgar]

I run append queries from a 4 temporary tables to 4 permanent tables.
After
the append, I'd like to run a Delete Query after the append queries, that
will delete all records in all 4 temporary tables.

I've looked at some ideas and code from others, but I'm not needing any
fault attributes. I am sure of the append functions and am sure I want
the
records deleted.

Here is some code I am trying with only two tables, but I get an error
"you
can not delete from the specified tables"

DELETE [1ReviewHDR].*, [2ReviewBPS].*
FROM 2ReviewBPS, 1ReviewHDR;

You can only delete from one table at a time. You'll have to run 4 delete
queries; e.g.,

With CurrentDb
.Execute "DELETE FROM [1ReviewHDR]", dbFailOnError
.Execute "DELETE FROM [2ReviewBPS]", dbFailOnError
' ... repeat for third and fourth tables ...
End With

 

[Eric]

That worked great Dirk, thanks. Now I just trying to get my head around how
to make into a Macro. When I use the code is shows up as a Module. I want
it to run after the append queries.

Maybe its to late and I'm not thinking straight. I'll visit again tomorrow.

Thanks Dirk
--
Eric the Rookie

I run append queries from a 4 temporary tables to 4 permanent tables.
After
the append, I'd like to run a Delete Query after the append queries, that
will delete all records in all 4 temporary tables.

I've looked at some ideas and code from others, but I'm not needing any
fault attributes. I am sure of the append functions and am sure I want
the
records deleted.

Here is some code I am trying with only two tables, but I get an error
"you
can not delete from the specified tables"

DELETE [1ReviewHDR].*, [2ReviewBPS].*
FROM 2ReviewBPS, 1ReviewHDR;

You can only delete from one table at a time. You'll have to run 4 delete
queries; e.g.,

With CurrentDb
.Execute "DELETE FROM [1ReviewHDR]", dbFailOnError
.Execute "DELETE FROM [2ReviewBPS]", dbFailOnError
' ... repeat for third and fourth tables ...
End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Stephen Lynch]

Eric:

Why do you want to put it in a macro. Just drop your code in the onclick
event of a button or whatever you are trying to do. Once you comment your
code you will not want to use macros. I started with macros and now have no
macros. Chasing down problems in a series of queries and macros is a
nightmare.

You can also use the following to get started with code:

Open the query editor, setup up your query like you normally do. Save it and
then hit the SQL code button, the one in the same as design mode. Copy the
sql code and drop it in your code with the docmd.runsql command, so you have
something like this:

' Append the data
Docmd.RunSQL "INSERT INTO tblTable1 ( Field1 ) SELECT tblTable2.Field2 FROM
tblTable2;
'Delete the tables after append
docmd.runsql "DELETE * FROM TABLE1
docmd.runsql "DELETE * FROM TABLE2
docmd.runsql "DELETE * FROM TABLE3
docmd.runsql "DELETE * FROM TABLE4

It is really a piece of cake, after a few days of struggling.

HTH

Steve

That worked great Dirk, thanks. Now I just trying to get my head around
how
to make into a Macro. When I use the code is shows up as a Module. I
want
it to run after the append queries.

Maybe its to late and I'm not thinking straight. I'll visit again
tomorrow.

Thanks Dirk
--
Eric the Rookie

I run append queries from a 4 temporary tables to 4 permanent tables.
After
the append, I'd like to run a Delete Query after the append queries,
that
will delete all records in all 4 temporary tables.

I've looked at some ideas and code from others, but I'm not needing any
fault attributes. I am sure of the append functions and am sure I want
the
records deleted.

Here is some code I am trying with only two tables, but I get an error
"you
can not delete from the specified tables"

DELETE [1ReviewHDR].*, [2ReviewBPS].*
FROM 2ReviewBPS, 1ReviewHDR;

You can only delete from one table at a time. You'll have to run 4
delete
queries; e.g.,

With CurrentDb
.Execute "DELETE FROM [1ReviewHDR]", dbFailOnError
.Execute "DELETE FROM [2ReviewBPS]", dbFailOnError
' ... repeat for third and fourth tables ...
End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Eric]

Thats great advice Steve thanks much. I have been developing Access
databases for years, and am just now getting started useing VB Script. What
a huge difference it is making and I'm hooked. Stuggling a little here and
there, but I sure like it.

Thanks again.


--
Eric the Rookie

Eric:

Why do you want to put it in a macro. Just drop your code in the onclick
event of a button or whatever you are trying to do. Once you comment your
code you will not want to use macros. I started with macros and now have no
macros. Chasing down problems in a series of queries and macros is a
nightmare.

You can also use the following to get started with code:

Open the query editor, setup up your query like you normally do. Save it and
then hit the SQL code button, the one in the same as design mode. Copy the
sql code and drop it in your code with the docmd.runsql command, so you have
something like this:

' Append the data
Docmd.RunSQL "INSERT INTO tblTable1 ( Field1 ) SELECT tblTable2.Field2 FROM
tblTable2;
'Delete the tables after append
docmd.runsql "DELETE * FROM TABLE1
docmd.runsql "DELETE * FROM TABLE2
docmd.runsql "DELETE * FROM TABLE3
docmd.runsql "DELETE * FROM TABLE4

It is really a piece of cake, after a few days of struggling.

HTH

Steve

That worked great Dirk, thanks. Now I just trying to get my head around
how
to make into a Macro. When I use the code is shows up as a Module. I
want
it to run after the append queries.

Maybe its to late and I'm not thinking straight. I'll visit again
tomorrow.

Thanks Dirk
--
Eric the Rookie

I run append queries from a 4 temporary tables to 4 permanent tables.
After
the append, I'd like to run a Delete Query after the append queries,
that
will delete all records in all 4 temporary tables.

I've looked at some ideas and code from others, but I'm not needing any
fault attributes. I am sure of the append functions and am sure I want
the
records deleted.

Here is some code I am trying with only two tables, but I get an error
"you
can not delete from the specified tables"

DELETE [1ReviewHDR].*, [2ReviewBPS].*
FROM 2ReviewBPS, 1ReviewHDR;


You can only delete from one table at a time. You'll have to run 4
delete
queries; e.g.,

With CurrentDb
.Execute "DELETE FROM [1ReviewHDR]", dbFailOnError
.Execute "DELETE FROM [2ReviewBPS]", dbFailOnError
' ... repeat for third and fourth tables ...
End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)