473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

What is the Best Way to Copy a Recordset?

I have a table in MS Access 2003 that contains records that I would
like to copy to the end of the table. There is one slight deviation
from just doing a straightforwared COPY, however, in that I want to
append the new records using different value of column 1.

Here's an example of what I'm talking about:

Values in in Table1 before "copy" operation:

Bill, 3200 Palm Blvd
Bill, 1425 North Drive
Sally, 234 Washington Drive
Sally, 13475 Harding Street
Tom, 2433 Folk Street

Now I want to take all of Bill's records and copy them over to Hank so
that I end up with:

Bill, 3200 Palm Blvd
Bill, 1425 North Drive
Sally, 234 Washington Drive
Sally, 13475 Harding Street
Tom, 2433 Folk Street
Hank, 3200 Palm Blvd
Hank, 1425 North Drive

(The value "Hank" is provided via a local variable)

What's the best way to do this? I would really like to execute an SQL
INSERT/SELECT statement because it seems like it would be easy, but
I'm not sure how to incorporate the "Hank" value into the SQL
statement.
Nov 13 '05 #1
3 2160
On 25 Jul 2004 20:10:26 -0700, al********@yahoo.com (David Altemir) wrote:
I have a table in MS Access 2003 that contains records that I would
like to copy to the end of the table. There is one slight deviation
from just doing a straightforwared COPY, however, in that I want to
append the new records using different value of column 1.

Here's an example of what I'm talking about:

Values in in Table1 before "copy" operation:

Bill, 3200 Palm Blvd
Bill, 1425 North Drive
Sally, 234 Washington Drive
Sally, 13475 Harding Street
Tom, 2433 Folk Street

Now I want to take all of Bill's records and copy them over to Hank so
that I end up with:

Bill, 3200 Palm Blvd
Bill, 1425 North Drive
Sally, 234 Washington Drive
Sally, 13475 Harding Street
Tom, 2433 Folk Street
Hank, 3200 Palm Blvd
Hank, 1425 North Drive

(The value "Hank" is provided via a local variable)

What's the best way to do this? I would really like to execute an SQL
INSERT/SELECT statement because it seems like it would be easy, but
I'm not sure how to incorporate the "Hank" value into the SQL
statement.


First, I'm going to assume the data is flat and not normalized because you
haven't mentioned an ID, but the ide will translate in any case.

Basically, yous just need to include a constant in the select of your insert
query, and there you go.

INSERT INTO Table1 ([Name], [Address])
SELECT "Hank", [Address]
FROM Table1
WHERE [Name] = "Bill"

So now, to do this from code with data from variables, many folks would insert
the text into the SQL, but there are several good security and reliability
reasons not to do this. Instead, use a querydef and parameters, like the
following example. This demo uses DAO, but the ADO code would be similar,
using a Connection instead of a Database, and a Command object instead of a
Querydef. There are some more differences, so see an ADO reference if you
need to use ADO. This demo has not been tested, so it could have errors.

Option Explicit

' Use Public if called from another module, but better to
' specify Private if called only from within same module.
Public Sub CopyAddressesToNewPerson( _
strFromName As String, _
strToName As String _
)
Dim dbs As DAO.Database
Dim qdf As DAO.Querydef

Set dbs = CurrentDb()
Set qdf = dbs.CreateQuerydef("")
qdf.SQL = "INSERT INTO Table1 ([Name], [Address])" & _
" SELECT prmToName, [Address]" & _
" FROM Table1" & _
" WHERE [Name] = prmFromName;"
qdf.Parameters("prmFromName") = strFromName
qdf.Parameters("prmToName") = strToName

Set qdf = Nothing
Set dbs = Nothing
End Sub
Nov 13 '05 #2
Oops. Significant ommission in the code example. It never executes the
query.

Here's a corrected version, though the code still has not actually been
tested.

' Use Public if called from another module, but better to
' specify Private if called only from within same module.
Public Sub CopyAddressesToNewPerson( _
strFromName As String, _
strToName As String _
)
Dim dbs As DAO.Database
Dim qdf As DAO.Querydef

Set dbs = CurrentDb()
Set qdf = dbs.CreateQuerydef("")
qdf.SQL = "INSERT INTO Table1 ([Name], [Address])" & _
" SELECT prmToName, [Address]" & _
" FROM Table1" & _
" WHERE [Name] = prmFromName;"
qdf.Parameters("prmFromName") = strFromName
qdf.Parameters("prmToName") = strToName

' If dbFailOnError is ommitted, any row insertions that fail
' due to data integrity rules such as primary key violations
' will fail silently. With the dbFailOnError option, if any
' rules would be broken by any row, the query does not update
' any data, and an error is raised in the VBA code.
qdf.Execute dbFailOnError

Set qdf = Nothing
Set dbs = Nothing
End Sub

On Mon, 26 Jul 2004 04:08:01 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:
On 25 Jul 2004 20:10:26 -0700, al********@yahoo.com (David Altemir) wrote:
I have a table in MS Access 2003 that contains records that I would
like to copy to the end of the table. There is one slight deviation
from just doing a straightforwared COPY, however, in that I want to
append the new records using different value of column 1.

Here's an example of what I'm talking about:

Values in in Table1 before "copy" operation:

Bill, 3200 Palm Blvd
Bill, 1425 North Drive
Sally, 234 Washington Drive
Sally, 13475 Harding Street
Tom, 2433 Folk Street

Now I want to take all of Bill's records and copy them over to Hank so
that I end up with:

Bill, 3200 Palm Blvd
Bill, 1425 North Drive
Sally, 234 Washington Drive
Sally, 13475 Harding Street
Tom, 2433 Folk Street
Hank, 3200 Palm Blvd
Hank, 1425 North Drive

(The value "Hank" is provided via a local variable)

What's the best way to do this? I would really like to execute an SQL
INSERT/SELECT statement because it seems like it would be easy, but
I'm not sure how to incorporate the "Hank" value into the SQL
statement.


First, I'm going to assume the data is flat and not normalized because you
haven't mentioned an ID, but the ide will translate in any case.

Basically, yous just need to include a constant in the select of your insert
query, and there you go.

INSERT INTO Table1 ([Name], [Address])
SELECT "Hank", [Address]
FROM Table1
WHERE [Name] = "Bill"

So now, to do this from code with data from variables, many folks would insert
the text into the SQL, but there are several good security and reliability
reasons not to do this. Instead, use a querydef and parameters, like the
following example. This demo uses DAO, but the ADO code would be similar,
using a Connection instead of a Database, and a Command object instead of a
Querydef. There are some more differences, so see an ADO reference if you
need to use ADO. This demo has not been tested, so it could have errors.

Option Explicit

....
Nov 13 '05 #3
Thanks, Steve! Works like a charm.

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<nb********************************@4ax.com>. ..
Dim dbs As DAO.Database
Dim qdf As DAO.Querydef

Set dbs = CurrentDb()
Set qdf = dbs.CreateQuerydef("")
qdf.SQL = "INSERT INTO Table1 ([Name], [Address])" & _
" SELECT prmToName, [Address]" & _
" FROM Table1" & _
" WHERE [Name] = prmFromName;"
qdf.Parameters("prmFromName") = strFromName
qdf.Parameters("prmToName") = strToName
qdf.Execute dbFailOnError

Set qdf = Nothing
Set dbs = Nothing
End Sub

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

37
by: middletree | last post by:
Yesterday, I posted a problem which, by the way, I haven't been able to solve yet. But in Aaron's reply, he questioned why I did several things the way I did. My short answer is that I have a lot...
3
by: Tlm | last post by:
Hello All, I have a form (FrmA) with a subform (SubFrmB) embedded in it. SubFrmB also has a subform embedded in it (SubFrmC) The form's recordsource is based on a table (TblA). SubFrmB's...
1
by: Geir Baardsen | last post by:
Hi! As you can see I have worked with my code and done some changes. Now the code will execute, because of this, and because I took away some code in the OnCurrent event that was probably causing...
2
by: Ken | last post by:
How can I copy data from SQL to MS Access using dsn-less connection and not using SQL DTS? I have this but am stuck. I just want to do an export but can't figure out how to do a "select into". ...
3
by: david | last post by:
Hi, I've been reading tons of posts on how to copy records, but to no avail....i'm still stuck. There are three tables: Main, Sub-Form1 & Sub-Form2 I have a form which displays some data....
7
by: Eric | last post by:
I want to open up 2 record sets. Then, copy everything from one record set to another (excluding a few fields). Is this possible? I need to exclude my primary key, and fields starting with...
4
by: Jim Devenish | last post by:
I wish to copy a table on a SQL Server backend to a table on an Access (.mdb) front end in the simplest way. I have the following to get the recordset but am seeking something easier. Dim...
2
by: mustang123 | last post by:
Hi, I get data from sybase server using ADO Connection and disconnected recordset in my access Database. Now I have a disconnected recordset. I want to insert the contents of this recordset...
0
by: Neelesh2007 | last post by:
Hi all, I have project with VB6.0 and Access-2003. I have datagrid and ADODC as datasource to retrieve records from database. To export the data of datagrid to Excel I am using Copy From Recordset...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.