Solved

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

Posted on 2014-10-20
6
241 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 119

Expert Comment

by:Rey Obrero
ID: 40392510
use

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

Accepted Solution

by:
Rey Obrero earned 500 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 119

Expert Comment

by:Rey Obrero
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now