Problem with Access and linked List in Sharepoint

[ttaylor993]

I have an Access 2003 database that I am using with a number of Sharepoint
2003 Lists as linked tables. The Lists were created by importing into
Sharepoint the tables from a standalone version of the database. However,
when the Lists were created in SharePoint a number of columns were added i.e.
ID, Created By, Date Created, Modified By, Dated Modified (or something
similar). Becasue the Access "Tables" are now linked, I cannot delete or
rearrange the columns from the Access side. When I open the Lists from the
Sharepoint side, the only way to see the "extra" columns is through the View.
The check boxes are not checked for the "extra" columns yet they still show
on the Access side, but not the SharePoint side. How can it delete or
rearrange the extra columns. The problem is the order in which these "extra"
columns were added. Some of the tables are used as Combo Boxes and the
"extra" columns alter the drop-down list in the Combo Boxes.

 

[Albert D. Kallal]

Becasue the Access "Tables" are now linked, I cannot delete or
rearrange the columns from the Access side.

The above is normal behavior even when linked to sql server....

The check boxes are not checked for the "extra" columns yet they still
show
on the Access side, but not the SharePoint side. How can it delete or
rearrange the extra columns.

You could consider removing the fields on the SharePoint. (but that would
loose "tracking" and change logging that occurs with those fields. I
suspect that just about any SharePoint application does expect to find those
fields (so removing them is a last resort type of suggestion here). In fact
I never tried removing then and I not sure if SharePoint will let you delete
those fields anyway...

I am not really sure why there is any particular problem with the order of
fields.

It possible you are viewing or editing that data directly in the table view
on the access side. As a general rule you usually want to avoid doing that
since as you just found out you have little if any control over how the data
will be viewed or presented to your users.

The simple solution here is to build a query with the only the columns you
want (and a query also allows setting the order of those columns). Then you
can either use that query to edit your data or to build your forms, reports
etc with.

The problem is the order in which these "extra"
columns were added. Some of the tables are used as Combo Boxes and the
"extra" columns alter the drop-down list in the Combo Boxes.

Are you talking about a combo box on a form?
You can certainly modify/change what columns show for that combo
box by basing the combo box on a query.

As a "general" rule you want to do things in such a way that the
order of the fields never matters to you. Thus if you remove a field (or
add one) to the table then that change of order will not break any forms or
any part of your application you have now.

 

[ttaylor993]

Thanks for information Albert. I believe I asked the wrong question. I
assumed that because the additional columns were added to the Lists that was
the reason why the database would not update. The real issue is not about
the columns. The real question is why the database will not let me input new
data. I get the message that the Sharepoint version of the database is no
updatable. Yet the stand-alone version updates with no problem. Can you
tell me why the database will not update. Everything appears to be the same
except the additional columns added to the Lists.

 

[Albert D. Kallal]

Thanks for information Albert. I believe I asked the wrong question. I
assumed that because the additional columns were added to the Lists that
was
the reason why the database would not update. The real issue is not about
the columns. The real question is why the database will not let me input
new
data. I get the message that the Sharepoint version of the database is no
updatable.

When you say SharePoint version, do you simply mean the mdb on your desktop
that has linked tables to SharePoint?
(or do you mean the copy of the mdb placed on SharePoint).

I guess I first thing would be open up the SharePoint list direct, and see
it allows editing from the web page?
I sure you can, but you do want to test this issue just in case....

Then, I would place that mdb with linked sharepoint tables on your desktop.
If it don't work, then I would consider deleting the linked
table(s), and re-link them to sharepoint and see if that allows editing
now....

 

[ttaylor993]

Albert, when I say Sharepoint version, I mean the mdb on Sharepoint that is
linked to List on Sharepoint. If I am not mistaken, when I open the mdb on
Sharepoint, doesn't it create an instance on my PC with links to the Lists on
Sharepoint? I have a copy of the mdb with links to the Lists on Sharpoint on
my PC too. When I open it and click on one of the forms to enter data, the
same thing happens as with the Sharepoint version. I get the message that
"This recordset is not updatabale" just above the Task bar just before the
form opens. This does not happen with the version on my PC that uses the
tables on my PC rather than the Lists on Sharepoint. I will check the Lists
and see if they allow updating directly from the web page. Thanks again.

 

[Albert D. Kallal]

Albert, when I say Sharepoint version, I mean the mdb on Sharepoint that
is
linked to List on Sharepoint.

Right....I did try to make the case for your version on your computer, and
the one that often placed on SharePoint when you upsize to SharePoint.

If I am not mistaken, when I open the mdb on
Sharepoint, doesn't it create an instance on my PC with links to the Lists
on
Sharepoint?

It copies down the copy on SharePoint but I do not believe the links are
changed
or touched at that time. (so, that is why I just reffered to a copy of the
mdb
sitting on your computer that has linked tables to SharePoint.

I have a copy of the mdb with links to the Lists on Sharpoint on
my PC too. When I open it and click on one of the forms to enter data,
the
same thing happens as with the Sharepoint version. I get the message that
"This recordset is not updatabale" just above the Task bar just before the
form opens.

Right. So, get/grab a copy of that mdb from SharePoint and place it on your
computer. Then one of the linked tables to sharepoint, and then re-link htat
one talbe to see if it will work. (you don't have to re-upsize the whoel mdb
again).

I will check the Lists
and see if they allow updating directly from the web page. Thanks again.

Yes, in 2007 it easy to "launch" the SharePoint table by right clicking on a
table in the table tab (pane now in 2007 and you can then launch the "web
list" editing view that way. In 2003, you have to browse to the table from
SharePoint....