Money value disappears

Posted on 2009-04-28
Last Modified: 2012-05-06
I have an Access 2002 mdb front end linking via DSN to SQL Server backend. When i enter values into a currency field via the front end they are not saved in the SQL Server back end. I can manually enter them into the tables in SQL Server and they are visible in SQL Server, but when I open the linked table in Access, the fields appear blank. It must be an error with DSN or ODBC but I don't know what to do about it. Can anyone help me please?  
Question by:NicholasSmith
    LVL 84
    And you're confident that you've set the ControlSource of the form's Textbox to the correct field in your SQL Server table? Are you entering valid values for that field? Are you certain that your SQL Server field is set to the correct datatype?
    LVL 10

    Expert Comment

    Is the write permission correct on the sql database? If user is read only then you won't be able to write back to db - this setting is done in sql server (not an access setting).
    Just as a point of note, when you connect to an odbc database using access, it's better to use a Passthorugh query, rather than link the table, this is because a linked table will process through the access jet engine, and reads all the table's data for each transaction - making it very inefficient and slow.  A passthrough query gets around this.
    It may be better to set your money field by writing a passthourgh querydef in vba that runs the update or append directly on the remote server - bypassing the jet engine. This may also solve your above problem...
    LVL 1

    Author Comment

    the datatype on sql server is set to Money and the control source in access is on a bound form
    LVL 1

    Accepted Solution

    it must have been a problem with the way the database was set up orginally as i created a new database and didnt have any more problems

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now