• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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.
0
sfrisk
Asked:
sfrisk
  • 4
  • 3
1 Solution
 
dqmqCommented:
Easiest:
Import the SQL table using a create table query from Access
Import the Access table using the import table wizard from SQL Server.

Harder, but doing it all from Access:
Create empty comment table on SQL Server using passthru query
From Access, ODBC link to the empty table and the view
Populate the empty table using an Append Table query (essentially, copying the view's result set to the comment table)



0
 
sfriskAuthor Commented:
I was able to do the "easiest" approach and it works great.  The problem is how do I get the data to automatically update in the new table once new records are added?  
0
 
dqmqCommented:
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.  
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sfriskAuthor Commented:
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.
0
 
dqmqCommented:
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.

0
 
sfriskAuthor Commented:
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.
0
 
dqmqCommented:
>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?



 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now