Offline data entry for split database?

[rc-alex]

Hello,

I have a large database mostly built and close to ready to deploy. The database will be split with the backend on a server. I was just told that they want to enter data offline and sync later.

I am not sure how this would be achieved, since the tables are located on the server.

Any way to do this? what happens if two people enter the same set of fields that form a primary key, causing a duplicate? I asumme one would have to overwrite the other. Assuming there is even a way to work offline.

 

[boblarson]

Which version of Access are you using and what file type (if 2007 or 2010)?

 

[rc-alex]

Bob, I am in Access 2010. Some users might be in 2007, although they could be upgraded if needed.

The source database file that will be split is: .accdb.

Thanks!

 

[boblarson]

Sorry, but there is no way to do an offline entry using 2010 and accdb file. This would require some major development in order to handle it. I wouldn't want to be in your shoes. The only Offline option supported by Microsoft in Access 2010 is to use Access Services in SharePoint 2010.

 

[rc-alex]

Bob,

Thanks for verifying that. By Access services, you mean web database correct? That option is unfortunately out because of the inabililty to perform calculations in queries.

Also looked at "Move the database to SharePoint" and convert the tables to lists, but SharePoint converted all the lists to only having one key field, in the case of a couple of tables which require complex keys to ensure no duplication. There isn't any way around this, is there?

Thanks!

 

[boblarson]

Bob,

Thanks for verifying that. By Access services, you mean web database correct?

Yes, that is correct except that it isn't just any web database. It has to be connected up to SharePoint 2010 with Access Web Services running (not just simple SharePoint).

That option is unfortunately out because of the inabililty to perform calculations in queries.

What do you mean? You can do calculations in queries in a web query. You can't use functions in them but you can set up your tables to use data macros to keep aggregate data available for you. That is how you have to do it with those.

Also looked at "Move the database to SharePoint" and convert the tables to lists, but SharePoint converted all the lists to only having one key field, in the case of a couple of tables which require complex keys to ensure no duplication. There isn't any way around this, is there?

You are mistaking that you have to have a composite key to keep from duplicating data with ways to avoid that. You do not have to have a composite key. You can use multi-field indexes to keep that from happening, or you can use the Before Update event in forms to validate and keep duplicates from occurring.

 

[rc-alex]

Yes, that is correct except that it isn't just any web database. It has to be connected up to SharePoint 2010 with Access Web Services running (not just simple SharePoint).

We already have a web database running on our SP Server 2010, so we do have this capability.

What do you mean? You can do calculations in queries in a web query. You can't use functions in them but you can set up your tables to use data macros to keep aggregate data available for you. That is how you have to do it with those.

I have it set up with a lot of calculations already, so I'm assuming changing to this format would be a major undertaking? How is each calculation changed over? Although, I just remembered using a web database was ruled out completely because they cannot display charts (from all information I could find).

You are mistaking that you have to have a composite key to keep from duplicating data with ways to avoid that. You do not have to have a composite key. You can use multi-field indexes to keep that from happening, or you can use the Before Update event in forms to validate and keep duplicates from occurring.

This looks very interesting to me. I am reading up on multi-field indexes now. If I don't have to restructure the database, but just apply the indexes, this might work wonders.

If I can "Move to SharePoint" and convert the tables to lists, offline data entry will work, correct? The downside is the data is accessible via the lists which leaves the possibility of corruption open, but better than no offline access at all, I suppose.

 

[boblarson]

Here's a tutorial of mine on the Multi-field index:
http://www.btabdevelopment.com/ts/mfi
And a Web Database CAN have charts and such. You just don't use the web forms. I think you aren't aware that you can have all of the client tools - forms, reports, charts, VBA, functions in queries and such if you use the client forms and such. The user can still use the database if they have Access installed or the runtime. It is called a Hybrid application and they wouldn't access it from the web browser. They would use the Access client.

 

[rc-alex]

Bob,

I really appreciate the information!

The tutorial link didn't show up.

To assign a new, single autonumber field as the primary key, I have to remove the relationships attached to the three fields combined as a key currently. Doing so, and then reassigning the relationships when they are not key fields shouldn't cause any problem, right?

After I get the multiple-field keys removed, I'll "Move to SharePoint" and convert to tables.

Thanks!

 

[boblarson]

The tutorial link didn't show up.

I just edited the post. I forgot to paste it in before I posted.

To assign a new, single autonumber field as the primary key, I have to remove the relationships attached to the three fields combined as a key currently. Doing so, and then reassigning the relationships when they are not key fields shouldn't cause any problem, right?

You should be able to remove the relationships without affecting things but you will want to add the ID field from the one table to the other table(s) as a foreign key and then run an update query to populate it. Then delete the other fields from that table(s) where they were the foreign key as you don't want all of that duplicated data.

After I get the multiple-field keys removed, I'll "Move to SharePoint" and convert to tables.

Remember SharePoint creates an ID field named ID so don't use that as the name for anything in yours.

Also, make sure to keep a copy just in case something goes wrong you can revert.

 

[rc-alex]

You should be able to remove the relationships without affecting things but you will want to add the ID field from the one table to the other table(s) as a foreign key and then run an update query to populate it. Then delete the other fields from that table(s) where they were the foreign key as you don't want all of that duplicated data.

Right now the three-field primary key is comprised of the three single primary keys, one from each of the three tables.

Are you saying I will have to use the new auto-number in each of those three tables?

I want to maintain the current relationships. Can't the Primary key in each of the three tables still point to the same spot in the main table?
They should still be able to find the unique instance in the main table simply by selecting the three primary keys from the other three tables on a form.

Very nice, to the point, and easy to follow tutorial about the index!!!

Thanks

 

[boblarson]

You shouldn't have to remove the Keys then, just add the autonumber and make it PK and set the MFI. But any tables where this table's PK would be a FK (if there is one) would need to have it done like I said.

 

[rc-alex]

You shouldn't have to remove the Keys then, just add the autonumber and make it PK and set the MFI. But any tables where this table's PK would be a FK (if there is one) would need to have it done like I said.

When I set the indexes, I have the three fields that make a unique entity. I also have the Primary Key (autonumber) because it apparently is required.

However, when I set the three original fields to "unique" in the index menu, it looks for any duplicate values in the column... not unique combinations of the three. Am I doing something wrong? Seems like it should work.

Thanks.

 

[boblarson]

You only set the UNIQUE on the first of the three if you followed the screenshots that I had in the tutorial. You don't put a name for it more than once and...

 

[rc-alex]

Bob,

Sorry for my confusion! I didn't see the block of red text to the side of the image, but that clears it up! Worked great!

One more question. I have a laptop with Access 2007 and it gives you the choice of a location to upload the database onto the SharePoint site so that users can access it. Access 2010 does not. I believe when I tried this a couple of weeks ago (with a test database) if I uploaded the database separately it was its own file? How can I upload the forms and queries with Access 2010?

THANK YOU!!

 

[boblarson]

How can I upload the forms and queries with Access 2010?

You don't. You give the users a copy of the frontend or you can "Publish" the database to SharePoint (it is in the FILE menu somewhere - I don't have a copy available at the moment to give explicit instructions). What it is called is "round tripping" as it sends all objects in the database up but the only things that can be used from the web via a browser are the web forms, etc. you have created. The rest would be used on their local machine. So then your users should be able to download the frontend from Sharepoint and then use it on their local machines. Or you can just send them a copy and then they have to have permissions on SharePoint which they will have to login when they start up the database.

 

[rc-alex]

You don't. You give the users a copy of the frontend or you can "Publish" the database to SharePoint (it is in the FILE menu somewhere - I don't have a copy available at the moment to give explicit instructions). What it is called is "round tripping" as it sends all objects in the database up but the only things that can be used from the web via a browser are the web forms, etc. you have created. The rest would be used on their local machine. So then your users should be able to download the frontend from Sharepoint and then use it on their local machines. Or you can just send them a copy and then they have to have permissions on SharePoint which they will have to login when they start up the database.

I think I need to do more reading about this. Are you saying split the database? (to give the users a copy of the front end)?

I did the "move to sharePoint" which converted the tables to lists.

Then when I upload the database to SharePoint, and to open it I had to save a local copy. As long as the local copy was open and connected to the internet, all was fine. When I turned off the network adapter, added records, closed the database file, turned on the network adapter, and then opened the database, it wanted me to save the local copy over the remote copy on SharePoint.

If I split the database, I don't think it would work over the SharePoint site which is https.

Thanks.

 

[cristine]

Hi ,

Since we are at Sharepoint. How do I use this? I feel this would help especially when my company runs a business in another country and I would like my admin there to be able to use what I have completed.

 

[rc-alex]

Doing some reading, "they say" that using the database as SharePoint lists gets really slow once about 2,000 records exist. That would probably be an issue. A single report might reference 1,000 records from one of the tables.

Or is the "round tripping" completely different? Maybe I misunderstood your post.

Thanks!

 

[boblarson]

Doing some reading, "they say" that using the database as SharePoint lists gets really slow once about 2,000 records exist.

You have to make sure you know which SharePoint I am talking about there. I'm pretty sure that I would have been talking about a version of SharePoint PRIOR to 2010. And it might have been with a different version of Access (2007???).

Or is the "round tripping" completely different? Maybe I misunderstood your post.

All Round Tripping refers to is that non web objects are stored with the Access file when you publish it up to SharePoint 2010 with Access Database Services running. It allows you to put a copy of the database where everyone can download a copy (hence for their frontend) and use it on a computer like Access has always been used where the normal non-web forms, reports, VBA, and such can be used. So, if you keep a copy of the database up there and they can click on the link for the program it will basically download to their computers temp directory and open for them. They can use it like normal and then when they next use it, it will again download the latest version and open. This can be helpful for maintaining the most current copy of the frontend instead of the older versions where you had to distribute new copies of the frontend somehow (for example using my frontend auto update utility).

Oh, and by the way, for your earlier question - if you have the tables published to SharePoint, your database IS Split. The data resides on SharePoint and the business logic and user interface is in the Access file.