Question Web Enable Primary Key Question

[Perissos]

Just when I though I was done.....I have been instructed to Web Enable the database I am using to a sharepoint server.

Looked easy enough. I ordered some server space and ran the compatability check. Had a few issues which are easy to resolve, but one I don't think I should have to and was wondering if someone knew why Access would make this an issue.

I have a table with Material codes which is unique for each material being used in a product. The material code is unique for each item and can not be duplicated so I set the material code as a primary key. Because the material code can contain letters as well as numbers I made it a text field.(Example: F60569, 703556, L32R69)

My problem is when I go to publish the database to the web it kicks it back saying that the primary key field should be a number with field size long to be compatible.

I know I can just add an auto number to the table, but why would it not accept it the way it was. Creating an autonumber key makes no sense when the code itself can be a Primary key.... at least to me it doesn't make sense.

 

[Banana]

It's simply because SharePoint itself is not a RDBMS and thus does not share the same concept of the primary key. Internally, SharePoint uses its own autoincrementing key which is hidden behind the scene. When we publish a table to SharePoint, Access has to map the original primary key to SharePoint's new keys and if it can be ensured that it's not dealing with composite or natural keys, the mapping is easier.

I hope that helps explain things a bit...

 

[Perissos]

Thanks.. this is gonna be one of those times when I say... "There has got to be a better way!"

 

[Banana]

Well, I suppose they could have made the re-mapping more transparent, but even so, when you publish, your original primary key will be no longer the "primary key" because behind the curtains they're now SharePoint lists and thus follow SharePoint's structure. You should make sure that your original primary key is marked as "unique" and "required" so you know it'll continue to function as key as in past.

 

[Perissos]

ok.. I got an error that says "An error occured while initializing the Access Services Database"

This will be a setting on the website and not the database?

 

[Banana]

Need some more context. Where are you getting this error and what you were doing? Did it pass the web compatibility? Is it your own SharePoint or is it a hosted account?

 

[Perissos]

It occurs when I go to publish. I get that it is compatible but it won't publish to the site.

Its a hosted site. 1and1

I have a feeling I will need to create a server partition on my laptop

I have their technical support on the phone and will be calling microsoft in a moment

 

[Banana]

Just to be 100% sure... the hosted site is running SharePoint 2010, and it has Access Services enabled?

 

[Perissos]

I had to go look

found this note posted for someone elses question.

Our SharePoint® Hosting has not been upgraded to the 2010 version as of yet

Not sure when that was posted but I imagine that is the problem.

I guess I will need to google sharepoint 2010 hosting

 

[Banana]

Yeah, that's the reason. It's too new. Right now, the only one I know about is AccessHosting.com which is specialized just for Access Services + SharePoint 2010. There may be other providers I don't know about though.

 

[Perissos]

Got it set up and it worked great until I got to the very end and it said I had to select a form to display when it goes to the website.

My option to select a web display form is greyed out. Would you know why?

 

[Banana]

Do you actually have any web forms? You can't use a client form as a web display form so if there were nothing but client forms, that could be why?

 

[Perissos]

oh geez...

I knew it sounded too easy.

Gotta be a bright side to this, lol

I may need lots of help!

 

[Banana]

Sorry. The thing is that a web application allows you to keep traditional client forms, reports, queries, VBA and linked tables and they can be functional inside an Access client. However to function in a *web browser*, you have to use everything web (web tables, web form, web queries and web macros). No VBA. In theory, you could have a web form as a default display form and use a IsClient function to detect if you are in a client and branch out to VBA or use web macros only.

I hope that helps a bit.

 

[Perissos]

yeah.. just wish there was an easy way to convert the forms from one to the other without having to redo all the code

that is A LOT of work.

some of the code I used for creating the reports is sql. Will that be ok to use?

 

[Perissos]

Would you be interested in a contract job since you know about this so well?

and yes.. I am being serious

 

[Banana]

yeah.. just wish there was an easy way to convert the forms from one to the other without having to redo all the code

that is A LOT of work.

I agree. I happen to know that AccessHosting.com offers a conversion services - I'm sure it won't hurt to ask them if they can do it for you even if you don't need a hosted account with them.

some of the code I used for creating the reports is sql. Will that be ok to use?

Maybe, maybe not. The basic is that with a SharePoint, we are not dealing with a SQL RDBMS anymore. Sure, it's powered by SQL Server but we essentially query SharePoint List with CAML (think XML + SQL) so there's changes in what is doable and what isn't with a SharePoint Query (and consequently web query). Again, you can continue to use client Access and use full functionality but if you need it in web browser, then you have to play by web browser rule, so to speak.

 

[Simon_MT]

How many users are involved? A Terminal Server could be worth investigating and needs little in the way of Access modifications.

Simon

 

[Banana]

Good question, Simon.

That also reminds me - while I've never used it I've heard pretty good praise for EQLData which can satisfy requirement of placing Access inside a web browser without any conversion.

 

[Perissos]

Well Simon... that is an extremely good question that I, unfortunately, don't know the answer to.