Home » MS OfficeRSS

Access 2007: Using VBA to create a table and fields, and modifying the properties of those fields

I need to know how to use VBA to create a table with fields, and also modify certain properties or attributes of those fields.  It sounds simple, but there are numerous ways to do it depending which method you want to use (dao, etc...) and I haven't been able to figure out how to do what I want with any of the methods.

So let's say that the table name will be "tblTest" and the field names will be "fldText", "fldByte", and "fldLngInteger".

1. I need to be able to create the table.

2. I need to create fldText and be able to make it have the following settings: TEXT, 15 Character length, Unicode Compression ON, Indexed

3. I need to create fldByte as type Byte, and fldLngInteger as type Long Integer

 

Someone please help me with this.  Thank you.

 

8 Answers Found

 

Answer 1

A table is a persistent object (whether in Access, Sql Server, Oracle, ...).  It is generally not a good practice to be dynamically modifying persistent objects like tables.  So some proper prior planning would be in order when creating a table (that will be persistent).  I will guess you are thinking about a temporary table.  The easiest way to create a temp table is to use a "Select * Into tblx" statement.

DoCmd.RunSql "Select * Into tblx From yourmainTbl Where datefld between #1/1/2011# And #2/1/2011#"

This will create a persistent table called tblx (in Sql Server you can create a temp table prefaced by # as int #tmpx).  Access doesn't have that kind of functionality.  In Sql server when you close the thread that created the #tmp table, that table gets eliminated.  In Access you have to take the responsibility of managing/discarding temp tables.  So tblx, in the sample above, will contain the same fields as yourmainTbl.  You could also select only a few fields from yourmainTlb, or you could join yourmainTbl to another tbl and select specific fields from that join, and the tblx will only contains the fields you selected from the join.  The data types will also be preserved (for the most part), number fields, text fields, date fields, ...

 

Answer 2

"KyssMe143" wrote in message
news:0c962c4c-6643-4384-8ffd-f03c01806e04@communitybridge.codeplex.com...
>I need to know how to use VBA to create a table with fields, and also
>modify certain properties or attributes of those fields.  It sounds simple,
>but there are numerous ways to do it depending which method you want to use
>(dao, etc...) and I haven't been able to figure out how to do what I want
>with any of the methods.
>
> So let's say that the table name will be "tblTest" and the field names
> will be "fldText", "fldByte", and "fldLngInteger".
>
> 1. I need to be able to create the table.
>
> 2. I need to create fldText and be able to make it have the following
> settings: TEXT, 15 Character length, Unicode Compression ON, Indexed
>
> 3. I need to create fldByte as type Byte, and fldLngInteger as type Long
> Integer
>
>
> Someone please help me with this.  Thank you.
>
> --------------------------------------------------------------------------------
> David Lee Wisniewski
 
>
 
As you say, there are several ways to do it.  Here's a way that uses
straight SQL, executed via ADO:
'
'------ start of code ------
    With CurrentProject.Connection
        .Execute _
           "CREATE TABLE tblTest2 (" & _
               " fldText CHAR(15) WITH COMP" & _
               ", fldByte BYTE" & _
               ", fldLngInteger INTEGER" & _
               " )"
        .Execute _
           "CREATE INDEX ixText ON tblTest2 (fldText)"
    End With
 
'------ end of code ------
'
 
One can do the same thing using DAO objects, but it's more complicated and
not any more efficient.  However, if you need to set other, Access-specific
table or field properties, then you have to use DAO.
 
 
 

Answer 3

For what you want to do and assume that you are working with an Access database, DAO is probably the best way to go.

you'll need to declare these variables

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim tblNewTable As DAO.TableDef
Dim fldNewField As DAO.Field
Dim idxNewIndex As DAO.index
Dim fldIndexField As DAO.Field
so the first thing to do is create a workspace then open the database you want to modify in the ws.

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDatabase) ' strDatabase is the full path to the accdb or mdb

 

next thing to do is create a table, in fact we will first create a tabledef, then create some fields in it then append the table to the tables collection (to make it permanent)

 

Set tblNewTable = db.TableDefs(strTableName) ' you will need to declare tblNewTable As DAO.TableDef at the top of your procedure

strFieldname = "ID"
Set fldNewField = tblNewTable.CreateField(strFieldname, dbLong) ' let's create an autonumber first
 With fldNewField
   ' Appending dbAutoIncrField to Attributes
   ' tells Jet that it's an Autonumber field
   .Attributes = .Attributes Or dbAutoIncrField
 End With

' now a long integer but most others are the same
strFieldname = "Long_Integer_Field"
Set fldNewField = tblNewTable.CreateField(strFieldname, dbLong)
' or you can use dbInteger, dbSingle, dbDate, dbCurrency, dbBoolean, dbBinary dbMemo or, dbLongBinary (did I miss any?)

' after each field you need to append it to the table def
tblNewTable.Fields.Append fldNewField

' text is a little different, you need to specify the length
Set fldNewField = tblNewTable.CreateField(strFieldname, dbText, intFieldLength)

tblNewTable.Fields.Append fldNewField

' once you've added all the fields then we need to append the table to the tables collection

db.TableDefs.Append tblNewTable

 

There's a table and all the fields now for the indexes - here are some samples

 

' PrimaryKey"
 Set idxNewIndex = tblNewTable.CreateIndex("PrimaryKey")
 Set fldIndexField = idxNewIndex.CreateField(strFieldname)
 idxNewIndex.Primary = True
 idxNewIndex.Fields.Append fldIndexField
 tblNewTable.Indexes.Append idxNewIndex
' Unique"
 Set idxNewIndex = tblNewTable.CreateIndex(strIndexName)
 Set fldIndexField = idxNewIndex.CreateField(strFieldname)
 idxNewIndex.Unique = True
 idxNewIndex.Fields.Append fldIndexField
 tblNewTable.Indexes.Append idxNewIndex
' NonUnique"
 Set idxNewIndex = tblNewTable.CreateIndex(strIndexName)
 Set fldIndexField = idxNewIndex.CreateField(strFieldname)
 idxNewIndex.Fields.Append fldIndexField
 tblNewTable.Indexes.Append idxNewIndex
I hope that gets you started

 

 


Kent
 

Answer 4

"KyssMe143" wrote in message
news:e485ad64-edfd-4ea7-9eb4-450c7e231b18@communitybridge.codeplex.com...
> Dirk,
>
> Thanks so much for your assistance... I had found similar code in the help
> file as what you posted, but for some reason it just wouldn't cooperate
> with me and kept giving me an error message.  I copied and pasted the code
> directly from your response into a public function and tested it, and IT
> WORKS GREAT! :)
>
> Of course the scenario I gave in my help request is a lot simpler than
> what I actually need to do, but it encompasses the actions I need to
> perform.  The reason I need to create and modify the tables like this is
> because I am dealing with home insurance policies, where I am importing
> over 800,000 records from multiple delimited text files directly into an
> access 2007 database, and then combining about 46 fields spread across the
> newly imported tables into one table.  The employee before me just used
> make table queries, but they don't allow you to set the field data type,
> size (for text fields), turn unicode compression on (text fields), or
> index certain fields... it seems the only way to do that is
> programmatically or manually, and the goal is to automate this process
> entirely and optimize the performance of the database at the same time.  I
> found out the hard way that once a table has say 800,000 records in it,
> you cannot change anything about the existing fields because access gives
> you an error message saying that it doesn't have enough resources (and my
> computer has 8gb of ram with a quad-core processor).  So the solution is
> in creating and setting up the table with code before any records are
> inserted into it.
>
> Which leads me to the next question:  I want to automate the import
> process also, where I am importing the delimited text files into access
> initially.  I'm sure this can be done with code, just don't know how and
> am still looking.  I would like to write code in an host access database
> that takes the delimited text files (always the same name and always the
> same location), creates a new database with the name of the new database
> being the same always except that the current date be added to the end of
> the name, imports those delimited text files (with whatever settings I
> choose) into the new database, then creates my actual working table and
> inserts the data from my five delimited text file tables into the working
> table.   So in effect I would have a separate database that I use every
> month that totally automates the database processes for my company and
> creates my new database for me... that would be the sole function of said
> separate database.  If you can point me in the right direction then that
> would be great.
>
 
Hmm ... I don't think I have the full picture, but I do have a couple of
comments on things you've said.  Maybe they'll be helpful, maybe not.
 
First, when I want to import text files into a database, and particularly
when I have to combine information from multiple text files into one table,
I don't import the text files and then move data from the imported tables
into the final table(s).  Instead, I link to the text files and run append
queries based on those linked tables.  Doing it that way vastly decreases
the database bloating -- and I can see from your comments about the "not
enough resopurces" message that bloating is a problem in this case.
 
Second, any process that requires me to dynamically create tables and fields
is suspect.  I tend to do that only when I am running an upgrade process on
an installed database application.  If your process does require you to
create tables dynamically, but the structure of those tables can be known in
advance, why not create template tables (empty, maybe hidden) with the exact
field definitions and properties you need, and copy those tables using
DoCmd.CopyObject or DoCmd.TransferDatabase?
 
Third, are you talking about creating one or more new databases every month,
to contain the same sort of data?  If I were in your position, I'd be
looking hard at whether I can keep all months' data in a single database.
Is there never any need to compare data across months?  Is there no shared
data between the months?
 
If there's too much data for an Access database to hold multiple months,
maybe you ought to be looking at a SQL Server back-end to store it all.  But
before deciding that, I would make sure I minimize bloating in the import
process (as described above), delete any unneeded data from the import
process, and compact the final database, to see just how big the optimized
database really is.
 
 
 

Answer 5

David,

As another general tip, when doing these sorts of data imports (and I just had a project very similar to the kind of thing you're talking about), I did what you tried initially, only I had no probelms adding the indexes later.

I had to import data (from an external Access database rather than a text file, but the basic principals are the same here in terms of the logical steps as I also had to massage the schema into a uniform table structure), and I rant the first data imports into a completely UNINDEXED target table (this went reasonably fast, even - in one case - for a source database of 1.7GB,with indexes, and holding 4.5million rows of data). I then had no trouble in modifying the tabledef to add the requisite indexes (providing I was not going to overflow the 2GB limit in doing so, of course). I never ran out of resources other than the 2GB limit (and I engineered carefully for that).

I did everything in DAO as well (Access/Jet's native methods/format rather than ADO+ADOX).

 

Answer 6

Dirk,

Thanks so much for your assistance... I had found similar code in the help file as what you posted, but for some reason it just wouldn't cooperate with me and kept giving me an error message.  I copied and pasted the code directly from your response into a public function and tested it, and IT WORKS GREAT! :)

Of course the scenario I gave in my help request is a lot simpler than what I actually need to do, but it encompasses the actions I need to perform.  The reason I need to create and modify the tables like this is because I am dealing with home insurance policies, where I am importing over 800,000 records from multiple delimited text files directly into an access 2007 database, and then combining about 46 fields spread across the newly imported tables into one table.  The employee before me just used make table queries, but they don't allow you to set the field data type, size (for text fields), turn unicode compression on (text fields), or index certain fields... it seems the only way to do that is programmatically or manually, and the goal is to automate this process entirely and optimize the performance of the database at the same time.  I found out the hard way that once a table has say 800,000 records in it, you cannot change anything about the existing fields because access gives you an error message saying that it doesn't have enough resources (and my computer has 8gb of ram with a quad-core processor).  So the solution is in creating and setting up the table with code before any records are inserted into it.

Which leads me to the next question:  I want to automate the import process also, where I am importing the delimited text files into access initially.  I'm sure this can be done with code, just don't know how and am still looking.  I would like to write code in an host access database that takes the delimited text files (always the same name and always the same location), creates a new database with the name of the new database being the same always except that the current date be added to the end of the name, imports those delimited text files (with whatever settings I choose) into the new database, then creates my actual working table and inserts the data from my five delimited text file tables into the working table.   So in effect I would have a separate database that I use every month that totally automates the database processes for my company and creates my new database for me... that would be the sole function of said separate database.  If you can point me in the right direction then that would be great.

Thanks a lot for your time and assistance,

 

David

 

Answer 7

Kent,

Thank you so much for taking the time to assist me with this issue.  The code you provided is helpful as a reference should I ever need to use DAO, but it is much more work and much more code than using ADO.

 

Grateful for your assistance,

 

David

 

Answer 8

Rich,

Thank you for taking the time to respond to my question.  Unfortunately, your response didn't help me solve my problem, but I do appreciate the information you provided.  It is not a temp table I need to create, and it's actually a quite elaborate process I'm trying to set up, for which this is just a small step.  If you see my response to Dirk Goldgar, who's solution did exactly what I needed, you will get a better understanding of exactly what it is I'm working toward accomplishing, and hopefully you can possibly assist me with one of the next steps that are mentioned.

I'm grateful for you taking time out to assist me.

 

Sincerely,

 

David

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter