Solved

SQL Server 2008 & Access 2010

Posted on 2013-06-24
9
292 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:hugonieto
Comment Utility
I don't have any folder called My Data Sources under My Documents folder....
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 12

Expert Comment

by:duttcom
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
InfoPath Business days 1 14
Backup of Sharepoint Online 3 27
MS Access Bound Objects. 6 28
Multiple queries for a form 10 12
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now