Cant import Money Data Type (1 Viewer)

[SmallTime]

Hi all

using access 2010 32 bit and sql server 2012 Developer edition on win 7

I'm having an issue running pass-through queries to a StoredProcedures that wont show anything in a Money datatype column. All other columns are fine with data showing as it should be in Access but the Money column is empty. However, the column is populated when I exec the SP in Management Studio.

I'm hoping not to have to the convert the data type to a decimal. Any ideas anyone?

 

[SmallTime]

I've come to the conclusion (rightly or wrongly) that the Money datatype in sql server is pretty useless when it comes to compatibility with access Currency datatype. In the end I gave up trying to make it work so have converted the server datatype to decimal and left the temp tables columns in access as currency.

 

[Rx_]

In Excel, the Currency display default for USD is 2 decimal points.
However, the calculation is on 4 decimal points. This becomes a big deal when calculating interest daily on accounts, then summing up a column. The totals can be off significantly from an accounting point of view.

This is for Access 2003:
http://office.microsoft.com/en-us/a...-database-and-access-project-HP005274573.aspx

A better overview of Data Types between common Databases
http://www.w3schools.com/sql/sql_datatypes.asp

Just wondering what driver you used to make the connection.
My favorite is SQL Server Native Client (based on ODBC). However, my regulatory emphasis is on temporal ranges and date management. Another department issues the notice of audits and yet another the accounting.
So far, I have been very happy with the conversions.

Another shameless plug: My preference for design is to design tables in MS Access 2010. Then use the free tool SQL Server Migration Assistant for Microsoft Access. So far, it has been very excellent to transform all aspects of an Access Table into SQL Server 2010. Basically, I am lazy and willing to let a perfectly good tool do my heavy lifting for me.

 

[SmallTime]

Hi

Interesting point about excel. However, my client needs are quite simple, nothing more than adding pounds and pence so 2 decimal places sufficed. Perhaps down the line I'll come up against a division or % but even then the margin of error wont be overly significant, a 100th works fine for this situation.

I've only just started converting this access db to sql back-end (following advice Hi-Tec Coach almost a year ago). Currently using ODBC but will convert to DSN-less connections very shortly.

Again taking advice from Hi-Tec Coach I built the SQL DB from scratch and although it was a little tricky at first it made things a lot clearer for me as time went on. I do prefer the manual way of doing things, simply consider it my purgatory for using access as the BE in the first place.