Solved

Access 2010 Multiple Users in Multiple Countries

Posted on 2014-02-05
16
438 Views
Last Modified: 2014-02-07
Hi-

I want to build an access database using microsoft 2010. The primary use of the database is going to be a couple forms to capture data from user input. I have users in multiple countries. Will this pose a problem?

Also, I only expect 5-10 users in the database at the time. So not too many.

Any thoughts or ideas?

Thank you-

Kelly
0
Comment
Question by:tawathav
  • 6
  • 6
  • 2
  • +1
16 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 39836193
Hello Kelly,

In my opinion, yes it is possible and it would not surely pose any issues provided that you properly design the database and plan out its deployment.

Just a few clarifications:
What would be the backend of your database? Is it a sharepoint or SQL Server?
How many users (approximate will do) would be the "multiple users" you mentioned?
How are you going to deploy it?

Sincerely,
Ed
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39836236
Your biggest problem will be date formats.  Use the dd-mmm-yyyy format to avoid ambiguity when dates are displayed or entered.  

Be careful to NOT specifically format a date data type with the Format() function since that turns the date into a string and it will no longer behave like a date unless you want a string.  Although there are situations where you may have to format() a date for use in an SQL string.  In that case use the mm/dd/yyyy format since that is what SQL expects to avoid confusion.  yyyy/mm/dd would also work.  As long as you leave the date fields as date data types, you won't have trouble.  Make sure you define unbound controls that will hold dates with a date format.  So, in a querydef -
Select ... From ... Where mydate = Forms!yourform!yourdate;
will work fine.  However, if you build an SQL string, that requires you to concatenate a date you will have to format the date.

strSQL = "Select .. From .. Where mydate = #" & format(me.yourdate, "mm/dd/yyyy") & "#;"

This can get really confusing but as long as you remember that a formatted date is a string and a string is not a date, you should be OK.
0
 

Author Comment

by:tawathav
ID: 39839967
So my company has Sharepoint 2010, however has not enables Access Services. So I was just going to store the database on the sharepoint site so everyone could access it. They would open it to fill out the forms and sync back to sharepoint.

I would be looking at 50 total users but only 5-7 in the database at a time max.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 36

Expert Comment

by:PatHartman
ID: 39840025
Using that method, you won't be able to have concurrent users.  The only way you can have concurrent users is if you are using a shared back end.  SharePoint may have a check in/check out procedure that you can use to have everyone update an unsplit Access app but only a single person can have the database out at once.

I don't think you need to have access services enabled for Access to use SharePoint lists.  You really don't want to create a web database using A2010 anyway.  The technology has already been deprecated.

You could use SharePoint lists to hold your data if you have no other way of sharing.  Just remember that SharePoint is not a relational database.  You would then distribute the FE to each individual.  You might be able to use SharePoint for this but the point is, the data would be SharePoint lists linked to the Access 2010 FE.  The data would not reside in the Access database itself.
0
 

Author Comment

by:tawathav
ID: 39840050
Basically I need to create custom forms. I can't use Sharepoint designer or infopath. I need to roll out to multiple countries. Perhaps I just put it on a share drive with a front end and back end. Skip the whole Sharepoint thing for now.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39840164
You can use Access to create the FE.  It is just the data that is in SharePoint.  The SharePoint lists are like any other ODBC data source.

Would they allow you to create new lists in SharePoint?

If you have a share drive that everyone uses, that would work fine and you don't have to involve SharePoint at all.  Just make sure the app is split and each user has his own personal copy of the FE.
0
 

Author Comment

by:tawathav
ID: 39840173
I can but they are awful looking....I need to enter data which would be better suited in a table format for some of the information. Which is why I was looking to do an access database so I could customize the forms.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39840280
I'm not making myself clear.

The users won't be entering data into the SharePoint lists.  The users will be using your Access application.  The SharePoint list is a linked table to Access.  They will never see them.  You just need the authority to create them so you can have an Access application link to them.  This is just like having the Access app link to SQL Server or Outlook.  It's all tables under the hood and all the user sees is the forms and reports you create for him.

If you have a shared server, you don't need the SharePoint lists.  The point is to have only one data source for your Access FE that all users connect to.  For many companies, remote locations do have a LAN that all can connect to.  So the office in Buffalo doesn't have any drives it can share with the office in Detroit so SharePoint offers a way to share data but still get the benefit of an Access application.  Is that better?
0
 

Author Comment

by:tawathav
ID: 39840341
Yes. How do i connect access to sharepoint lists so they only see the form from access?
0
 
LVL 75
ID: 39840398
@  tawathav:

Is there *any* chance Acesss 2010 could be replaced with Access 2013 or Office 365?
This would open a lot more possibilities for your specific case ...

m
0
 

Author Comment

by:tawathav
ID: 39840403
no I do not have the ability to replace. My company will be upgrading in about a year.
0
 
LVL 75
ID: 39840408
:-(
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39840541
The Get External Data dialog offers SharePoint as an option.  Drop down the More list.
0
 

Author Comment

by:tawathav
ID: 39840679
I have the database created and SharePoint linked. How do I display the actual access form in Sharepoint?

I saw you can create an Access view from the Sharepoint list, however, when I do access opens and I save to the sharepoint site, but nothing happens and no view appears. Does this work?
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 39841062
You don't.  As I have said, the ONLY thing in SharePoint is the list.  EVERYTHING else is in the Access application that you create.  It contains ALL the forms, reports, etc.  You must distribute the Access application to EVERY user.
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 250 total points
ID: 39841688
Hello  tawathav,

Thanks for your response with my questions posted above.

Since the Access Services is not enabled in your sharepoint, you could not publish it as an access web application. With this, you will be opted to use sharepoint list as the Back End (BE) of your access database. It means that you need to distribute your Front End (FE) to your users.

You need to look into consideration that once the user opens the application (FE), it asks for a sharepoint authentication. It implies that a user must have an account in your sharepoint in order for them to use it.

I may suggest to look into a similar thread on the link below for more info about using Sharepoint as BE of any access application.

http://stackoverflow.com/questions/10076162/publishing-a-split-access-2010-database-to-sharepoint

Sincerely,

Ed
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question