Discussion Locked

Creating Access Tables from VB

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am attempting to create tables dynamical out of a VB program running SQL. The problem I am having I keep getting a SQL syntax error for my numeric columns. The syntax I have been using is "field_1 number(6,0), field_2 number(18,2), field_3 number (5,4)".

I know "field_1 number" works but I need to specific several fields with different decimal precision. What is the correct format to use in ACCESS.

8 replies

by donq

Your best bet may be to convert everything withoiut worrying about decimal accuracy. Once you can open Access and before running anything open each Table in Desgn View and scan each Numeric Field's "Decimal Places" property (set to Auto by default). Your import (or conversion) won't change anything and every Numeric field should be checked for BOTH Data Type (Single, Byte, Double, etc.) accuracy AND Decimal Places accuracy.
There is no known "bug" but conversions between these tow Microsoft products does funny stuff quite often.

Good Luck.

p.s. The checking process is very fast and easy once you locate your first records "Field Size" and "Decimal Places" properties.

by lo

Interesting question. Just tried to put in in 18.2 (1234567890123456.78) with Access in design. Won't work as a long (1234567890.12 was bigest). Defined as Double and that worked OK for 18.2

Not sure how to specify. There is an Auto as a property for decimal. May want to use that if decimal isn't same for all values in a column. Option is to do what #1 said, pre-process the data and determine what the largest field and number of decimal positions is, then use that to define field. Notsure how to specify field properties, should be in the Help (somewhere).

by m_ozog

You can't specify a decimal precision in Access. It can only store numeric values with decimals as either Single or Double.

Funny things happen in Access with decimals. It may not store your exact number. (I currently have touble with this). Ex. You enter 18.42 but Access really stores 18.41998423423423.

Just for grins, run a summation query on a field that only has 2 decimal places. Why do you get sums with values out past the thousandths place?

No answer for you, just my input.
Matthew Ozog
Excellis
mozog@excellis.com

This discussion is closed to new replies.