Access BE to Sharepoint List (1 Viewer)

StuartG

Registered User.
Local time
Today, 02:03
Joined
Sep 12, 2018
Messages
125
Hi all,

I have been testing Access BE tables into a Sharepoint List and reached a stumbling block.
(Hopefully this is still supported by MS)

I've managed to get the data uploaded into SharePoint using the export function in access but now I have lost the ID number in a particular form for each new record I create.

In the below picture, i have entered text but it has not provided me an ID in this view, which i use to create a unique folder ID and know the next sequential number.

1594024845577.png


but then where I review all my Tenders, it shows the number?

1594024938765.png


Could anyone explain why it could be doing this, the ID number showed prior to me linking to SharePoint?

The records are writing to my SharePoint List as well, so the link seems good there.

Thanks as always. :)

Stuart
 

NauticalGent

Ignore List Poster Boy
Local time
Yesterday, 22:03
Joined
Apr 27, 2015
Messages
6,286
Good morning Stuart,

When you export your tables to a SP list, SP will establish it's own primary key field and name it ID. If you look at the list, you will see a new field called, if memory serves, Old_ID, which is your old PK field.

So you can either redo your forms to include new ID or, if the old PK field actually was important, you can use it to redo your relationships. A little tricky to hack, but it can be done.

Also, MS has moved away from SP as a BE for Access, especially the web publishing feature. You may want to think about an alternative solution if you are able to...
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
Hi Stuart. When you use SP as a BE, I believe it behaves like SQL Server does when it cones to generating the ID number. In Access, you get the new ID as soon as you dirty the form. But in SQL Server, you don't get the new ID until you save the new record.
 

StuartG

Registered User.
Local time
Today, 02:03
Joined
Sep 12, 2018
Messages
125
Hi Stuart. When you use SP as a BE, I believe it behaves like SQL Server does when it cones to generating the ID number. In Access, you get the new ID as soon as you dirty the form. But in SQL Server, you don't get the new ID until you save the new record.

Thanks for the advice.

Umm this might be an issue in my current setup then as we use the PK as an identifier for the record, i.e updates, comments etc.
If it does not show us chronologically the next number it could start to throw out our records.

The way i setup it up at the moment would be.

PK Record = 464 - Provided by Access
Our Internal Ref - TEN2020-464 - Manually typed by User.

Any suggestions on what I could do leaving the tables in SP lists, bearing in mind that we use the PK to update records etc.

The reason we are looking at SP is because we are starting to use MS Teams and want to be able to report using PowerBi - my employer is also looking at methods that aren't going to costs thousands and moving us to an SQL server will. (So they say)
 
Last edited:

tucker61

Registered User.
Local time
Yesterday, 19:03
Joined
Jan 13, 2008
Messages
321
Can i ask - how are you finding sharepoint as a back end ? i am currently trying to migrate my databases to sharepoint - 1 went with no problem as it only had 1000 records, the 2nd keeps timing out due to system resource issue - Stays at 35% for hours then quits.

We want to migrate this to sharepoint due to everyone currently working from home and they cannot access the old database on the network.

Fingers crossed i get this uploaded soon.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
Can i ask - how are you finding sharepoint as a back end ? i am currently trying to migrate my databases to sharepoint - 1 went with no problem as it only had 1000 records, the 2nd keeps timing out due to system resource issue - Stays at 35% for hours then quits.

We want to migrate this to sharepoint due to everyone currently working from home and they cannot access the old database on the network.

Fingers crossed i get this uploaded soon.
Hi. Just one person's humble opinion, but I think SharePoint is not suitable for critical or massive database applications. It's great for small ones, though.
 

NauticalGent

Ignore List Poster Boy
Local time
Yesterday, 22:03
Joined
Apr 27, 2015
Messages
6,286
To add what DBG has said, SP's performance really starts to degrade after the list has 20k records or more. One way around this is to make Views and then link the Views as tables. It gets a little tricky working with the GUIDs and all, but the improved performance is worth it.

Having said all that, you would be better on migrating to MySQL or even SQL Server.
 

StuartG

Registered User.
Local time
Today, 02:03
Joined
Sep 12, 2018
Messages
125
@theDBguy - Thanks for the advice, its greatly appreciated. Currently In one of our DB's we only have 500 Records and the BE tables are on a network drive and are linked to the Users FE, however this has becoming a nuisance with users having to connect through VPNs.

@NauticalGent - Again thanks for the tip - The company charges by department to put Data into an SQL server, but it seems that there will always be an Access program so perhaps SQL is the way forward the company will just need to pay it...
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
@theDBguy - Thanks for the advice, its greatly appreciated. Currently In one of our DB's we only have 500 Records and the BE tables are on a network drive and are linked to the Users FE, however this has becoming a nuisance with users having to connect through VPNs.
Sounds like you'll be fine then. Good luck!
 

StuartG

Registered User.
Local time
Today, 02:03
Joined
Sep 12, 2018
Messages
125
Sounds like you'll be fine then. Good luck!

Thanks,
Concerning the PK reference - i am assuming there is no way i can get the form to show ID number when I dirty the form?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
Thanks,
Concerning the PK reference - i am assuming there is no way i can get the form to show ID number when I dirty the form?
Not until you save the new record.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
Bummer!
Im clutching at straws, but what if I create another table just for autonumber or would that not work?
Why wouldn't saving the record first work?
 

StuartG

Registered User.
Local time
Today, 02:03
Joined
Sep 12, 2018
Messages
125
Why wouldn't saving the record first work?

I would be unable to see the next sequential number that the DB provides which is used as our identifier.
I.e. When I dirty the form it shows me the ID i then use this to label our files so it would be like TEN2020-476 etc. As we have multiple Status's for our Tenders the numbers on view may not necessarily be next.

Hope that makes sense?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
I would be unable to see the next sequential number that the DB provides which is used as our identifier.
I.e. When I dirty the form it shows me the ID i then use this to label our files so it would be like TEN2020-476 etc. As we have multiple Status's for our Tenders the numbers on view may not necessarily be next.

Hope that makes sense?
Sorry, no, it doesn't make sense. If you have a form, dirty it, then save it, but stay on the same record, why won't you be able to see the new ID?
 

StuartG

Registered User.
Local time
Today, 02:03
Joined
Sep 12, 2018
Messages
125
Sorry, no, it doesn't make sense. If you have a form, dirty it, then save it, but stay on the same record, why won't you be able to see the new ID?

Sorry, i thought it might be the case. I have been speaking with my colleague and we can get away with not having the ID number visible when creating a new record, plus we have another form that we use to update records and that form opens on the last record created, courtesy of Colin from AWF.

Have you ever used Access to SharePoint List?
I ask as when i import from Access to SharePoint my numbers don't match as SharePoint has it's own identifiers and disregards the Access PK number and lists it as Old_ID and then provides its own numbers.

I know NauticalGent mentioned it above and said about relationships but i would not have a clue where to start.

Wondering how i get round that.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
Sorry, i thought it might be the case. I have been speaking with my colleague and we can get away with not having the ID number visible when creating a new record, plus we have another form that we use to update records and that form opens on the last record created, courtesy of Colin from AWF.

Have you ever used Access to SharePoint List?
I ask as when i import from Access to SharePoint my numbers don't match as SharePoint has it's own identifiers and disregards the Access PK number and lists it as Old_ID and then provides its own numbers.

I know NauticalGent mentioned it above and said about relationships but i would not have a clue where to start.

Wondering how i get round that.
You can use an UPDATE query to resynchronize your old IDs with the new ones SharePoint provided.
 

StuartG

Registered User.
Local time
Today, 02:03
Joined
Sep 12, 2018
Messages
125
You can use an UPDATE query to resynchronize your old IDs with the new ones SharePoint provided.

Great - What is the best way of using the update Query.
Below is a snippet of my numbering

ID_OldIDInternal Ref
1​
9​
TEN2018-001
2​
10​
TEN2018-002
3​
11​
TEN2018-003
4​
12​
TEN2018-004
5​
13​
TEN2018-005
6​
14​
TEN2018-006
7​
15​
TEN2018-007
8​
16​
TEN2018-008
9​
17​
TEN2018-009

ID = What SharePoint Provided
OldID = Access PK ID

Don't worry too much about the "Internal Ref"

Do i create a separate table in Access for oldID,
I set it up like this but thinking i might have done it wrong?

1594220808875.png


Not used an update query before.

P.S. I have to use the oldID there are memo comments in another table.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
Great - What is the best way of using the update Query.
Below is a snippet of my numbering

ID_OldIDInternal Ref
1​
9​
TEN2018-001
2​
10​
TEN2018-002
3​
11​
TEN2018-003
4​
12​
TEN2018-004
5​
13​
TEN2018-005
6​
14​
TEN2018-006
7​
15​
TEN2018-007
8​
16​
TEN2018-008
9​
17​
TEN2018-009

ID = What SharePoint Provided
OldID = Access PK ID

Don't worry too much about the "Internal Ref"

Do i create a separate table in Access for oldID,
I set it up like this but thinking i might have done it wrong?

View attachment 83344

Not used an update query before.

P.S. I have to use the oldID there are memo comments in another table.
Hi. The basic idea is this. Let's say you have a parent table and child table as follows.

tblParent
Code:
ID    ParentValue
11    ValueA
12    ValueB
tblChild
Code:
ID    ParentID   ChildValue
21    11         Child1
22    11         Child2
23    12         Child3
So then, after you have moved the tables into SharePoint, you ended up with something like this.
tblParent
Code:
ID   ParentValue   Old_ID
1    ValueA        11
2    ValueB        12
tblChild
Code:
ID   ParentID   ChildValue   Old_ID
1    11         Child1       21
2    11         Child2       22
3    12         Child3       23
Now, all you really need to do is use an UPDATE query to change the child table into this.
tblChild
Code:
ID   ParentID   ChildValue   Old_ID
1    1          Child1       21
2    1          Child2       22
3    2          Child3       23
Hope that makes sense...
 
Last edited:

Users who are viewing this thread

Top Bottom