Create a SQL view via MS Access 2000 1

[LSQUARE]

There are 2 objectives:

1. I would like to set up a SQL view but avoid going through SQL to create the query. I am not too familiar with the language.

2. Manipulate the datatable for the SQL view

The theory is to import the raw data table from SQL, manipulate the table in Access (eg. insert /delete columns) and then export it back to SQL as a virtual table.

Questions: Can this be done? If so, how do I do this?

Any advice would be much appreciated.

 

[JayKusch]

Your still going to have to do some SQL statements in SQL Server in order to create the schema for the view itself.

Can you define further what you are trying to accomplish.

Thanks

J. Kusch

 

[DougP]

If you link the table in Access then you can use it just like any other Access table. First you have to make a ODBC connection to the SQL Server. Right click on the “Tables” tab in Access then click “Link Tables”
At the bottom is “Files of Type” find ODBC it is the last one. Next click the “machine Data Source” Tab at the top. Then click the NEW button (lower right)
Depending on which version of Access and ODBC you have the box looks different here.
Bu the next box has a list of all ODBC data sources your computer will support.
SQL is near the bottom.
Once you click this you will be done. with this part next a dialog will pop up and want a name anything easy and short is good.
The third box will show a list of SQL servers on your network. This is the Computers network name where SQL server is installed and running. Next is permission and so on
Now back in Access you will see a list of table on the SQLK server you can link to anyone of these or as many as you like. Make sure if you type in a password you click save password check box in the lower right or you will be prompted to enter the password everytime.

SQL also server has a SQL statement builder which most people don't know about. In Enterprise Manager Open the table and view all rows
then click the buttons at the top of this view
It will turn on a SQL builder which will show the SQL code "Select From YourTable"
Then you can add columns similar to Access and it will also generate the SQL code at the same time.

Hope this helps

DougP, MCP

 

[LSQUARE]

Thanks DougP. Very helpful!