RE: Creating a table in Basic

My plan was to have a second archive DB where the old data was stored. i was
just seeing if i could get the process to work on a single db. I got the VBA
code to work in the database. My next goal is to get this to work with a
second db.

My question now is, how would i use VBA to open a DB in another location,
more specificaly on a seperate computer/server. (the application isn't
really server/sql major stuff. just data being thrown from one db to another)

would it be like

Dim dbsArchive as Database
Set dbsArchive = "\\Servername\Directory\Subdirector\dbname"

::create sql statments to create new table::

dbsArchive.Execute (sql statement)

::make sql statment to make insert into table::

dbsArchive.Execute (sql statment)

so if my archive DB was located on the server named SmoothDB in the director
named RollingData, and the name of the database was rollarch.mdb
my sql statment would be

Set dbsArchive = "\\SmoothDB\RollingData\rollarch.mdb"

"Klatuu" wrote:

I think you may like this idea a little better. If you don't want to keep
your archive data in your production database, you can move the record from
the table in your current database to a table with a matching structure in
the archive database before you delete it from your production table.

To move the data from the table to the archive, create an append query.
Notice that after you have selected the table and you change the query type
from Select to Append, it will have 2 buttons, Current Database and Another
Click Another Database. Click Browse and navigate to your archinve database.

After you have completed buiding the query with the criteria and all, switch
to SQL view. Note the path to the other database will be in Drive Letter
mapping. You will want to change that to UNC mapping because not all users
will have the same drive letter mapped to the folder.
So you would change

Now all you have to do is run this query then the delete query and you are

I would also suggest you use
Currentdb.Execute("MyActionQueryName"), dbFailOnError
rather than
Docmd.RunSQL "MyActionQueryName"

It is much faster because it bypasses the Access UI and goes directly to
Jet. Since is does not go through Acces, it is not affected by SetWarnings.

"Mike" wrote:

I have a form that runs when the DB is opened. When the DB is opened, i want
it to search a table for dates that are 30 days or older. each of these
records must be deleted. A Delete query would sufice there. however, each
record is associated with a table. i would like this table to be moved to an
archive database (but right now, i'm just setting it to a backup table in the
current db). If i use the Make table query, it responds that there isn't a
table that data can be dumped into.

is there a basic command that will create an empty table that i can dump
data into?