Hi,

I'm trying to run queries on an access database through code in another database (this is because the database is too big to sit in one access db and so i've had to split it, but i still want only one 'user interface').

I'm using DAO to do this just now, using the

database.execute("...")

command to execute sql code on the 'remote' database.

I'm having problems because i can't use the 'set warnings off' command that i would have used in a macro to stop an error occurring when I make tables which already exist (i.e. to have it automatically replace these tables). I've tried 'DROP'ing the tables before i run the above make table code, but I'm finding this causes the remote db to top out at 2GB. I presume deleting tables before replacing them instead of setting warnings off defragments the database more?

I know about the docmd.setwarnings code but to my knowledge this can only be performed on the currently open (or active) database... So, how can i get my vb to stop falling over when it tries to create a table (in a remote db) which already exists, and just replace it instead?

Any help anyone can give would be MUCH appreciated!
x

 
Do not drop tables on the fly. I hope you are not wharing this database, but, from your post, I see that you are not sharing one backend, but more than one backend .mdb. Access will lock a database when it is doing anything on design view or creating/deleting an object.

Do not drop tables, just delete the records and re-fill the table with the new data. Anyways, how many records are you generating?


If you must do this, you can set a link to your tables and then use the table as a local object.

By the way, use ADO instead. compact your db often and de-compile.






Sal

A thought. Are You aware that You can "turn off" the warnings completely? In Access2000 it is done via
tools->
options->
edit\find.
uncheck in the confirmations section.

Dave

Thanks for the quick replies! Will try out both and see how they go - by the way sal what does 'wharing a database' mean?!

I'm compacting it quite a lot already, but if all else fails I'll try doing this more often. I can't create links as access views all linked databases as open and hence they are not automatically compactable from another database's code...

Thanks :)
x