Create table - DAO.Property errors (1 Viewer)

[allgirlband]

I don't know why I am struggling with this. I do create the table, and I do populate it with data, but errors occur . This is VBA from an Access module.
I DOES make the table, but when I try and populate the MONTHS with 0.0000 there are just 0's in there - the error is 3270 Property not found DAO.Property

TWO errors pop up - 3270 twice. I have set them all to text, still an error!


With myTable
.Fields.Append .CreateField("CATEGORY", dbText)
.Fields.Append .CreateField("RESOURC", dbText)
.Fields.Append .CreateField("TREATMENT", dbText)
.Fields.Append .CreateField("LBLS", dbText)
.Fields.Append .CreateField("SORTORDER", dbInteger)
.Fields.Append .CreateField("SCENARIO_ID", dbInteger)
.Fields.Append .CreateField("MONTH_ID", dbInteger)

For x = 0 To UBound(MyArray, 1) - 1
.Fields.Append .CreateField(MON_COLS(x, 0), dbDouble)
Next x
End With

db.TableDefs.Append myTable

I used the debug window:

CATEGORY Text from myTable.CATEGORY
RESOURC Text from myTable.RESOURC
TREATMENT Text from myTable.TREATMENT
LBLS Text from myTable.LBLS
SORTORDER Integer from myTable.SORTORDER
SCENARIO_ID Integer from myTable.SCENARIO_ID
MONTH_ID Integer from myTable.MONTH_ID
JUL 12 Double from myTable.JUL 12
AUG 12 Double from myTable.AUG 12
SEP 12 Double from myTable.SEP 12
OCT 12 Double from myTable.OCT 12
NOV 12 Double from myTable.NOV 12
DEC 12 Double from myTable.DEC 12
JAN 13 Double from myTable.JAN 13
FEB 13 Double from myTable.FEB 13
MAR 13 Double from myTable.MAR 13
APR 13 Double from myTable.APR 13
MAY 13 Double from myTable.MAY 13
JUN 13 Double from myTable.JUN 13

 

[G37Sam]

What exactly are you trying to achieve here? Sounds like your data model isn't very solid.

 

[allgirlband]

Hi - I have a (complicated) query that creates a new table from 3 joins. I've made this table, defined the fields, can load data, but I get the DAO.property error and I have no clue - also wondering how to have table data store numbers with four significant figures - so I define the table, make the fields, and when I stuff in the data I get these errors, but the data goes in anyway ... "use sql server like constructs" is on, so I suppose behind the scenes MS is trying to shoehorn my data into the correcty formated data-type fields, but I can't expose exactly what it doesnt like - I am doing this inside ACCESS with a module so I am using DAO because that's native to ACCESS as I understand it ...

 

[G37Sam]

In my 10 years of working with access I have never had the need to create tables in run-time. Can you explain why you're doing that?

Also, access understands decimal places rather than significant figures. You can format your numbers to show in scientific format as in 5.32e-3 if you want.

 

[allgirlband]

Hi -

I have an Excel front end to and Access back end. Rather than mucking around with recordsets for LOTS of user inputs, I am instead importing ACCESS tables into Excel, then pushing EXCEL spreadsheets up into ACCESS as tables. All the while I call necessary ACCESS vba modules from EXCEL. It's working, except when EXCEL opens the ACCESS I have to click on the icon, hit the button to acknowledge the error popups generated by ACCESS, then have the thing go to completion. I have to fix this issue. Just in a friendly way, I have this working, SQL is my main worker, not recordsets, I just have to create this table and not get DAO.Property errors 3207 which I can trap but can't figure out how to fix.

I've found a solution where I move tables and sheets, not rows ... it's due to the sheer volume of data transfer/calculation ...