Changing field data type to use in Access

Question

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,

Answer 1

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.

Answer 2

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.

Thanks again.