Drop Table in SQL database from access 1

[ph74]

I have a

DoCmd.TransferDatabase

which sucessfully copies a table from my Access DB to an SQL DB BUT only the first time. After checking other posts i need to add a

DROP TABLE

before the export.

How do you drop a table from an SQL DB from within Access??

 

[jrbarnett]

DoCmd.RunSQL "DROP TABLE tablename;"

John

 

[ph74]

Thanks for the quick reply John.
This returns a

Table 'StaffExport' does not exist

The table is not is the local Access DB but rather in an SQL DB, so some how need to reference the ODBC connection??

 

[nicsin]

You could use ADO

Dim con as ADODB.COnnection

set con = New ADODB.Connection

with con
    .Open "DSN=mySystemDSN;" & _ 
          "Uid=myUsername;" & _ 
          "Pwd=myPassword"
    .Execute "DROP TABLE StaffExport"
end with

set con = Nothing

You would need a reference to ADO though.

 

[ph74]

Thanks nicsin, this looks like what i need, but could you expand a bit on "You would need a reference to ADO though."

 

[nicsin]

You have to add a reference to ADO in your project. Just go to menu Tools -> References and check the Microsoft ActiveX Data Objects Library. Then you will be able to construct the ADO connection object and perform the task.