Creating fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working in Access 2003. I keep records for several kiosks which are
seasonal ( Nov & Dec ) and I have linked tables, some common tables like
Location and Employees and then I make a new Db each year for that years
specific data. So far, each year I manually create a new Db and all the 5
tables which keep track of that years business. I would like to be able to
click on a cmd button and have it creat the new Db programmatically each
year. I have gotten the basics down but I don't see a way to create an
auto-number field using tdf.createfield. I have the list of 'types' you can
define the field with like dbText or dbInteger but no auto-number.?. Can
someone help me out? I have a pretty good understanding of SQL if I need to
use it here but I wanted to be consistent since I am using newcurrentdatabase
and createtable already. Can someone tell me if it would be easier to use
SQL for the whole creation process??
Thanx
 
Realistically, unless you're talking huge volumes of data, you'd be better
off just adding a Year column to your tables, and keeping the same database
from year to year. By creating a query that return only this year's data
(using Year(Date()) as the criteria against the Year field) and using that
query rather than the table, you shouldn't have to make any other changes to
your database. (Make sure you don't name the field "Year": that's a reserved
word).

However, in answer to your specific question:

Dim dbsNew As DAO.Database
Dim tdfNew As DAO.TableDef
Dim fldNew As DAO.Field

Set dbsNew = DBEngine.Workspaces(0).CreateDatabase("path to database")
Set tdfNew = dbsNew.CreateTableDef("NewTable")
Set fldNew = tdfNew.CreateField("AutonumberField", dbLong)
fldNew.Attributes = fldNew.Attributes + dbAutoIncrField
tdfNew.Fields.Append fldNew
 
Thank You very much Doug - I will consider setting up my tables with the year
field for selection - it sounds like a good idea but I've got the whole thing
set up already so it could be hard to switch over. Thanx for the info tho' -
I hadn't thought of looking at the attributes...
 
Back
Top