Tips for using Visual Studio to Access your database:

HistoryUpdated Sep 22, 2008 2:05 PM by Daniel Beatty…

Previous Lesson: Establishing connection to your SQLSERVER database:

Visual studio provides a GUI similar to MS Access for accessing SQL server database. To create a table, right click on subfolder table and choose add new table. You can then create a table in the same way as you did for Access.

To create a view, go to Server Explorer, right click on subfolder view, and choose new view. This will open a QBE window similar to MS Access where you can define a view and save it.
To create a query, go to Server Explorer, right click on subfolder view, and choose new query. However SQL Server does not ask if you want to store the query when you close the query window. The query will go away if you close the query window.

In Server Explorer, right-click the table that will be on the foreign-key side of the relationship and click Open Table Definition.
The table opens in Table Designer. Note from this point the procedure is identical to using MS SQL Server Management Studio Express.
From the Table Designer menu, click Relationships.
In the Foreign-key Relationships dialog box, click Add.
The relationship appears in the Selected Relationship list with a system-provided name in the format FK_<tablename>_<tablename>, where tablename is the name of the foreign key table.
Click the relationship in the Selected Relationship list.
Click Tables and Columns Specification in the grid to the right and click the ellipses (…) to the right of the property.
In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table that will be on the primary-key side of the relationship.
In the grid beneath, choose the columns contributing to the table's primary key. In the adjacent grid cell to the left of each column, choose the corresponding foreign-key column of the foreign-key table.
Table Designer suggests a name for the relationship. To change this name, edit the contents of the Relationship Name text box.
Choose OK to create the relationship.

If your are using JDBC, you can stop here and proceed to the Web Objects - EO Modeler segments.
If you are generating scripts running via VS 2008 and wish to generate scripts that will produce the same database, then follow these steps.

You must have a database project created in a solution to generate a Create script. To create a new database project, from the File menu, click New Project and in the New Project dialog box, under Project Types open the Other Project Types node. Click Database, and in the Templates window click the Database template icon. Name the solution and then click OK.
In Server Explorer, right-click the database object (e.g. table) for which you want to generate create scripts.
go to View -> Solution Explorer, you can double click on database objects to view generated scripts.

Open Access, File->New ->Project using existing data, the following dialog will show. Enter the same database connection information.
Click OK, you will see all the database objects you’ve created in your SQL Server database.
Using Access as front end, you can: