Solved

SQL Server 2008 & Access 2010

Posted on 2013-06-24
9
326 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
Office 365 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

 

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 500 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 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