How to extract Access table schema with decimal places on numeric fields?
How to extract Access table schema with decimal places on numeric fields?
(OP)
In a VBA module within an Access database I am trying to create a query which needs the schema information for a table. I can get the field names, type and size but cannot get the decimal places for the various numeric types. Anyone have a clue how to get the field properties as seen in design mode?
Following are a couple of failed tries which do not give me the decimal places for the various fields.
TIA, Mark
Following are a couple of failed tries which do not give me the decimal places for the various fields.
CODE
Private Function GetSchema1(tcTable As String) Dim oDatabase As Database, oTabledef As TableDef Set oDatabase = CurrentDb() Set oTabledef = oDatabase.TableDefs(tcTable) For Each oField In oTabledef.Fields a = 1 Next oField End Function
CODE
Private Function CreateSQL(tcTable As String) Dim cSQL As String, oDatabase As Database, oRecordset As Recordset cSQL = "SELECT * FROM " + tcTable + " WHERE 1=0" Set oDatabase = CurrentDb() Set oRecordset = oDatabase.OpenRecordset(cSQL, dbOpenDynaset) Dim iFields As Integer, oField As Object, cFieldName As String For iFields = 0 To oRecordset.Fields.Count - 1 Set oField = oRecordset.Fields(iFields) With oField cFieldName = .Name Select Case .Type Case Is = dbBigInt End With Next iFields End Function
Talk To Other Members
RE: How to extract Access table schema with decimal places on numeric fields?
CODE
For iFields = 0 To oRecordset.Fields.Count - 1 Set oField = oRecordset.Fields(iFields) With oField cFieldName = .Name Select Case .Type Case Is = dbBigInt End Select End With Next iFieldsSkip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: How to extract Access table schema with decimal places on numeric fields?
You can do a simple test to find properties with code like the following which is not meant for production and will error out if allowed.
CODE --> vba
Sub PrintProperties(strTable As String, strField As String) Dim db As DAO.Database Dim td As DAO.TableDef Dim fd As DAO.Field Dim intProp As Integer On Error Resume Next Set db = CurrentDb Set td = db.TableDefs(strTable) Set fd = td.Fields(strField) For intProp = 1 To fd.Properties.Count Debug.Print fd.Properties(intProp).Name, fd.Properties(intProp).Value Next Set fd = Nothing Set td = Nothing Set db = Nothing End SubDuane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016