Clear data from table in another database

[nschroeder]

I have an append query that adds data to a table in another database, but I want it to replace the data every time it's run. How do I clear the data before running the append query? The databases are not linked, and I don't want them linked.

 

[plog]

How do I clear the data before running the append query? The databases are not linked, and I don't want them linked.

Open the external database and run a delete query (http://www.w3schools.com/sql/sql_delete.asp) in there.

 

[nschroeder]

Sorry, I need to do this in VBA each time before I run the append query.

 

[plog]

You can do that in VBA. Its gonna take a shell call to open the database, then you can execute a query on it.

http://www.access-programmers.co.uk/forums/showthread.php?t=129779

 

[nschroeder]

Not really liking that option. Was hoping for something a little cleaner and quicker, and without possible security issues. I'm building security procs into that database, and I'm trying to send it updated user IDs whenever we have employee changes.

 

[plog]

If you don't want to link to it, you're gonna have to open it. Why the aversion to linking?

 

[nschroeder]

I do semi-frequent maintenance on db#1, and I can easily tell who's in there and boot them out when needed, because only certain users get in it, but it's a whole different set of users that get in db#2, and I wouldn't be able to tell who it was.

Thanks for the suggestions. I'll consider it and see what I can come up with.

 

[nschroeder]

I combined some code I found on a couple of different forums, and it works! Here it is:

    Dim cn As adodb.Connection
    Dim cmd As adodb.Command
    Set cn = New adodb.Connection
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.ConnectionString = "data source=[I]c:\dbname.accdb[/I]"
    cn.Mode = adModeReadWrite
    cn.Open
    Set cmd = New adodb.Command
    cmd.CommandType = adCmdText
    cmd.CommandText = "Delete * from [I]tablename[/I]"
    cmd.ActiveConnection = cn.ConnectionString
    cmd.Execute
    Set cmd = Nothing
    Set cn = Nothing

 

[plog]

So wait, you don't need to open the application? Sorry for the bad advice and thanks for the code.

 

[nschroeder]

Runs very fast too!