Tuesday, January 22, 2013 11:07 PM
I want to use Access as a front-end to build some reports but the SQL tables have some fields defined as bigint. We're not in a position at this time to redefine the fields as numeric.
I've read that I can create a view and link that to the front-end just like a table but how do I change the data type of some of the fields? The help shows I can CREATE VIEW [< owner > . ] view_name [ ( column [ ,...n ] ) ] but how do I define the column data types?
Or if I create a pass-through query same question, how to I change the data type so Access doesn't give me the #DELETE.
Thanks in advance,
Wednesday, January 23, 2013 3:03 PM
You simply cast the column (or expression) to the desired value. When doing so, you should consider very carefully the domains of the two datatypes - you will generate an error at runtime when you attempt to "squeeze" a larger value into datatype that is too small.
create view xxxx as select cast(mybigintcol as int) as myintcol, .... from mytable ...
I have no idea what your second question means, so I'll suggest that you post that to an forum that is specific to MS Access.
- Marked As Answer by cpsaltis Saturday, January 26, 2013 4:12 AM
Saturday, January 26, 2013 4:12 AM
Scott, thank you. That took care of it. I wish MS help would have explained that.
I'm casting a bigint into numeric(18,0) so I won't have a problem with the data types being too small.
The issue with Access is that it doesn't recognize the bigint data type which is why it gives you a #DELETE when you attempt to view the table. By creating a View and casting the data type to Numeric it allows me to do the rest. Now I can build my queries and reports without issue.