• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Money value disappears

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?  
0
NicholasSmith
Asked:
NicholasSmith
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
therealmongooseCommented:
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...
0
 
NicholasSmithAuthor Commented:
the datatype on sql server is set to Money and the control source in access is on a bound form
0
 
NicholasSmithAuthor Commented:
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
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now