Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Codesearcher
Starting Member
8 Posts |
 Posted - 2009-10-10 : 03:02:59
|
| hiI am using sql server database(back end) which contains all the export details of my company.I want this database to be connected to MS Access (Front End). In the access.Any changes made to the database should reflect in the Access sheet.Also i have to filter the contents of Access sheet with following condition.1.Whenever any goods move out of the company the status of that good becomes 'Sold' and goods which is not sold has the status 'backlog'so based on the status i should filter out only the backlogged goods alone in the access sheet.Im basically a non IT person, so it would be more helpful if u explain be in detailed way.Any help in this regard is highly appreciated. |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2009-10-10 : 10:15:35
|
| The easiest way is to set up an ODBC data source, then to link the tables in Access. If security isn't an issue, and you don't need to directly call Stored Procedures from the front end, this is the way to go. Got to Control Panel(Data Sources is under Administrative tools on Server versions of Windows, not control panel), and click Data Sources (ODBC). Click on the System DSN Tab. Scroll to the bottom and select SQL Server. Click Finish. Give the connection a name. Select the server. Click next. Use the Windows Authentication, if that is how your SQL server is set up, if not, put in the SQL server login (probably a bad idea for Access). Click Next.Check the 'change the default database' and select your database. Click Next. Click Finish. This will create an ODBC datasource that any version of Access can use. Older versions of Access won't work directly with SQL server 2005 or 2008, so while you lose some functionality, it will work. In Access, under tables, righ-click on any white space in the tables tab, and select Linked Tables. When the file dialogue comes up, select the pulldown for files of type. Scroll down down down and select ODBC. A box will come up. click on Machine data source tab. Your sql server db should be there. Select it. A new box will appear. Select each table you'll need (views will show up as tables, too). Click OK.You're done. All your tables should now be in the Access Database. Oh, by the way, use an MDB (database), not an ADP (Access Project).Finally, post further questions relating to Access in the Access forum, not the SQL forum.-----------------Stephen |
 |
|
|
|
|
|
|
|
Connecting SQL server with Access