Solved

ADO AddNew to multiple tables!

Posted on 2006-11-10
11
501 Views
Last Modified: 2012-08-14
Hello,

I would like to create an ADO recordset that basically takes two tables and adds a row to each but I am unable to do this I get a "object or provider is not capable of performing..." error.

Can anyone tell me how to do this?

I want to do this because I have a form that is bound to 2 tables and I want to have a custom ADDNew option.

Basically something like this:

Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM Table1, Table2", cn,  adOpenKeyset, adLockOptimistic
rs.AddNew

ANY IDEAS WOULD BE GREATLY APPRECIATED.

Thanks

Mac
0
Comment
Question by:mac-will01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 20 total points
ID: 17916945
A Recordset object can only be for one table, not two.

You can have a recordset object that runs a query with columns from two tables, but you can't use it to add records to two tables.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17916950
"SELECT * FROM Table1, Table2",

this is a cartesian result and is simply not updateable, try opening each table seperately and doing the add
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17916968
If there are common columns in these tables, you can join them into one recordset object, but you won't be able to add records to both.

"SELECT Table1.*, Table2.* FROM Table1 INNER JOIN Table2 ON Table1.SomeID=Table2.SomeID"
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 30 total points
ID: 17916977
Why not two recordset objects rs1 and rs2 - and AddNew to both of them

Dim rs1 As New ADODB.Recordset, rs2 as ADODB.Recordset

rs1.Open "SELECT * FROM Table1, cn,  adOpenKeyset, adLockOptimistic
rs1.AddNew

rs2.Open "SELECT * FROM Table2", cn,  adOpenKeyset, adLockOptimistic
rs2.AddNew
0
 
LVL 1

Author Comment

by:mac-will01
ID: 17917291
Thanks guys,

I guess you are all saying I can't use AddNew when and ADO recordset has fields from multilple tables?

And as for GRayL's idea I have been tinkering with this but basically I am stuck with the following problem:
(P.S. I know this won't work)

Dim rs1 As New ADODB.Recordset, rs2 as ADODB.Recordset
rs1.Open "SELECT * FROM Table1, cn,  adOpenKeyset, adLockOptimistic
rs1.AddNew

rs2.Open "SELECT * FROM Table2", cn,  adOpenKeyset, adLockOptimistic
rs2.AddNew

Set Me.Recordset = rs1 + rs2  '?????

0
 
LVL 18

Assisted Solution

by:Data-Man
Data-Man earned 30 total points
ID: 17918360
What about Jim's comment about a join between the two tables.

If there is a join, then you could use a single recordset.

What are you trying to do?  I have found that executing an insert statement is faster than opening the recordset, adding a record and then closing it.

Is your data in SQL Server or just in Access?

Mike

0
 
LVL 1

Author Comment

by:mac-will01
ID: 17918614
Basically I have a form that cointains bound fields from two tables.  These two tables have a one-to-one relationship.  I would like to have a form to add a new entry into both tables.

As of now I am only using Access.
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 17918668
If the form is bound then adding data to the underlying tables should be automatic.  What is the Record Source property for the form?

Mike
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17919600
But what from rs1 and rs2?  Shouldn't it be something like:

Set Me.Recordset = rs1!fld1 + rs2.fld1
0
 
LVL 1

Author Comment

by:mac-will01
ID: 17919837
The SQL Record Source property for the form is quite complicated and is actually what is causing the problem.

I select ALL (*) fileds from 2 tables than 3 additional fields from 3 other tables that all optionally link in a chain from my main table.

Unfortunately I think I have to use an unbound form here, I just don't see anyway around it
0
 
LVL 1

Author Comment

by:mac-will01
ID: 17919844
Thanks for the help folks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question