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
Database.
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
G:\MyAppArchive\AppArchive.mdb
to
\\ServerName\MyAppArchive\AppArchive.mdb

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

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?

Thanks
.



Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)
  • RE: Import external data - web query
    ... Your reply for my query is very extensive, this is for importing a file from ... The data source I want isn't listed in the Select Data Source dialog box. ... information used to connect to a database. ... Check your driver First, make sure you have the right ODBC driver (Open ...
    (microsoft.public.excel.misc)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... synchronize combo boxes to the detail section or the parameter form query ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would ...
    (microsoft.public.access.conversion)