Creating a table in Basic

[Mike]

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

 

[Mike]

Solved

 

[Klatuu]

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]

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]

I always avoid using make table queries. They are very inefficient. Since
they don't know the data structure of the original table, they use defaults.
Text fields are alway created with a length of 255 or whatever is set in the
options for example.

The method I suggested is about as easy as it gets. If you follow my
example, it will be a lot less code. You use an append query to copy the
data from production to archive. If you need to start with an empty table,
create a delete query that points to the archve database. It can all be done
from your production front end.

 

[Mike]

You have given me information w/o answering my question. i guess i should
give mroe background though.

when the user opens the DB, a start form is run, that archives old data,
then the user goes about his/her business. the startform archiving is my
problem.

each archived table has to be in a seperate table. so i must make this
table when the archiving is started. I can't go into the archive DB and
create tables every day and whatnot. that's why i liked the maketable
command. sure it may be inefficent, but working with small data in a time
insensitive process means i can be lax on runtimes. this is why i would like
to open a second database with vba so i can create the table needed to fill
the archive data.

i tried your method of doing the make query, but i need to create the table
in the archive first. access won't make a table that isn't there.

here is the code i use now to create the table in the current database, but
as i said, i'd like to create the table in the archive database

Private Sub Form_Load()

Dim dbsC1408 As Database
Dim rstCoil As Recordset
Dim strSQL As String
Dim crtTbl As String
'Dim dbsArchive As Database

Set dbsC1408 = CurrentDb


Set rstCoil = dbsC1408.OpenRecordset("tblCoils")

rstCoil.MoveFirst


Do Until rstCoil.EOF


If rstCoil!Date < (Date - 30) Then

'Make table statmenet here
crtTbl = "CREATE TABLE " + rstCoil!CoilID
crtTbl = crtTbl + "bak (Ref SINGLE, Front SINGLE, Back SINGLE, Power
CHAR(50), CapBanksFw CHAR(50), PresetHex CHAR(50), CapBanksHex CHAR(50), Ia
SINGLE, Va SINGLE, Kw SINGLE, [Time] LONG);"

dbsC1408.Execute (crtTbl)


'select query
strSQL = "INSERT INTO " + rstCoil!CoilID + "bak"
strSQL = strSQL + " ( Ref, Front, Back, Power, CapBanksFw, PresetHex,
CapBanksHex, Ia, Va, Kw, [Time]) SELECT ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ref, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Front, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Back, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Power, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksFw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].PresetHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ia, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Va, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Kw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Time FROM "
strSQL = strSQL + rstCoil!CoilID + ";"



'Make the backup table
dbsC1408.Execute (strSQL)

'delete table command
dbsC1408.TableDefs.Delete (rsCoil!CoilID)

'delete record
rstCoil.Delete

End If

rstCoil.MoveNext
Loop

rstCoil.Close
DoCmd.OpenForm "frmCoils"
DoCmd.Close acForm, "frmStart"

End Sub


so after the Set dbsC1408 = CurrentDb i'd like to have a statement
Set dbsArchive = 'way to open database from a different location'

and then adjust the statments to use dbsArchive instead of dbsC1408

i hope this gives you a better understanding of what i'm looking for

 

[Klatuu]

Okay Mike, you are not paying attention. The answer is there. It appears
you are choosing to ignore the answer as well as the advice.

Based on your post, the design of the database is seriously flawed.

Lets see if you can understand this. You can use the same approach I
offered in ANY KIND OF QUERY. If you insist on using the make table, go
ahead. You keep wanting to make it harder than it is. Just direct your make
table query to the archive database. That is all there is to it. You don't
have to go through opening the archinve database and doing anything else.

Here it is step by step:
1. Create the make table query - tell it you want to put it in another
database, remember to change the drive letter mapping to UNC mapping
2. Put one and only one (you don't need more) line of code in the Open event
of your startup form to run the query

Currentdb.Execute("MyMakeTableQuery"), dbFailOnError

That's all there is to it.

You have given me information w/o answering my question. i guess i should
give mroe background though.

when the user opens the DB, a start form is run, that archives old data,
then the user goes about his/her business. the startform archiving is my
problem.

each archived table has to be in a seperate table. so i must make this
table when the archiving is started. I can't go into the archive DB and
create tables every day and whatnot. that's why i liked the maketable
command. sure it may be inefficent, but working with small data in a time
insensitive process means i can be lax on runtimes. this is why i would like
to open a second database with vba so i can create the table needed to fill
the archive data.

i tried your method of doing the make query, but i need to create the table
in the archive first. access won't make a table that isn't there.

here is the code i use now to create the table in the current database, but
as i said, i'd like to create the table in the archive database

Private Sub Form_Load()

Dim dbsC1408 As Database
Dim rstCoil As Recordset
Dim strSQL As String
Dim crtTbl As String
'Dim dbsArchive As Database

Set dbsC1408 = CurrentDb


Set rstCoil = dbsC1408.OpenRecordset("tblCoils")

rstCoil.MoveFirst


Do Until rstCoil.EOF


If rstCoil!Date < (Date - 30) Then

'Make table statmenet here
crtTbl = "CREATE TABLE " + rstCoil!CoilID
crtTbl = crtTbl + "bak (Ref SINGLE, Front SINGLE, Back SINGLE, Power
CHAR(50), CapBanksFw CHAR(50), PresetHex CHAR(50), CapBanksHex CHAR(50), Ia
SINGLE, Va SINGLE, Kw SINGLE, [Time] LONG);"

dbsC1408.Execute (crtTbl)


'select query
strSQL = "INSERT INTO " + rstCoil!CoilID + "bak"
strSQL = strSQL + " ( Ref, Front, Back, Power, CapBanksFw, PresetHex,
CapBanksHex, Ia, Va, Kw, [Time]) SELECT ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ref, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Front, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Back, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Power, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksFw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].PresetHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ia, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Va, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Kw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Time FROM "
strSQL = strSQL + rstCoil!CoilID + ";"



'Make the backup table
dbsC1408.Execute (strSQL)

'delete table command
dbsC1408.TableDefs.Delete (rsCoil!CoilID)

'delete record
rstCoil.Delete

End If

rstCoil.MoveNext
Loop

rstCoil.Close
DoCmd.OpenForm "frmCoils"
DoCmd.Close acForm, "frmStart"

End Sub


so after the Set dbsC1408 = CurrentDb i'd like to have a statement
Set dbsArchive = 'way to open database from a different location'

and then adjust the statments to use dbsArchive instead of dbsC1408

i hope this gives you a better understanding of what i'm looking for

I always avoid using make table queries. They are very inefficient. Since
they don't know the data structure of the original table, they use defaults.
Text fields are alway created with a length of 255 or whatever is set in the
options for example.

The method I suggested is about as easy as it gets. If you follow my
example, it will be a lot less code. You use an append query to copy the
data from production to archive. If you need to start with an empty table,
create a delete query that points to the archve database. It can all be done
from your production front end.

 

[Mike]

well, i'm new to db so the db may be flawed, but whatev. my thing is that i
don't know what table will be removed. there may be up to 20 tables and
maybe 3 need moved/archived. there is another table, tblCoils that contains
the name of the data tables, CoilID. i use that to select the table and
such, as you can see in the strSQL and crtTbl strings.

but going on what you said, i could change that to just one string and have
it be like

INSERT INTO 'tablename_bak' IN '\\Server\Directory\Filename'
SELECT <fields>
FROM (current table)


now, seeing as how i want SEPERATE tables for each table archived, this
statement WILL CREATE A NEW TABLE for each table being archived???

and btw, will that delete the record containg the tablename from the coils
table, along with deleting the table named after the one record entry?

Okay Mike, you are not paying attention. The answer is there. It appears
you are choosing to ignore the answer as well as the advice.

Based on your post, the design of the database is seriously flawed.

Lets see if you can understand this. You can use the same approach I
offered in ANY KIND OF QUERY. If you insist on using the make table, go
ahead. You keep wanting to make it harder than it is. Just direct your make
table query to the archive database. That is all there is to it. You don't
have to go through opening the archinve database and doing anything else.

Here it is step by step:
1. Create the make table query - tell it you want to put it in another
database, remember to change the drive letter mapping to UNC mapping
2. Put one and only one (you don't need more) line of code in the Open event
of your startup form to run the query

Currentdb.Execute("MyMakeTableQuery"), dbFailOnError

That's all there is to it.

You have given me information w/o answering my question. i guess i should
give mroe background though.

when the user opens the DB, a start form is run, that archives old data,
then the user goes about his/her business. the startform archiving is my
problem.

each archived table has to be in a seperate table. so i must make this
table when the archiving is started. I can't go into the archive DB and
create tables every day and whatnot. that's why i liked the maketable
command. sure it may be inefficent, but working with small data in a time
insensitive process means i can be lax on runtimes. this is why i would like
to open a second database with vba so i can create the table needed to fill
the archive data.

i tried your method of doing the make query, but i need to create the table
in the archive first. access won't make a table that isn't there.

here is the code i use now to create the table in the current database, but
as i said, i'd like to create the table in the archive database

Private Sub Form_Load()

Dim dbsC1408 As Database
Dim rstCoil As Recordset
Dim strSQL As String
Dim crtTbl As String
'Dim dbsArchive As Database

Set dbsC1408 = CurrentDb


Set rstCoil = dbsC1408.OpenRecordset("tblCoils")

rstCoil.MoveFirst


Do Until rstCoil.EOF


If rstCoil!Date < (Date - 30) Then

'Make table statmenet here
crtTbl = "CREATE TABLE " + rstCoil!CoilID
crtTbl = crtTbl + "bak (Ref SINGLE, Front SINGLE, Back SINGLE, Power
CHAR(50), CapBanksFw CHAR(50), PresetHex CHAR(50), CapBanksHex CHAR(50), Ia
SINGLE, Va SINGLE, Kw SINGLE, [Time] LONG);"

dbsC1408.Execute (crtTbl)


'select query
strSQL = "INSERT INTO " + rstCoil!CoilID + "bak"
strSQL = strSQL + " ( Ref, Front, Back, Power, CapBanksFw, PresetHex,
CapBanksHex, Ia, Va, Kw, [Time]) SELECT ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ref, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Front, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Back, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Power, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksFw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].PresetHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ia, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Va, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Kw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Time FROM "
strSQL = strSQL + rstCoil!CoilID + ";"



'Make the backup table
dbsC1408.Execute (strSQL)

'delete table command
dbsC1408.TableDefs.Delete (rsCoil!CoilID)

'delete record
rstCoil.Delete

End If

rstCoil.MoveNext
Loop

rstCoil.Close
DoCmd.OpenForm "frmCoils"
DoCmd.Close acForm, "frmStart"

End Sub


so after the Set dbsC1408 = CurrentDb i'd like to have a statement
Set dbsArchive = 'way to open database from a different location'

and then adjust the statments to use dbsArchive instead of dbsC1408

i hope this gives you a better understanding of what i'm looking for

I always avoid using make table queries. They are very inefficient. Since
they don't know the data structure of the original table, they use defaults.
Text fields are alway created with a length of 255 or whatever is set in the
options for example.

The method I suggested is about as easy as it gets. If you follow my
example, it will be a lot less code. You use an append query to copy the
data from production to archive. If you need to start with an empty table,
create a delete query that points to the archve database. It can all be done
from your production front end.

:

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"
??

 

[Mike]

sorry about the last post. I was still going from the append query you
suggested me making. i reread through, and found you changed the apend to
make

i didn't realize you didn't need to open the db to do that. i'm going to
try that first. i'll let you know how it goes.

Okay Mike, you are not paying attention. The answer is there. It appears
you are choosing to ignore the answer as well as the advice.

Based on your post, the design of the database is seriously flawed.

Lets see if you can understand this. You can use the same approach I
offered in ANY KIND OF QUERY. If you insist on using the make table, go
ahead. You keep wanting to make it harder than it is. Just direct your make
table query to the archive database. That is all there is to it. You don't
have to go through opening the archinve database and doing anything else.

Here it is step by step:
1. Create the make table query - tell it you want to put it in another
database, remember to change the drive letter mapping to UNC mapping
2. Put one and only one (you don't need more) line of code in the Open event
of your startup form to run the query

Currentdb.Execute("MyMakeTableQuery"), dbFailOnError

That's all there is to it.

You have given me information w/o answering my question. i guess i should
give mroe background though.

when the user opens the DB, a start form is run, that archives old data,
then the user goes about his/her business. the startform archiving is my
problem.

each archived table has to be in a seperate table. so i must make this
table when the archiving is started. I can't go into the archive DB and
create tables every day and whatnot. that's why i liked the maketable
command. sure it may be inefficent, but working with small data in a time
insensitive process means i can be lax on runtimes. this is why i would like
to open a second database with vba so i can create the table needed to fill
the archive data.

i tried your method of doing the make query, but i need to create the table
in the archive first. access won't make a table that isn't there.

here is the code i use now to create the table in the current database, but
as i said, i'd like to create the table in the archive database

Private Sub Form_Load()

Dim dbsC1408 As Database
Dim rstCoil As Recordset
Dim strSQL As String
Dim crtTbl As String
'Dim dbsArchive As Database

Set dbsC1408 = CurrentDb


Set rstCoil = dbsC1408.OpenRecordset("tblCoils")

rstCoil.MoveFirst


Do Until rstCoil.EOF


If rstCoil!Date < (Date - 30) Then

'Make table statmenet here
crtTbl = "CREATE TABLE " + rstCoil!CoilID
crtTbl = crtTbl + "bak (Ref SINGLE, Front SINGLE, Back SINGLE, Power
CHAR(50), CapBanksFw CHAR(50), PresetHex CHAR(50), CapBanksHex CHAR(50), Ia
SINGLE, Va SINGLE, Kw SINGLE, [Time] LONG);"

dbsC1408.Execute (crtTbl)


'select query
strSQL = "INSERT INTO " + rstCoil!CoilID + "bak"
strSQL = strSQL + " ( Ref, Front, Back, Power, CapBanksFw, PresetHex,
CapBanksHex, Ia, Va, Kw, [Time]) SELECT ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ref, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Front, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Back, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Power, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksFw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].PresetHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ia, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Va, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Kw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Time FROM "
strSQL = strSQL + rstCoil!CoilID + ";"



'Make the backup table
dbsC1408.Execute (strSQL)

'delete table command
dbsC1408.TableDefs.Delete (rsCoil!CoilID)

'delete record
rstCoil.Delete

End If

rstCoil.MoveNext
Loop

rstCoil.Close
DoCmd.OpenForm "frmCoils"
DoCmd.Close acForm, "frmStart"

End Sub


so after the Set dbsC1408 = CurrentDb i'd like to have a statement
Set dbsArchive = 'way to open database from a different location'

and then adjust the statments to use dbsArchive instead of dbsC1408

i hope this gives you a better understanding of what i'm looking for

I always avoid using make table queries. They are very inefficient. Since
they don't know the data structure of the original table, they use defaults.
Text fields are alway created with a length of 255 or whatever is set in the
options for example.

The method I suggested is about as easy as it gets. If you follow my
example, it will be a lot less code. You use an append query to copy the
data from production to archive. If you need to start with an empty table,
create a delete query that points to the archve database. It can all be done
from your production front end.

:

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]

Great, Mike.
The Make Table will not affect any data in your production table. It will
overwrite an exitsting table of the same name in your archive database. If
that is an issue, you may want to use the date or something that will keep
the tables separate.

If there are multiple tables that need to be archived, you will need a
different query for each of them.

Please feel free to post back if you have any more questions on this.

sorry about the last post. I was still going from the append query you
suggested me making. i reread through, and found you changed the apend to
make

i didn't realize you didn't need to open the db to do that. i'm going to
try that first. i'll let you know how it goes.

Okay Mike, you are not paying attention. The answer is there. It appears
you are choosing to ignore the answer as well as the advice.

Based on your post, the design of the database is seriously flawed.

Lets see if you can understand this. You can use the same approach I
offered in ANY KIND OF QUERY. If you insist on using the make table, go
ahead. You keep wanting to make it harder than it is. Just direct your make
table query to the archive database. That is all there is to it. You don't
have to go through opening the archinve database and doing anything else.

Here it is step by step:
1. Create the make table query - tell it you want to put it in another
database, remember to change the drive letter mapping to UNC mapping
2. Put one and only one (you don't need more) line of code in the Open event
of your startup form to run the query

Currentdb.Execute("MyMakeTableQuery"), dbFailOnError

That's all there is to it.

You have given me information w/o answering my question. i guess i should
give mroe background though.

when the user opens the DB, a start form is run, that archives old data,
then the user goes about his/her business. the startform archiving is my
problem.

each archived table has to be in a seperate table. so i must make this
table when the archiving is started. I can't go into the archive DB and
create tables every day and whatnot. that's why i liked the maketable
command. sure it may be inefficent, but working with small data in a time
insensitive process means i can be lax on runtimes. this is why i would like
to open a second database with vba so i can create the table needed to fill
the archive data.

i tried your method of doing the make query, but i need to create the table
in the archive first. access won't make a table that isn't there.

here is the code i use now to create the table in the current database, but
as i said, i'd like to create the table in the archive database

Private Sub Form_Load()

Dim dbsC1408 As Database
Dim rstCoil As Recordset
Dim strSQL As String
Dim crtTbl As String
'Dim dbsArchive As Database

Set dbsC1408 = CurrentDb


Set rstCoil = dbsC1408.OpenRecordset("tblCoils")

rstCoil.MoveFirst


Do Until rstCoil.EOF


If rstCoil!Date < (Date - 30) Then

'Make table statmenet here
crtTbl = "CREATE TABLE " + rstCoil!CoilID
crtTbl = crtTbl + "bak (Ref SINGLE, Front SINGLE, Back SINGLE, Power
CHAR(50), CapBanksFw CHAR(50), PresetHex CHAR(50), CapBanksHex CHAR(50), Ia
SINGLE, Va SINGLE, Kw SINGLE, [Time] LONG);"

dbsC1408.Execute (crtTbl)


'select query
strSQL = "INSERT INTO " + rstCoil!CoilID + "bak"
strSQL = strSQL + " ( Ref, Front, Back, Power, CapBanksFw, PresetHex,
CapBanksHex, Ia, Va, Kw, [Time]) SELECT ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ref, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Front, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Back, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Power, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksFw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].PresetHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ia, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Va, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Kw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Time FROM "
strSQL = strSQL + rstCoil!CoilID + ";"



'Make the backup table
dbsC1408.Execute (strSQL)

'delete table command
dbsC1408.TableDefs.Delete (rsCoil!CoilID)

'delete record
rstCoil.Delete

End If

rstCoil.MoveNext
Loop

rstCoil.Close
DoCmd.OpenForm "frmCoils"
DoCmd.Close acForm, "frmStart"

End Sub


so after the Set dbsC1408 = CurrentDb i'd like to have a statement
Set dbsArchive = 'way to open database from a different location'

and then adjust the statments to use dbsArchive instead of dbsC1408

i hope this gives you a better understanding of what i'm looking for

:

I always avoid using make table queries. They are very inefficient. Since
they don't know the data structure of the original table, they use defaults.
Text fields are alway created with a length of 255 or whatever is set in the
options for example.

The method I suggested is about as easy as it gets. If you follow my
example, it will be a lot less code. You use an append query to copy the
data from production to archive. If you need to start with an empty table,
create a delete query that points to the archve database. It can all be done
from your production front end.

:

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"
??

 

[Mike]

Glad we finally got on the same path. sorry again about exploding, major
slip on my part. I've been trying to get this for about 3 days. but it's
all good.

for more info about my database, there is a table that contains a records of
different coils produced by a company. this table includes fields such as
customer, time, date, alloy and such. there is also a field, CoilID which is
the identifyer for each coil produced. this CoilID field has the same name
as another table. so if the CoilID field entry was Coil14_5Jan06 there
would be a table named Coil14_5Jan06. This table contains different
measurements taken from sensors on the production line. this is used for QA
and process control i.e. making sure the lines are running at a good
production level.

if you will look at the code posted above, what i do is when the db is
opened, an dthe load function runs, it opens the the database and the
recorset of the table holding information about the many coils (tblCoils).
from that recordset, the function searches the date field, searching for any
that are older than 30 days. (the If rstCoil!Date < (Date - 30)). now when
that statement is true, the code would execute the crete table sql statement,
by first building it, and then using the db.Execute (crtTbl) statement.

then the function makes the apend table SQL line, strSQL to collect the
different fields and whatnot. once the strSQL statement is complete, it uses
the same db.execute (strSQL) statement.

then the table is deleted and the record is deleted. the function moves to
the next redord, thus checking all tables.

because of i'm pulling info basically from 2 tables with one being semi
linked (in a round about way) to the other, is why i had the large load
function.

i'm going to change my load function to this. i will remove the statemtns
starting at
'Make table statment here through
dbsC1408.Execute(strSQL)

and replace it with this
strSQL = "Select ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ref, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Front, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Back, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Power, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksFw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].PresetHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].CapBanksHex, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Ia, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Va, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Kw, ["
strSQL = strSQL + rstCoil!CoilID
strSQL = strSQL + "].Time INTO "
strSQL = strSQL + rstCoil!CoilId + "back IN
'\\Crispdb\hdconnector\rollback.mdb' FROM "
strSQL = strSQL + rstCoil!CoilID + ";"


'MsgBox (strSQL)

'Make the backup table
dbsC1408.Execute (strSQL)

everything else will stay the same, the structure of the loop and the delete
table/record and what not.

whit that in mind, does it look alright?

 

[Mike]

one more question, on teh dbsC1408.Execute (strSQL), dbFailOnError

if i already have a table in the archive db, and the above statment tries to
make a table with the same name, will the statement just procede to the next
line?

so, if for some strange reason a table gets added, but the record adn table
in the orig db don't get deleted, and the program is run, what will happen?

i tried that now, w/o the dbFailOnError and the first table it came to that
had a backup the program threw up an error stating the table already existed,
and ended. i would want the program to delete the table/record in the usable
db, and move on.

any suggestions?

 

[Mike]

ok, i got it. i changed the code to the following

On Error Resume Next
dbsC1408.Execute(strSQL)

and that works fine.

now i'd like to do a similar thing, in the archive. except delete records
over 90 days old. but i would like it to run from the main database.
(program requirement, i can't argue) so....blah.

i'll post a new topic/question tho.