General discussion

Locked

Creating Access Tables from VB

By jkra ·
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.

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Creating Access Tables from VB

by donq In reply to Creating Access Tables fr ...

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.

Collapse -

Creating Access Tables from VB

by jkra In reply to Creating Access Tables fr ...

The question was auto-closed by TechRepublic

Collapse -

Creating Access Tables from VB

by Lo In reply to Creating Access Tables fr ...

Interesting question. Just tried to put in in 18.2 (1234567890123456.7 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).

Collapse -

Creating Access Tables from VB

by Lo In reply to Creating Access Tables fr ...

Forgot - My Access is 97.

Collapse -

Creating Access Tables from VB

by jkra In reply to Creating Access Tables fr ...

The question was auto-closed by TechRepublic

Collapse -

Creating Access Tables from VB

by M_Ozog In reply to Creating Access Tables fr ...

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

Collapse -

Creating Access Tables from VB

by jkra In reply to Creating Access Tables fr ...

The question was auto-closed by TechRepublic

Collapse -

Creating Access Tables from VB

by jkra In reply to Creating Access Tables fr ...

This question was auto closed due to inactivity

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums