?
Solved

SQL Server 2008 & Access 2010

Posted on 2013-06-24
9
Medium Priority
?
348 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

807 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