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. 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
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
....
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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". ...
|
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....
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
|
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...
| |