sfrisk
asked on
How do I create a new table to export into SQL from Access using a SQL view?
I am importing data into Access from a SQL view that I created by linking to the view using ODBC. I want to create a new table based on the SQL view and add a couple columns for comments and then export the new table into the SQL database. How do I create the new table with additional comments that links back to the primary key – is this done in Access or do I create the new table in SQL first? After the new table is exported back to SQL, how do I refresh the table so that I don’t lose the comments previously entered? SQL Server 2005 and Access 2007.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't completely understand your scenario. I understand creating the comment table based on the old table. But where do the comments come from?
I do not understand why Access is involved in this process at all.
I do not understand why Access is involved in this process at all.
ASKER
I'm using access as the entry point to enter comments. I'm pulling sales orders and need to have a separate table to add comments to each sales order that includes the sales dta. I can't modify the sales order table. A user would enter the comments into the table through Access. When a new order is entered, it's not dropping into my new table.
I didn't realize this was to be an ongoing process. For that, I'd steer you toward the other method. The setup is a little more difficult (not too bad, though), but it gives you direct access to the SQL tables from MS Access.
From Access, go to external data menu and create ODBC links to the Sql Server tables of interest. Then they will appear as tables to Access and your Acccess application can lookup the sales data from one table and add the assoicated comment rows to the other table when the user is ready to imput comments.
If you want the comment rows automatically added, you can do that with SQL Server triggers. However, I don't much see the point because you don't have the comments at that time.
From Access, go to external data menu and create ODBC links to the Sql Server tables of interest. Then they will appear as tables to Access and your Acccess application can lookup the sales data from one table and add the assoicated comment rows to the other table when the user is ready to imput comments.
If you want the comment rows automatically added, you can do that with SQL Server triggers. However, I don't much see the point because you don't have the comments at that time.
ASKER
The comments will come after the fact. I want to have the users go into Access and add the comments. The comments will be used in a report that will be created in Crystal Reports and will need to be saved in the new table. I'm able to have the comments show in SQL, but the problem I'm running into is that any new data isn't showing up in the table that's linked to the sales order.
>I'm able to have the comments show in SQL, but the problem I'm running into is that any new data isn't showing up in the table that's linked to the sales order.
What new data are you talking about? In my mind, new data = comments and comments aren't added till later. What to you expect to be in the comments table before the comments are added?
What new data are you talking about? In my mind, new data = comments and comments aren't added till later. What to you expect to be in the comments table before the comments are added?
ASKER