Make Table Query

  • Thread starter Thread starter Alan Fisher
  • Start date Start date
A

Alan Fisher

How do I make an empty table with the fields I need to
populate at a later time. I have been able to make tables
based on data from other tables but haven't had any
success with just a blank table. Just so I am clear, here
is what I am trying to do. Make a blank table with the
following fields:

Item Number
Date Item Failed
Fail Code

Thanks for any help you can provide
..
 
Alan Fisher said:
How do I make an empty table with the fields I need to
populate at a later time. I have been able to make tables
based on data from other tables but haven't had any
success with just a blank table. Just so I am clear, here
is what I am trying to do. Make a blank table with the
following fields:

Item Number
Date Item Failed
Fail Code
Click to expand...
Hi Alan,

Here be code line you can put
in a routine based on if you are
using DAO or ADO:

DAO example:

CurrentDb.Execute "CREATE TABLE tblBlank " _
& "([ItemNumber] LONG, " _
& "[DateItemFailed] DATE, " _
& "[FailCode] TEXT(50));", dbFailOnError

ADO example:

CurrentProject.Connection.Execute "CREATE TABLE tblBlank " _
& "([ItemNumber] LONG, " _
& "[DateItemFailed] DATE, " _
& "[FailCode] TEXT(50));", dbFailOnError

or in the Query designer, just copy in the following SQL
and click on exclamation point to create "tblBlank"
(you did not give a name for your table, so change to
what you want in SQL below):

CREATE TABLE tblBlank ([ItemNumber] LONG,
[DateItemFailed] DATE, [FailCode] TEXT(50);

Gary Walter
 
How do I make an empty table with the fields I need to
populate at a later time. I have been able to make tables
based on data from other tables but haven't had any
success with just a blank table. Just so I am clear, here
is what I am trying to do. Make a blank table with the
following fields:

Item Number
Date Item Failed
Fail Code

Thanks for any help you can provide
.
Click to expand...

umm... why not have such a table permanently stored in your database?

If you need to do so, you can run a Delete query DELETE * FROM
FailureTable; and then append data into the emptied table.

MakeTable queries have a LOT of overhead; I avoid using them unless
there's a critical need to do so!
 
Dim sTableName As String
Dim tdf As TableDef
Dim NewField As DAO.Field
Dim CollFlds As DAO.Fields
Dim idx As DAO.Index
Dim Collidx As DAO.Indexes
Dim cdb As Database

sTableName = "AnyNameYouWant"

'First, the table should be deleted if it exists.

If IExists(sTableName) Then
'IExists () is a function that I wrote, If you need it,
'let me know
DoCmd.DeleteObject acTable, sTableName
End If

'Prepare a new table
Set cdb = Application.CurrentDb

Set tdf = cdb.CreateTableDef(sTableName)
Set CollFlds = tdf.Fields
Set Collidx = tdf.Indexes

'Add id, parent, name and type fields.

Set NewField = tdf.CreateField("id", dbLong)
NewField.Attributes = NewField.Attributes Or dbAutoIncrField '
'AutoNumber
CollFlds.Append NewField

Set NewField = tdf.CreateField("parent", dbLong)
CollFlds.Append NewField

Set NewField = tdf.CreateField("name", dbText, 50)
CollFlds.Append NewField

Set NewField = tdf.CreateField("type", dbLong)
CollFlds.Append NewField

'and their indexes
Set idx = tdf.CreateIndex("PrimaryKey")
With idx
.Fields.Append .CreateField("id")
.Primary = True
End With
Collidx.Append idx

cdb.TableDefs.Append tdf

'Now an empty table exists with the fields that you want.
'Change the field names and types as you see fit.

the object browser can answer any other questions. Just look up the
commands and investigate the possible arguments.

Doug
 
you can run a Delete query DELETE * FROM FailureTable
<<<

....which is only a good idea *IF* being run in memory
instead of on the disk (i.e. use a zero-length string
querydef instead). Otherwise, your suggestion would crawl
like a snail on a very large dataset [which he might
have].

Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, etc - 60% off
 
Back
Top