?
Solved

How to return the field type and field size of a table

Posted on 2014-10-20
6
Medium Priority
?
263 Views
Last Modified: 2014-10-20
I am trying to determine the data type and size of fields in a specified table.
I am using the following code to return the list of fields in the table and write the information to table "TableFieldNames"

        Dim fld As DAO.Field
        Dim db As DAO.Database
        Dim rs2 As DAO.Recordset

        Set db = OpenDatabase(txtdatabasename)    
        Set rs2 = Currentdb.OpenRecordset("TableFieldNames")
        Currentdb.Execute ("DELETE TableFieldNames.* FROM TableFieldNames")

        For Each fld In db.TableDefs(cmbTable).Fields
              rs2.AddNew
              rs2!NamofTable = cmbTable
              rs2!NameOfField = fld.Name
              rs2!TypeofField = ??????????
              rs2!SizeofField = ?????????
              rs2.Update
        Next fld
        rs2.close

In rs2!TypeofField I want to put the specified data type for the field (Text, Number, Date/Time, etc.)
If the data type is Text or Number I want to put the specified field size in rs2!Sizeoffield (255, integer, double, etc.)
Is this doable?
0
Comment
Question by:dsoderstrom
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40392510
use

              rs2!TypeofField = fld.type
              rs2!SizeofField = fld.size
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40392521
see this link to get the field description and other attributes

http://allenbrowne.com/func-06.html
0
 

Author Comment

by:dsoderstrom
ID: 40392538
I tried using fld.type and fld.size but what I get is numbers.
For example, a field that has a data type of "Text" returns a "10" for the field type.  The field size returns correctly.
A field with a data type of "Number" and a field size of "Double" returns "7" for the datatype and "8" for the field size.
Is there a way to return the actual data type and size as it shows in the field properties of the table?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40392548
look at my second post..
0
 

Author Comment

by:dsoderstrom
ID: 40392554
Yep, sorry.  Responded to your first one before I got the second one.
Just trying it now.
0
 

Author Closing Comment

by:dsoderstrom
ID: 40392570
this was just what I needed.
Thanks once again.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question