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

    Question

  • 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
    Tuesday, January 25, 2011 10:58 PM

Answers

  • "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.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by KyssMe143 Thursday, January 27, 2011 4:24 PM
    Wednesday, January 26, 2011 3:14 AM

All replies

  • 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, ...

    Wednesday, January 26, 2011 12:24 AM
  • "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.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by KyssMe143 Thursday, January 27, 2011 4:24 PM
    Wednesday, January 26, 2011 3:14 AM
  • 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
    Wednesday, January 26, 2011 3:19 AM
  • 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


    David Lee Wisniewski
    Thursday, January 27, 2011 4:41 PM
  • 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


    David Lee Wisniewski
    Thursday, January 27, 2011 4:43 PM
  • 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


    David Lee Wisniewski
    Thursday, January 27, 2011 4:48 PM
  • "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.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Thursday, January 27, 2011 6:16 PM
  • 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).


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Thursday, January 27, 2011 6:44 PM
  • Dirk, can you please tell me how to use VBA to export tables from Access 2007 to Excel 2007.  When I do it manually via the External Data ribbon it doesn't limit me to 65,000 records, but every way that I've tried via vba it limits me to 65,000 records.  Anything you can do in the Access GUI you can automate via VBA, but I just can't figure it out... could you point me in the right direction?
    David Lee Wisniewski
    Wednesday, February 02, 2011 10:11 PM
  • "KyssMe143" wrote in message
    news:bf3bf019-7425-4630-98de-30079dd8bab5@communitybridge.codeplex.com...
    > Dirk, can you please tell me how to use VBA to export tables from Access
    > 2007 to Excel 2007.  When I do it manually via the External Data ribbon it
    > doesn't limit me to 65,000 records, but every way that I've tried via vba
    > it limits me to 65,000 records.  Anything you can do in the Access GUI you
    > can automate via VBA, but I just can't figure it out... could you point me
    > in the right direction?
    >
     
    What have you tried?  I would expect this to work:
    '
       DoCmd.TransferSpreadsheet acExport,  _
           acSpreadsheetTypeExcel12Xml, _
           "YourBigTable", _
           "C:\Your Path To\YourBigTable.xlsx", _
           True
    '
    I'm not completely sure that the constant acSpreadsheetTypeExcel12Xml is
    defined in Access 2007, so if the above doesn't work, try this instead:
    '
       DoCmd.TransferSpreadsheet acExport,  _
           10, _
           "YourBigTable", _
           "C:\Your Path To\YourBigTable.xlsx", _
           True
    '
     
    Please let me know if neither of those works.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Thursday, February 03, 2011 4:26 AM