'Created By' names are now numbers in Access 2007

L

Libbytw

Using Access 2003 to import data from a SharePoint List, it pulled in Created
By and Modified By data from the SharePiont site. Once I converted to Access
2007, although the same activity runs via linked tables, I no longer get the
names in these fields. Now I get only numbers.

I've tried also importing the User Information List but don't know how to
link the information. In one of my SharePoint Lists I also have some data in
LookUp tables. This data also comes into the linked table as numbers.

If I create an Imported Table instead of a Linked Table the names are pulled
in. But I need the functionality of the Linked Table. How can I get the
names to appear in my linked table?

I trust MS has not taken a step backward in functionality - it must be a new
feature I'm not familiar with. If someone could just explain it to me I
would be greatful.

Thank you,
 
A

Albert D. Kallal

Using Access 2003 to import data from a SharePoint List, it pulled in
Created
By and Modified By data from the SharePiont site. Once I converted to
Access
2007, although the same activity runs via linked tables, I no longer get
the
names in these fields. Now I get only numbers.
Hum, I not aware of a change here.

When you link to sharepoint lists using 2007, then some the attachment
fields etc. don't show when you open up the linked table direct in access.
If you build query based on that linked table...you then see all of the
collums.

I don't think the above is your problem....

I would try deleting the linked table..and then re-linking it to see what
you get....

I not seen this behavior you speak of...so, somthing seems a miss here...
 
L

Libbytw

I've recreated the Linked Tables 3 or 4 times without any difference. I
think I understand now. I went and looked at the Linked Table and sure
enough those fields are not there.

What I'm trying to do is link to a SharePoint List via an Linked Table (9 of
them on different SharePoint sites), then do a Append Query on each of the 9
Linked Tables appending the results into one table. It's in this resulting
table where the Created By and Modified By fields have numbers...where they
used to have names.

I'm not a skilled database manager. I know enough to do what I need. So
when converting to Access 2007 I thought it was the Linked Table not bringing
in the data from SharePoint. Now I'm wondering where the data came from. Is
the Query going out the the SharePoint site? Should I be recreating the
queries.
 
A

Albert D. Kallal

Libbytw said:
I've recreated the Linked Tables 3 or 4 times without any difference. I
think I understand now. I went and looked at the Linked Table and sure
enough those fields are not there.

And, try bringing up the linked table in design mode. (you get a prompt
about this being read only), but answer yes..and bring the table into design
mode....do you see the other columns?

? Now I'm wondering where the data came from. Is
the Query going out the the SharePoint site?

If the query based on a linked table to SharePoint, then that is data source
(so, yes).
Should I be recreating the
queries.

Yes, I would try re-creating the query. Don't use "*" for all fields, but
drop in the specific fields you need as you build that append query...
 
L

Libbytw

Albert,
Thank you for your quick response.

I did open the Linked Table in Design View and the fields were not there.
Am I not getting all the information I should be from the SharePoint site?
When I set up an Import Table in Access 07 it does bring in the names, so
where is it getting the information from?

I would use the Import Tables for the source, but I need to refresh the data
once a week so I prefer to use the Linked Tables.

I'm trying to re-create the Append Query...but having some trouble. I'm
able to select the Linked Table as the source but how do I select the Table I
want to append to? When the cursor is in the field under "Append To:" the
pulldown list only shows the Linked Table name. In 2003 I used to see the
list of fields from the destination table.

FYI: What I'm doing, by the way, is extracting data from 9 SharePoint Lists
using Access and then via Append Queries I'm putting all the data together
into one Table so I can run reports, comparisons, etc. I've been using
Access 2003 for a few years and it's worked fine, but with 2007 I'm missing
the names (Created By and Modified By). If you can think of a better way to
do this, I'm open to any suggestions. Thank you.
 
J

Jeff Boyce

Any chance you're getting the rowID instead of the name?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
L

Libbytw

Excellent troubleshooting suggestion, however, numbers include hundreds of
"1" in the Created By field. Since I uploaded these several hundred records
when importing the data from another source, and I'm #1 since I created the
SharePoint site, it's pretty clear that these are user numbers from the
SharePoint permissions list.

Why it's pulling numbers and not names from this SharePoint-maintanined-list
is the question that apparently has many of us stumpped.

I do appreciate you're offering your ideas into the mix.

Thank you,
 
L

Libbytw

PS Although I haven't gone into all the detail in earlier questions, this is
also happening to the names that are included in the fields in SharePoint
that are inserted into each record using the LookUp feature.

I guess I was hoping once I had an answer for the Created By and Modified By
fields I would be able to apply it to the other name fields where the list of
names are managed by a SharePoint-managed List.

Is it possible that the functionality was not included in Access 07's
initial release and it waiting for some future release to be added back?

Since it's 2009 already, this may be a crazy question, but thought I'd toss
it out there.
Thanks,
 
J

Jeff Boyce

If you were working solely within Access, I'd have you check which column is
bound to the field (because it sounds like the first one is), and which
column is the first visible (column width > 0) (and it still sounds like the
ID field is).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

PartHart

Has there been any resolution on this? I have encountered and fought with
same problem for weeks now - though my scenario includes a link to sharepoint
list with column/field data type people/groups AND allows multiple values
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top