Can I make DoCmd.TransferText to replace existing table?

[Sirritys]

I'm tryingto replace tables when importing, but instead the
"DoCmd.TransferText" just appends the data after existing data in
existing table.

How could I make it to replace the whole table.

If this is not possible, how can I delete tables with VBA?

Sincerelly yours,
Sirritys

 

[Nikos Yannacopoulos]

DoCmd.DeleteObject acTable, "MyTable"

HTH,
Nikos

 

[Sirritys]

Thank you!

 

[Nikos Yannacopoulos]

I should have said this in the first instance... this kind of operation
is expected to result in significant database bloat, so you better make
sure you compact regularly! If this is a monolithic database, setting
the Compact On Close option (Tools > Options, tab "General") will do it
just fine.

On second thought, you could achieve the same result by just deleting
all records from the table rather than the table itself:

CUrrentDb.Execute "DELETE * FROM MyTable", dbFailOnError

I suspect this might result in somewhat less bloat, especially for small
tables (few records), but you'll still need to compact regularly; if the
record numbers are high, the difference in bloat would be insignificant.

Nikos

 

[Sirritys]

Hi again,

I am still wondering that if I use this method how am I able to insert
variable name on the place of "MyTable", since the whole command is
inside of " " ?

 

[Nikos Yannacopoulos]

You can concatenate test strings with variable values in code:

MyVariable = "Some Value"
strSomething = "Some Text " & MyVariable

so in the first line you assign value SomeValue to variable MyVariable;
in the second one, you concatenate a text string and the value in
MyVariable, so the value of strSomething becomes:
"Some Text Some Value"

Likewise:

strTable = "SomeTableName"
strSQL = "DELETE * FROM " & strTable
CurrentDb.Execute strSQL, dbFailOnError

I hope this is clear.

Nikos

 

[Sirritys]

Thanks man, didn't thought it that way =). Whould have realised that on
my own ;-(