Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2008 & Access 2010

Posted on 2013-06-24
9
Medium Priority
?
344 Views
Last Modified: 2013-06-25
Eh Guys,

I have a situation or is more like a curiosity. I have a SQL server 2008 and I have an Access 2010 database which is a template from the list of templates Access has. It is called Students...... it is really cool! it let you enter all kind of information about a student. I linked or moved this database to the SQL Server by making a connection to it. So, now the data is in the SQL server and the end interface is the template in Access. So, I enter a new student and the data is stored in the SQL server!!

Well, my curiosity comes in here.... What if I have the exact same Access template in a second computer and link that template to the same SQL server where the first template is storing the data. Is it possible? I mean to have one SQL server and more than one computer with the same Access template and be able to access and retrieve the data from the SQL server through the template and have all templates in all computers up to date with the same data....... Would all the templates Sync? Do you have any idea how I can accomplish this? Do you think sharepoint is an option?



Thanks,
0
Comment
Question by:hugonieto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 12

Expert Comment

by:duttcom
ID: 39273378
That's the whole point of using an SQL database to store the data - you can connect many Access front ends to it and they all see the same data. Much like this website - there is only one database of everyone's questions and related answers, but multiple people can view that data and if I add to the database by posting this response, you can see it when you access this page next.
0
 

Author Comment

by:hugonieto
ID: 39273441
Thanks Duttcom.....

but how can I do it with Access 2010? Do you have any idea or advice?


Thanks
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39273459
On the PC that is connecting to the SQL server, check in the My Documents folder - is there a folder in there called My Data Sources, and if so, does it contain any files and what are they called?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:hugonieto
ID: 39273476
I don't have any folder called My Data Sources under My Documents folder....
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39273500
Hmmm. There are several options which can be chosen when using the Move Data to SQL Server tool, so I'm not exactly sure how your connection was made.

Have you tried simply opening a copy of the Access database on another PC? If so, what were the results?
0
 

Author Comment

by:hugonieto
ID: 39273510
Well, I connected it by using the Upsizing Wizard on Access 2010 which it use a Data Source driver (ODBC for Access 2010 ) to connect to a SQL server.
0
 
LVL 12

Accepted Solution

by:
duttcom earned 2000 total points
ID: 39273534
Ah, but there are different ways for Access to use that data source driver, depending on what options you chose when you ran the tool; you obviously chose the option that uses a trusted connection instead of a named data source (which would have been found in your My Data Sources folder).

If that is the case, then Access will be connecting to the SQL server using your current Windows logon (a trusted connection). If you copy the Access database to someone else's PC and they have exactly the same permissions to access the SQL database as you do, Access should work. If they don't have the required permissions then they won't be able to connect. This way you have to make sure that all the Windows users who conenct to the database have the correct permissions.

The alternative way (the way I use) would be to create a named data source (which uses a single SQL user login rather than a Windows login) that can be copied to other PCs. In this scenario, you can set up one user in SQL that has the permissions required and all of your Windows users would connect using a DSN file that contains the connection information.

If you are thinking that this is all a bit confusing, you'd be right!
0
 

Author Comment

by:hugonieto
ID: 39275288
Thanks Duttcom!!

I actually played around the way you do it.... meaning to create a Data Source file and take that file and the Access template to any other computer and it works!!! right now I have three computers with the same Access template and all three talk to the SQL server and all three can see each others data.... THIS IS GREAT!!! I can just copy the template and the Data Source files to an USB flash drive and paste them into a 4th computer and it will be able to communicate with all other computers and the SQL server!! WOOOWW! Pretty nice!!

Now that I accomplished what I wanted! I was wondering..... Can I use this template to somehow linked to SharePoint Foundation 2010? Meaning that this Sharepoint will be able to see the SQL server and sync with all 4 computers...... Let's say I would like to see or enter a new record using any browser..... I would be able to see the same forms and data that the Access template has and when I enter I new student information it would be stored in the SQL server and show this new student on all 4 computers and Sharepoint too...... SQL server, Access 2010, and SharePoint Foundation 2010 would sync back and forth.... Is this possible? Is there a way to do this?



Thanks a lot!
0
 
LVL 21
ID: 39276068
hugonieto.

Now that I accomplished what I wanted! I was wondering.....
It is my understanding that the per the EE rules that you are not allowed to ask additional questions. You really should to close this one and start a new question.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

636 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