Solved

Access 2010 Multiple Users in Multiple Countries

Posted on 2014-02-05
16
430 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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
@  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
Comment Utility
no I do not have the ability to replace. My company will be upgrading in about a year.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
:-(
0
 
LVL 34

Expert Comment

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

Author Comment

by:tawathav
Comment Utility
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 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now