Deleting records before import

[Prachi via AccessMonster.com]

Is it possible to delete all records (tables ) at the click of a button?

 

[Guest]

Hi Prachi,

to delete all records in a table, on click event of your button put this few
lines of code
docmd.setwarnings false
docmd.runsql ("delete * from name of the table you wanna empty")
docmd.setwarnings true

HTH Paolo

 

[Prachi via AccessMonster.com]

thanks for replying..
So this will just delete the records from the local Access application right?
Also once i delete these records I want to import fresh data from the main
database(basically i want to perform File->Get External Data->Import->Type of
database->Name of connection.Is there a way to do this

 

[Prachi via AccessMonster.com]

thanks for replying..
So this will just delete the records from the local Access application right?And instead of the records in the table can I delete all the tables Also once i delete these tables I want to import fresh tables from the main database(basically i want to perform File->Get External Data->Import->Type of
database->Name of connection.Is there a way to do this.I have an ODBC connection installed on my computer that connects to the Database

Hi Prachi,

[quoted text clipped - 7 lines]

 

[Guest]

Yes, if your table isn't a linked table this'll delete the records from the
local table.
Otherwise (i.e. the table is linked) the records will be deleted from the
source (remote) table.
To import the "fresh" data you can do an append query if you have a table
linked to the table source. To do this in VBA (in this case all the fields of
the linked table are appended to the local table assuming the fields name are
the same)
docmd.setwarnings false
docmd.runsql("insert into your local table select your linked table.* from
your linked table")
docmd.setwarnings true

 

[George Nicholson]

docmd.runsql ("delete * from name of the table you wanna empty")

So this will just delete the records from the local Access application
right?

Well, *if* "name of table you wanna empy" is a local table, then yes, it
will just delete local data.

Also, keep in mind that instruction only empties *one* table, not all tables
in the application

If you want to empty more than one table, simply repeat that instruction as
many as necessary.
docmd.runsql ("delete * from table1")
docmd.runsql ("delete * from table2")

If you have enforced relationships between tables, you may have to do the
deletes for that group of tables in a specific order (bottom of heirarchy to
top)
Reverse the order when filling those tables again (top of heirarchy to
bottom).

HTH,

 

[Prachi via AccessMonster.com]

Application:
Ok so this application generates (or prints word documents).
the data is mapped from queries written for this which further uses the
tables.
But i need to refresh this table from time to time (say a week) so as to
bring in fresh data to print reports.
So my first question where i had to automate deletion of tables instead of
manually selecting and deleting them was resolved by I the Use of a macro
and the DeleteObject command for deleting all the tables.I hope I am right.

Now my concern is how to automatically import the data at a press of a button.
when i use transfer database it gives me the error'Installed ISAM not found"
which i don not understand because the connection is ODBC connection,Please
can you help me here.

So this will just delete the records from the local Access application
right?

Well, *if* "name of table you wanna empy" is a local table, then yes, it
will just delete local data.

Also, keep in mind that instruction only empties *one* table, not all tables
in the application

If you want to empty more than one table, simply repeat that instruction as
many as necessary.
docmd.runsql ("delete * from table1")
docmd.runsql ("delete * from table2")

If you have enforced relationships between tables, you may have to do the
deletes for that group of tables in a specific order (bottom of heirarchy to
top)
Reverse the order when filling those tables again (top of heirarchy to
bottom).

HTH,

thanks for replying..
So this will just delete the records from the local Access application

[quoted text clipped - 16 lines]