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

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 ?

--
Patruce

"Sirritys" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail Removed)...
> 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
>

Use VBA to delete the relations before deleting the Table.

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

--
HTH
Van T. Dinh
MVP (Access)



"Sirritys" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

Patrice wrote:
> 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 ?

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


Van T. Dinh wrote:
> Use VBA to delete the relations before deleting the Table.
>
> For sample code, see http://www.tinyurl/kmlww

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.

--
HTH
Van T. Dinh
MVP (Access)




"Sirritys" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Can't open that url, and don't want to delete relations =)
>
>

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 ?




> 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.

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 ?)

--
Patrice

"Sirritys" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail Removed)...
> 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
>
> Patrice wrote:
>> 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 ?
>

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


--
HTH
Van T. Dinh
MVP (Access)



"Sirritys" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 ?
>
>
>
>
>> 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.
>

THANKS!

I guess it works now.

Van T. Dinh wrote:
> CurrentDb.Execute "DELETE * FROM [" & StringVar & "]", dbFailOnError