Solved

SQL Server 2008 & Access 2010

Posted on 2013-06-24
9
311 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 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