Solved

Access 2010 Multiple Users in Multiple Countries

Posted on 2014-02-05
16
435 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 35

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 35

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 35

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 35

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 35

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 35

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

860 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