Access2007 - Sharepoint = can't edit records in Access

[twboiid]

I have a VB frontend app to an Access2007 database. Works fine, no problem.

I also need to have a Sharepoint page sharing the database so web users can
perform defined searches and see output. To do this, I used the Access2007
feature called "Move to Sharepoint". This created a Sharepoint list linked
to my Access database. The web page searches are working fine.

The problem is, as soon as I did the "move to sharepoint", my VB app can't
find the Access table anymore. Sometimes, Access itself says that it can't
find the table (even though the table is listed right there from within
Access), when I right click on the table name and tell it to open.

The error message I get (from both Access and VB) is: "The Microsoft Office
Access database engine could not find the object 'Table1'. Make sure the
object exists and that you spell its name and the path name correctly."

Does anyone know how to get around this?

Here is another arcane clue: When the database is first moved to
Sharepoint, everything works fine....even the VB app. No access problems.
Then, if I reboot the system and try again, the errors start happening. As
if it knew where to find the table at first, but then forgot when rebooting.

Any help would be greatly appreciated.

 

[Albert D. Kallal]

I have a VB frontend app to an Access2007 database. Works fine, no
problem.

I also need to have a Sharepoint page sharing the database so web users
can
perform defined searches and see output. To do this, I used the
Access2007
feature called "Move to Sharepoint". This created a Sharepoint list
linked
to my Access database. The web page searches are working fine.

The problem is, as soon as I did the "move to sharepoint", my VB app can't
find the Access table anymore. Sometimes, Access itself says that it
can't
find the table (even though the table is listed right there from within
Access), when I right click on the table name and tell it to open.

When you say the VB application, are you talking about code inside of the
access application? Or are you talking about some external application like
vb.net or vb6 that is trying to use access as the connector to sharepoint? I
think using the second scenario is rather risky, and not exactly a formula
for a lot of stability here.

It seems to me that if the access is connecting to SQL server, or
sharepoint, and you're writing some code in VB, then should not that visual
basic code connect to SQL server or SharePoint direct, and bypass MS access?

You might want to explain a little bit more what you're doing in visual
basic (and if you using vb.net, or VB6, or access VBA here). If you're using
visual basic inside an MS access, then you' should not have any problem
using the sharepoint table just like any other table in your
code. You'll notice that we launch MS access, if you do have any sharepoint
linked tables, in the lower right hand corner, you'll see a little
sharepoint "icon" that appears and says you are "on line".

So I suspect that there is some type of
caching or logon sequence that MS access uses, but is likely to be based on
the windows user id....

Here is another arcane clue: When the database is first moved to
Sharepoint, everything works fine....even the VB app. No access problems.
Then, if I reboot the system and try again, the errors start happening.
As
if it knew where to find the table at first, but then forgot when
rebooting.

As mentioned, I suspect when you run access and link the SP table,
some caching of the logon information does occur. However, you
mention that after rebooting your VB application does not work.
However, when you launch ms-access and try to use the linked
sharepoint tables, does it work correctly? In other words, it
sounds like the visual basic's not able to setup or use the
connection correctly here, but MS access still is?

You might want to give a few more details as to what you mean by the visual
basic application, is this a vb.net application, or the now 10+ years old
tool called VB6 from 1998?

 

[twboiid]

Thanks for the reply....

In order to simplify the problem, forget about the VB code for now and just
consider the problems I'm having with Access2007 (just simply trying to open
a table for viewing).

After using the "Move to Sharepoint" feature in Access2007, I can no longer
open the table from Access in order to edit or view records. As you
mentioned, the status message at the lower right in Access does say "Online
with Sharepoint". If I hover my mouse over the Table name in the Table list,
the tool tip text shows that the table resides on the sharepoint server.
But if I right click on the table name and tell it to open, it displays this
message: "The Microsoft Office Access database engine could not find the
object 'Table1'. Make sure the object exists and that you spell its name and
the path name correctly." When this message is cleared, another message is
displayed which says: "Microsoft Office Access can't open the table in
Datasheet view."

The funny thing is that every once it awhile, it will open in the normal
manner. I've tried all sorts of troubleshooting and I can't determine what
changes to cause it to work sometimes.

 

[Albert D. Kallal]

Thanks for the reply....

The funny thing is that every once it awhile, it will open in the normal
manner. I've tried all sorts of troubleshooting and I can't determine
what
changes to cause it to work sometimes.

Hum, if you right click on the table, and choose "share point options", try
launching the table using "open default view"

After you do the above...then try opening the table. The above is a "quick
test" I often use to ensure I have permissions and ability to open the table
in question.....

Doing the above also ensures you logged onto the SharePoint site. After you
open the "web view" of the table, then try clicking on the access linked
table to see if that works....

 

[twboiid]

Hum, if you right click on the table, and choose "share point options", try
launching the table using "open default view"

After you do the above...then try opening the table. The above is a "quick
test" I often use to ensure I have permissions and ability to open the table
in question.....

Doing the above also ensures you logged onto the SharePoint site. After you
open the "web view" of the table, then try clicking on the access linked
table to see if that works...

Checked this morning. Still getting the error finding Table1 as I mentioned
earlier when I try to open from Access in the normal way (Right Click - "Open
Table")

Then, I tried your suggestion (Right Click from Access - Sharepoint List
Options - Open Default View). This works fine, however it just opens up a
Sharepoint Window and opens the list from the Sharepoint document library.
So the permissions are being recognized properly.

With the list still open in Sharepoint, I went back to Access and tried the
normal way of opening the table again, and still got the error.

Additional Info of Interest: As you may recall, my original goal had been
to set up an existing VB6.0 App with Access2007 database, to work with a
shared Sharepoint list to allow dynamic web page generation to display a
filtered list from this database. The VB6.0 App was using DAO routines to
talk with its database. (I know, VB6.0 and DAO are very old.)

The interesting thing is that the VB6.0 code is currently working properly
without any errors. It is sending read/write/ and SQL commands through
Access to the Sharepoint list. And my dynamic web page generation is also
working properly on its end.

The only piece of the puzzle I am missing is to be able to open the table
directly in Access when needed to perform periodic table maintenance, if
desired.

 

[Albert D. Kallal]

The only piece of the puzzle I am missing is to be able to open the table
directly in Access when needed to perform periodic table maintenance, if
desired.

I not experienced the above. As mentioned, try deleting, and re-linking.
Also, make sure you installed office sp2 update, as it fixes a lot of
issues.....

You could try some of this on a differnt list (and perahps differnt sp
site). Perhaps some setting on the SharePoint side is messed up, but that a
long shot since the right click and "web view" of the table works....

 

[david]

Sharing problem? Have you made sure that only 1 application
is using the file?

(david)

 

[twboiid]

Yup, only 1 app was hitting the data file. Everything is still in
development so I'm the only one using it at this point. As of today, nothing
is working again.

Albert: You mentioned in your first post that perhaps I should have my VB
App query the Sharepoint list directly. This might be my best option at
this point, however I haven't done this before. Any chance you might have
any VB type code snippets on connecting to a Sharepoint list and opening a
recordset object? ....either ADO or DAO, doesn't matter to me.