[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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