I have several MS Access databases with tables containing fields that were set to the Data Type "Double" with decimal places set to "Auto"

Access somehow changed all these fields to the Data Type "Decimal". When my users tried to run imports, an error message was displayed saying the decimal field's precision was too small to accept the data

I resolved this easily enough by switching the Data Type back to "Double". But, does anyone know why this would happen? Why would Access change the Data Type to Decimal and set its own parameters for the Precision? It caused the app to fail

Is there any way to prevent Access from doing this?

  • So you mean that the actual table definition changed? Not just a query's format properties? – C Perkins Aug 23 '18 at 23:47
  • How do the users "run imports"? Access saved import steps? Or VBA routine? Or an external applicatoin? – C Perkins Aug 24 '18 at 0:05
  • Yes, the actual table definition changed from Double to Decimal. The imports are queries run against linked SQL Server tables to create local tables – Chris Elder Aug 27 '18 at 14:31
  • Access doing its own thing is not surprising, but I have never experienced the table definitions spontaneously changing... only certain properties of forms and queries when in design mode. Sorry no real answer, but could it be that a user accidentally imported not to an existing table, rather overwrote the table? Access is not at all secure when it comes to users manipulating things, and there would be no record of someone doing it. For instance, a novice user trying to find a way to format the numbers "how they like it"... not understanding the shared database concept. – C Perkins Aug 27 '18 at 16:02
  • Thanks for the info. Agreed, Access does throw some curve balls. Do you think upgrading from 2010 to Office 365 might have affected anything? It's hard to imagine an upgrade would redefine Table Defs, but it's the only thing I am aware of that has changed since the database was built – Chris Elder Aug 27 '18 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.