We help IT Professionals succeed at work.

Appending a recordset to an existing table

directxBOB
directxBOB asked
on
1,422 Views
Last Modified: 2013-11-27
I make a call to a stored procedure which returns a recorset, that exactly matches a table I have.

What is the best way to append this recordset onto an existing table. Is it possible to check that the primary key does not conflict with an existing one?

Cheers
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Open an ADO recordset on the Access table, then loop through the recordset returned by your stored proc, use the AddNew method to add a record, assign the values then loop back to  Addnew.

Author

Commented:
Sorry to ask such a dumb question but how do I open an ADO Recordset on the access table?

I have a table called Missions, which is open and has it's entries. I have a Query which pulls down all the relevant data.

I would assume I want to be looking at the table Missions (bare in mind I am using Access 2007 which has an overhauled menu.)
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ah so it is actually done with coding now. Super, this is something I can get my head around instead of navigating through these damn access menus.

Is it just VB or can C# be used in these forms?

Regarding the code

      !Field1 = rsSQL!Field1
      '... add other field assignments

I assume I add all my field assignment here for the entire recordset? Also would this work with earlier versions of Access or is it 2007 specific?
CERTIFIED EXPERT

Commented:
VB only I'm afraid.
Yes, add all the fields you want to assignment
The DAO recordset manipulation should work at least back to Access 2.

Author

Commented:
Now finally one last question (fingers crossed)

I have the table open which I want to have the above DAO code. So where do I go from here? I clicked on the VIsual Basic button but it would appear this requires a form so I can do anything code wise.

Or doi I create a module?

I am just confused between the steps of hooking up the code with my table, how and when the code gets run etc.
CERTIFIED EXPERT

Commented:
The easiest way to get straight to the code project is to use the shortcut key combination alt+F11
Add a module to the code projext and drop the code in there. You don't need anything else open (<I have the table open >?) - it's all done in code.
But if you're asking this question I wonder whether you've got a recordset for your Stored Proc output yet?

Author

Commented:
I created a pass through query and did :

exec myStoredProcedure

which pumps out a recordset. So once I saw that I had a feeling there should be a way to get the information from there out into my table.

Author

Commented:
Looking at the code

Set rs = db.OpenRecordset("MyAccessTable", dbOpenTable)

I can go ahead and call my stored procedure directly, which would  populate the recordset?
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I just noticed that Acces 2007 has an unmathced query wizard which I can use to return rows which do not match between my Passthrough Query and my Tablle.

Would I need a 3rd query to take the results of this query and insert them into the table, or is there a quick way to do this in the same query (ie insert result set into table x)
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.