SQL server link Access converting decimal to long integer

[Hoardling via AccessMonster.com]

I have a SQL table that has two columns that are data type decimal. When I
link Access to these columns Access likes to convert the columns to long
integers. The problem is the data is correct in SQL Server, but when
displayed as a subform it rounds the data off. I want to see the decimal
places from data. Any ideas?

 

[Sylvain Lafontaine]

Hi, without beeing rude, you should ask your question in a newsgroup about
ODBC linked tables like m.p.access.odbcclientsvr or mpa.externaldata. This
newsgroup is about ADP and there is absolutely no relation between ODBC
linked tables and an ADP project.

When posting, you should also state the version and SP of Access and
SQL-Server used as well as the exact definition of the decimal type used
(length and precision) if you want other people to try to reproduce your
problem. Saying what format (standard, general, fixed, etc.) you are using
for these numbers would also be a good idea.

 

[Hoardling via AccessMonster.com]

Well, that wasn't the answer I was looking for. You seem to be promoting
these 2 sites. I noticed you go to many other sites, and general post the
same thing. Go to these 2 sites. I don't mean to be rude, but your promoting
of those sites make me think that something is up. If I am wrong, sorry.
That sometimes is the problem with the internet, people promote bad and good
things. It is up to the user to figure out which is which.

Now if you want more details, I can easily provide those details. I am
running SQL Server 2000 with an Access 2003 Front End via ODBC.
I simply have a SQL Server column with a data type of decimal with precision
10 and a scale of 2. I use a view in SQL. Access ODBC's this view. I
looked at the Access design of the table, and Access seems to have converted
the column from a decimal into a number with Long Integer type. I would have
normally made this a stored procedure, but it is used in a subform in Access.

 

[Sylvain Lafontaine]

I'm not promoting any site. I'm not the owner of these sites and
personally, you can come around and post about any topic (Access, Excel,
Word, Linux, your personal life or your sexual life, whatever), I don't
care. However, must (but not all) people that come around here are for
searching an answer to some technical problem(s) related to a product sold
by Microsoft and the best way to them to get an answer (when there is one)
to their problem(s) is to post it to the right newsgroup or to a newsgroup
whose topic is not to far from it.

Like I said to you in my previous post, ADP don't have ODBC linked tables
and most (but not all) people who are working with ADP don't know anything
advanced about ODBC linked tables. So if you want to have the best chance
to get an answer to your problem about ODBC linked tables, the (best) thing
to do would be to post to a newsgroup whose topic is precisely about ODBC
linked tables.

Having said that, there is a lot of people with a knowledge of both ADP and
ODBC linked tables; so you can keep continuing to post here and there is a
good chance that someone with both a knowledge of ODBC linked tables and
ADP/SQL-Server will know the answer to your problem and reply to you
directly in this exact newsgroup. It happens quite frequently.

Finally, in your case, if this View is read-only then maybe your should
convert your decimal value to a string type.

 

[Hoardling via AccessMonster.com]

Ok, perhaps we are getting off on the wrong foot here. I didn't mean to be
rude, but as an IT professional we should know that many people post
advertising and junk on this site. Now, when someone says go to this site, I
don't just jump right away and go. I don't enjoy getting viruses and other
nasty stuff. Just being cautious.

I am honestly not trying to mispost information here, but I am looking for
something that involved SQL Server 2000 and Access 2003. I was under the
impression that this site had to deal with Access and SQL Server. So I have
no intention of posting non Access related material here.

To address your other information about going to newsgroups, what is the full
URL?
I did try googling the groups that you provided, and I wasn't able to find
the sites. So can you please provide that information for me and other
readers? Thanks

Last note, the information you provided will work. I found another
resolution to the problem not too long afterwards. Honestly, Thank you for
providing an answer. For any readers what I did was created a stored
procedure of the information I needed. I then created a pass through in
Access to the stored procedure. In the desired form tab, I created a listbox.
The listbox's record source uses the pass through statement. Then in the
forms VBA code under Current I added this code.

'ParentElement represents the Parent link field to the listbox
If Not IsNull("ParentElement".value) Then
Me."listboxName".Requery
End If

I'm not promoting any site. I'm not the owner of these sites and
personally, you can come around and post about any topic (Access, Excel,
Word, Linux, your personal life or your sexual life, whatever), I don't
care. However, must (but not all) people that come around here are for
searching an answer to some technical problem(s) related to a product sold
by Microsoft and the best way to them to get an answer (when there is one)
to their problem(s) is to post it to the right newsgroup or to a newsgroup
whose topic is not to far from it.

Like I said to you in my previous post, ADP don't have ODBC linked tables
and most (but not all) people who are working with ADP don't know anything
advanced about ODBC linked tables. So if you want to have the best chance
to get an answer to your problem about ODBC linked tables, the (best) thing
to do would be to post to a newsgroup whose topic is precisely about ODBC
linked tables.

Having said that, there is a lot of people with a knowledge of both ADP and
ODBC linked tables; so you can keep continuing to post here and there is a
good chance that someone with both a knowledge of ODBC linked tables and
ADP/SQL-Server will know the answer to your problem and reply to you
directly in this exact newsgroup. It happens quite frequently.

Finally, in your case, if this View is read-only then maybe your should
convert your decimal value to a string type.

Well, that wasn't the answer I was looking for. You seem to be promoting
these 2 sites. I noticed you go to many other sites, and general post the

[quoted text clipped - 37 lines]

 

[Douglas J. Steele]

Just to clarify, Sylvain isn't actually talking about any site. You've
posted to a newsgroup, and Sylvain was suggesting that your post would be
better placed in a different newsgroup. Now, I see you're posting through
AccessMonster, who appear to do a good job of hiding the fact that you're
actually posting to public newsgroups that aren't actually run by
AccessMonster.

The specific newsgroup to which you've posted is
microsoft.public.access.adp.sqlserver, which AccessMonster appears to label
"SQL Server / ADP". Unfortunately, it would appear that AccessMonster has
chosen not to use the feeds of all of the Access newsgroups under the
microsoft.public.access umbrella.

If you prefer using a web interface (believe me, newsgroup readers such as
Outlook Express, Thunderbird or Forte Agent are far superior!), go to
http://msdn.microsoft.com/newsgroups/, and look under Access under Office
Solutions Development on the left-hand side of the screen. Alternatively,
you can go to http://groups.google.com and find the newsgroups to which
Sylvain was referrring.

If you do want to use a newsgroup reader, point to msnews.microsoft.com
(there's no username nor password required)

 

[Hoardling via AccessMonster.com]

Thank You.

Just to clarify, Sylvain isn't actually talking about any site. You've
posted to a newsgroup, and Sylvain was suggesting that your post would be
better placed in a different newsgroup. Now, I see you're posting through
AccessMonster, who appear to do a good job of hiding the fact that you're
actually posting to public newsgroups that aren't actually run by
AccessMonster.

The specific newsgroup to which you've posted is
microsoft.public.access.adp.sqlserver, which AccessMonster appears to label
"SQL Server / ADP". Unfortunately, it would appear that AccessMonster has
chosen not to use the feeds of all of the Access newsgroups under the
microsoft.public.access umbrella.

If you prefer using a web interface (believe me, newsgroup readers such as
Outlook Express, Thunderbird or Forte Agent are far superior!), go to
http://msdn.microsoft.com/newsgroups/, and look under Access under Office
Solutions Development on the left-hand side of the screen. Alternatively,
you can go to http://groups.google.com and find the newsgroups to which
Sylvain was referrring.

If you do want to use a newsgroup reader, point to msnews.microsoft.com
(there's no username nor password required)

Ok, perhaps we are getting off on the wrong foot here. I didn't mean to
be

[quoted text clipped - 4 lines]

other
nasty stuff. Just being cautious.

 

[Sylvain Lafontaine]

Oh, I'm sorry for the confusion. I've just took a look at the web site
AccessMonster and indeed, this site seems to have been designed with the
primary purpose of bringing a lot of confusion amongst the users of
newsgroups. You can find the groupes that I mentionned on Google on:

http://groups.google.ca/group/microsoft.public.access.odbcclientsvr/topics
http://groups.google.ca/group/microsoft.public.access.externaldata/topics

or by going to http://msdn.microsoft.com/newsgroups/

However, like Douglas mentionned, you should use a newsgroup reader: when
you will have a taste of a real newsgroup reader, you won't be willing to
hear anything about any web based newsgroups reader anymore.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank You.

Just to clarify, Sylvain isn't actually talking about any site. You've
posted to a newsgroup, and Sylvain was suggesting that your post would be
better placed in a different newsgroup. Now, I see you're posting through
AccessMonster, who appear to do a good job of hiding the fact that you're
actually posting to public newsgroups that aren't actually run by
AccessMonster.

The specific newsgroup to which you've posted is
microsoft.public.access.adp.sqlserver, which AccessMonster appears to
label
"SQL Server / ADP". Unfortunately, it would appear that AccessMonster has
chosen not to use the feeds of all of the Access newsgroups under the
microsoft.public.access umbrella.

If you prefer using a web interface (believe me, newsgroup readers such as
Outlook Express, Thunderbird or Forte Agent are far superior!), go to
http://msdn.microsoft.com/newsgroups/, and look under Access under Office
Solutions Development on the left-hand side of the screen. Alternatively,
you can go to http://groups.google.com and find the newsgroups to which
Sylvain was referrring.

If you do want to use a newsgroup reader, point to msnews.microsoft.com
(there's no username nor password required)

Ok, perhaps we are getting off on the wrong foot here. I didn't mean to
be

[quoted text clipped - 4 lines]

other
nasty stuff. Just being cautious.

 

[nutter]

Ok, perhaps we are getting off on the wrong foot here. I didn't mean to
be
rude, but as an IT professional we should know that many people post
advertising and junk on this site. Now, when someone says go to this
site, I
don't just jump right away and go. I don't enjoy getting viruses and
other
nasty stuff. Just being cautious.

I am honestly not trying to mispost information here, but I am looking for
something that involved SQL Server 2000 and Access 2003. I was under the
impression that this site had to deal with Access and SQL Server. So I
have
no intention of posting non Access related material here.

To address your other information about going to newsgroups, what is the
full
URL?
I did try googling the groups that you provided, and I wasn't able to find
the sites. So can you please provide that information for me and other
readers? Thanks

Last note, the information you provided will work. I found another
resolution to the problem not too long afterwards. Honestly, Thank you
for
providing an answer. For any readers what I did was created a stored
procedure of the information I needed. I then created a pass through in
Access to the stored procedure. In the desired form tab, I created a
listbox.
The listbox's record source uses the pass through statement. Then in the
forms VBA code under Current I added this code.

'ParentElement represents the Parent link field to the listbox
If Not IsNull("ParentElement".value) Then
Me."listboxName".Requery
End If

I'm not promoting any site. I'm not the owner of these sites and
personally, you can come around and post about any topic (Access, Excel,
Word, Linux, your personal life or your sexual life, whatever), I don't
care. However, must (but not all) people that come around here are for
searching an answer to some technical problem(s) related to a product sold
by Microsoft and the best way to them to get an answer (when there is one)
to their problem(s) is to post it to the right newsgroup or to a newsgroup
whose topic is not to far from it.

Like I said to you in my previous post, ADP don't have ODBC linked tables
and most (but not all) people who are working with ADP don't know anything
advanced about ODBC linked tables. So if you want to have the best chance
to get an answer to your problem about ODBC linked tables, the (best)
thing
to do would be to post to a newsgroup whose topic is precisely about ODBC
linked tables.

Having said that, there is a lot of people with a knowledge of both ADP
and
ODBC linked tables; so you can keep continuing to post here and there is a
good chance that someone with both a knowledge of ODBC linked tables and
ADP/SQL-Server will know the answer to your problem and reply to you
directly in this exact newsgroup. It happens quite frequently.

Finally, in your case, if this View is read-only then maybe your should
convert your decimal value to a string type.

Well, that wasn't the answer I was looking for. You seem to be
promoting
these 2 sites. I noticed you go to many other sites, and general post
the

[quoted text clipped - 37 lines]

decimal
places from data. Any ideas?