MS SQL Server ODBC - RE: SQL 2000 and Access 2002 front end

Author

RE: SQL 2000 and Access 2002 front end

Simon McLaren

2005-04-01, 8:01 pm

One possibility would be that after linking the table the table has been
renamed in the ACCESS file. This is sometimes done when an access DB is
upgraded or migrted into a SQL Server backend. Keep in transitions like this
are usually begun by folks in the organization who are learning as they go...
so they may choose to adopt so naming standards in SQL that they never used
in Access... At any rate, you could seeone name in access and it could be
linked to a table names something else in the SQL DB.

Possiblity two - same cause as above, but the access linked table could also
be inlinked to a view in SQL Server - again the link could be renamed to
conform to the naming convention in the access db.

As for having to relink tables to edit records. Does this tend to happen
after you have changed the structure of the table in SQL Server... Usually
when you have added field. I know that in Access 2000 one usually had to
drop and relink a table/view to see and use recently added fields in
underlying table/view. This tends to happen particualy when the new field
noes not allow nulls and since access can not see it it can not place a value
into that field...

any of this sound like what might be happening in your case?

"Xavier" wrote:

> I have a web based application where my reports are run from an access front
> end
> (don't ask but the people who own the application did it this way) my issue
> is the following I have a report that shows all the proper records but:
> If I look at the linked tables used in this report the data does not match
> what is in the tables in SQL.
>
> What could cause a linked table not to show the same information as the
> table in SQL?
>
> I also have an issue where sometimes my database will not allow me to edit a
> record till i delete the linked table and relink it.
>
> any help is greatly apprciated.

XavierVP

2005-04-02, 7:01 am

Simon

No this would not be the case, the table is linked from Access 2002 directly
to the SQL table using ODBC, with no changes at all it will work now and you
close the Access front end and open it again and it will not let you edit in
any way shape or form, if I drop the table and relink it then I can continue
doing work.

another issue I have noticed is that if I run a query in access with a SQL
linked table it does not show me the correct contents of the table but if I
go into enterprize Manager tha data is all there.

what else would you think to look for, Im not an expert in SQL but I can
work my way around it I have been using Access since Ver 2 and Im happy with
it but I need the SQL behind it because of a webbased application which is
what is used for 99% of all this data entry and Access is just a very easy
reporting tool for me to produce
all my needed reports on the fly.

thanks
Xavier

"Simon McLaren" wrote:
[color=darkred]
> One possibility would be that after linking the table the table has been
> renamed in the ACCESS file. This is sometimes done when an access DB is
> upgraded or migrted into a SQL Server backend. Keep in transitions like this
> are usually begun by folks in the organization who are learning as they go...
> so they may choose to adopt so naming standards in SQL that they never used
> in Access... At any rate, you could seeone name in access and it could be
> linked to a table names something else in the SQL DB.
>
> Possiblity two - same cause as above, but the access linked table could also
> be inlinked to a view in SQL Server - again the link could be renamed to
> conform to the naming convention in the access db.
>
> As for having to relink tables to edit records. Does this tend to happen
> after you have changed the structure of the table in SQL Server... Usually
> when you have added field. I know that in Access 2000 one usually had to
> drop and relink a table/view to see and use recently added fields in
> underlying table/view. This tends to happen particualy when the new field
> noes not allow nulls and since access can not see it it can not place a value
> into that field...
>
> any of this sound like what might be happening in your case?
>
> "Xavier" wrote:
>

Simon McLaren

2005-04-03, 7:01 am

Xavier,

Curious, have you tried runing the Access front end on more than one
computer?

The next thing I would look at is the way you linking to the SQL server. It
is possible to creat DSN as User, System or File DSN. You might try using
the System or File DSN. While this does not seem that it would make a
defference, I know I have had some "strange" experiances using the user
version of the DNS. But those issues usually relate to the ability of other
users to utilize the Access file.

Also ensure the SQL table has a key identified.. While it seems unlikely
that you would not alreay have done this, I figure it's worth asking... I
think this could cause a "missing" record issue, unlikely, but possible.

In all honesty in sounds like you could have an old version of the MDAC
installed on the computer you are running Access on, or some setting in
Access 2002 needs to be tweeked... I am shooting in the dark here. The last
big project I worked was using SQL Sever 2000 and Access 2000... I am not
familiar with Access 2002 or it differences from 2000.

Simon

"XavierVP" wrote:
[color=darkred]
> Simon
>
> No this would not be the case, the table is linked from Access 2002 directly
> to the SQL table using ODBC, with no changes at all it will work now and you
> close the Access front end and open it again and it will not let you edit in
> any way shape or form, if I drop the table and relink it then I can continue
> doing work.
>
> another issue I have noticed is that if I run a query in access with a SQL
> linked table it does not show me the correct contents of the table but if I
> go into enterprize Manager tha data is all there.
>
> what else would you think to look for, Im not an expert in SQL but I can
> work my way around it I have been using Access since Ver 2 and Im happy with
> it but I need the SQL behind it because of a webbased application which is
> what is used for 99% of all this data entry and Access is just a very easy
> reporting tool for me to produce
> all my needed reports on the fly.
>
> thanks
> Xavier
>
> "Simon McLaren" wrote:
>

Sponsored Links