web database table links (1 Viewer)

I created a web database for use on SharePoint. And I was able to publish it to SharePoint today. But to make it web compatible I had to remove my row source Data I used to make my combo boxes fill the way I needed them to. I created these on the form Property sheet, not using a lookup in the table development.

These are the fields and tables they were linked to:
cmbFailureTypeCode from tblFailureCode
cmbContainmentCode from tblInterimContainment
cmbRootCauseCode from tblRootCause
cmbCorrectiveCode from tblCorrectiveCode
The records in each of these tables make up the options I need for the combo box fields.

This code is one example of how I linked the fields to the tables:

Since its a web database is it possible for me to link these tables, so that my combo boxes pull there list info from there respected tables?

My attached database is the one published to SharePoint.
Any help would be much appreciated. Thank you

Hi,

No one will be able to open and use your attached published web database because they don't have permissions to your SharePoint site where you published it. If you want people to look at your database, you'll have to create a backup unpublished copy and attach that instead.

Perhaps some screenshots of what you're trying to do in your web database would help instead.

In general, the process for creating a data source for a combo box on a web form is similar to a client form. Just click the Builder button on the Row Source property line and build an embedded query to display the results you want.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

Ok I attached the backup verison of my database on my first post.

Also the SharePoint version is in Read-Only mode, how do I change that?

Hi,

Thanks for the unpublished copy; that's helpful.

I took a quick look at your web database. Before we worry about the combo box controls on the web form, it seems to me like you need the base fields listed in your previous post to be Lookup fields.

Is there some reason why you have the following fields set to be text fields in the tblIncident table instead of being a Lookup field?
- FailureTypeCode
- ContainmentCode
- RootCauseCode
- CorrectiveCode

It would seem to me like you want to create a relationship for each of those four fields to their respective related tables:
- tblFailureCode
- tblInterimContainment
- tblRootCause
- tblCorrectiveCode

By defining a Lookup at the table level you'll be able to have the application enforce referential integrity between the related tables so you won't have incorrect and/or orphaned records. In web databases, the only way to define relationships between tables is through Lookup fields.

If you changed the above fields to be Lookup fields, Access would provide combo box controls predefined for you on your web forms that would show the correct related data in the drop-down lists. Access would take care of most of this work for you automatically.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

When I first tried to upload it to SharePoint the web compatiablity checker said it was incompatiable with the lookups at table level. And what I have read from other posts creating lookups at table level are not recommended for web databases.
But I believe I found the work around for the lookups. By establishing the Row Source using the ... button which opened the Query Builder. Which seems to be working just fine.

But the issue i'm not sure of now is how to get the web database out of Read-only mode?

Thank you for all your help.

Hi,

I don't know why you were getting compatibility errors after creating your lookup fields for your web tables in this database. I deleted your four existing fields identified above, and re-created four lookup fields with the same field names. They were perfectly fine and pass the compatibility checker without any issues. And, best of all, you now have referential integrity established between the main table and the four related tables.

Please see the attached revised web database which works just fine. I added the fields onto the form grid in the appropriate places and the combo boxes display the related data just fine. Take a look.

I'm not sure where you read creating table level lookups for web databases is not recommended. You'll have to trust me when I say that information is completely false. I said it in the previous post and I'll say it again, the *only* way to define relationships between web tables in a web database is through Lookup fields. So if you care about relationships between your web tables in a 2010 style web database (as you should), you *must* create lookup fields at the table level.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

I read about the lookups in a couple other threads on this forum. I don't remember which ones, because I have read so many different ones looking for some answers to my issues. But Thank you very much Jeff, I appreciate all your help and advice.

Hi,

Glad I could help.

The subject of lookup fields in tables is a "hot button" with many developers. Most have very passionate <cough> opinions about the subject.

That being said, lookup fields are essential in 2010 style web databases and 2013 style web apps. I suspect the discussions you read concerned lookup fields in desktop databases. Defining and maintaining relationships between related tables is critical which means you must use lookup fields in web databases to have referential integrity.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------