Acces won't allow me to delete related tables with VBA

[Sirritys]

Hi,

I have a problem (again).

I'm trying to delete table from database with VBA using
"DoCmd.DeleteObject" before I import the table with same name with
"DoCmd.TransferText".

Everything went fine until I came up with database with relations. Now
acces won't let me delete the table before importing new one. And if I
just import the new data, acces appends data to previous table instead
of replacing it.

So what should I do?

Yous,
Sirritys

 

[Patrice]

Not sure what is the exact goal of this update. My personal preference woudl
be likely to import the new table under a temporary name and then perform 3
SQL satements to update current data (updating those who are there,
inserting new ones, deleting old ones)...

Another option could be to delete/recreate the relations but what if the new
table is not consistent ?

 

[Van T. Dinh]

Use VBA to delete the relations before deleting the Table.

For sample code, see http://www.tinyurl/kmlww

 

[Sirritys]

Hi,

The table is always consistent. But it seems like too much work to
delete/recreate relations every time.

About the other option: How could I execute such statements you
referred from my VBA code.

Lets assume I import data to table called "tmp" in my loop which goes
trough all tables (in separate .txt files) in 1 folder. So when program
enters this loop it should 1st create that "tmp" table and after that
update the existing table and finally delete this "tmp" table.

So where I'm lost is what would be code for that update (updating those
who are there,
inserting new ones, deleting old ones) part. I'm really a newbie with
SQL =)

Yours,
Sirritys

 

[Sirritys]

Can't open that url, and don't want to delete relations =)

 

[Van T. Dinh]

Sorry, the correct link is:

http://www.tinyurl.com/kmlww

If you son't want to delete the relations then you won't be able to delete
the Table. In this case, you probably want to delete ALL existing records
from the Table and then append the new records to the now empty Table.

 

[Sirritys]

Hi,

This would be just what I should do.
The thing is I don't know how to do it.

I got this example from someone on the group:

CUrrentDb.Execute "DELETE * FROM MyTable", dbFailOnError

The thing is that the command is a string, as I would need to use a
variable containing a table name. (ie. "MyTable" stored in string
variable).

Can I do that somehow ?

 

[Patrice]

This would be basically something like (air code) :

1)

UPDATE MyTable SET MyTable.a=tmp.A,MyTable.B=Tmp.b etc... FROM MayTable
INNER JOIN tmp ON MyTable.Key=tmp.Key

Records are matched on the key code and fields are updated.

2) INSERT INTO MyTable(a,b etc...) SELECT a,b.. FROM tmp WHERE NOT Key IN
(SELECT Key FROM MyTable)

Records are inserted if the key is not already found in the table

3)

DELETE FROM MyTable WHERE NOT Key IN (SELECT Key FROM tmp)

It will delete records in MyTable that are not in the tmp table.

Of course all updates are still subject to existing relations (i.e. if you
have related records and not delete cascade, you'll have to delete them, if
you insert and needs related records, you'll get an error if the relation is
not satisfied).

The exact purpose may help (this is to gather data in a readonly db ?)

 

[Van T. Dinh]

CurrentDb.Execute "DELETE * FROM [" & StringVar & "]", dbFailOnError

 

[Sirritys]

THANKS!

I guess it works now.

 

[Van T. Dinh]

You're wecome ... Glad you sorted it out ...

--
Van T. Dinh
MVP (Access)

THANKS!

I guess it works now.

CurrentDb.Execute "DELETE * FROM [" & StringVar & "]", dbFailOnError