473,326 Members | 1,972 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,326 software developers and data experts.

copying records

I need to copy a record one access record into the same table. I just need
to modify a couple fields that the user will change. Im sure there is an
easy way to do it. I have started just reading the data and writing a SQL
insert command, but there are a ton of fields. It would take forever to get
the datatypes correct and the SQL formatted properly. Anyone know how to
just copy a record and make a couple changes to it?

Thanks
Jeff

May 5 '06 #1
6 1384
Jeff,

If is an easy question however has not a simple answer. If the keys of those
records are for sure completely unique, than it is simple.

Create a SQL Insertcommand with parameters and let it run by using an
ExecuteNonQuery

Because almost all text for OleDb parameters on MSDN are wrong. I give you
this, which is in my opinion one of the correct once. (OleDB uses no named
parameters which is consequent used on MSDN).

http://msdn2.microsoft.com/en-us/lib...enonquery.aspx

I hope this helps,

Cor

"Jeff Brooks" <Je********@discussions.microsoft.com> schreef in bericht
news:7B**********************************@microsof t.com...
I need to copy a record one access record into the same table. I just need
to modify a couple fields that the user will change. Im sure there is an
easy way to do it. I have started just reading the data and writing a SQL
insert command, but there are a ton of fields. It would take forever to
get
the datatypes correct and the SQL formatted properly. Anyone know how to
just copy a record and make a couple changes to it?

Thanks
Jeff

May 5 '06 #2
Cor,

Thanks for the reply. I have used the execnonquery before. It works great,
but I was really hoping to get away from having to build a large insert
command. I was hoping to just copy the dataset and modify the fields I need
modified.
Jeff
"Cor Ligthert [MVP]" wrote:
Jeff,

If is an easy question however has not a simple answer. If the keys of those
records are for sure completely unique, than it is simple.

Create a SQL Insertcommand with parameters and let it run by using an
ExecuteNonQuery

Because almost all text for OleDb parameters on MSDN are wrong. I give you
this, which is in my opinion one of the correct once. (OleDB uses no named
parameters which is consequent used on MSDN).

http://msdn2.microsoft.com/en-us/lib...enonquery.aspx

I hope this helps,

Cor

"Jeff Brooks" <Je********@discussions.microsoft.com> schreef in bericht
news:7B**********************************@microsof t.com...
I need to copy a record one access record into the same table. I just need
to modify a couple fields that the user will change. Im sure there is an
easy way to do it. I have started just reading the data and writing a SQL
insert command, but there are a ton of fields. It would take forever to
get
the datatypes correct and the SQL formatted properly. Anyone know how to
just copy a record and make a couple changes to it?

Thanks
Jeff


May 5 '06 #3
Jeff,

You can and when you have less than 100 fields you can even use a
commanbuilder to build the insert.

First you have than to set a schema in a new dataset using the Fileschema
Than you create a dataadapter

http://msdn.microsoft.com/library/de...chematopic.asp

Fill that with your data in the way you wish.
and use than the commanbuilder

dim cmb = oledbcommandbuilder(TheDataAdapter)

And than the update.

I hope this helps,

Cor

"Jeff Brooks" <Je********@discussions.microsoft.com> schreef in bericht
news:CF**********************************@microsof t.com...
Cor,

Thanks for the reply. I have used the execnonquery before. It works
great,
but I was really hoping to get away from having to build a large insert
command. I was hoping to just copy the dataset and modify the fields I
need
modified.
Jeff
"Cor Ligthert [MVP]" wrote:
Jeff,

If is an easy question however has not a simple answer. If the keys of
those
records are for sure completely unique, than it is simple.

Create a SQL Insertcommand with parameters and let it run by using an
ExecuteNonQuery

Because almost all text for OleDb parameters on MSDN are wrong. I give
you
this, which is in my opinion one of the correct once. (OleDB uses no
named
parameters which is consequent used on MSDN).

http://msdn2.microsoft.com/en-us/lib...enonquery.aspx

I hope this helps,

Cor

"Jeff Brooks" <Je********@discussions.microsoft.com> schreef in bericht
news:7B**********************************@microsof t.com...
>I need to copy a record one access record into the same table. I just
>need
> to modify a couple fields that the user will change. Im sure there is
> an
> easy way to do it. I have started just reading the data and writing a
> SQL
> insert command, but there are a ton of fields. It would take forever
> to
> get
> the datatypes correct and the SQL formatted properly. Anyone know how
> to
> just copy a record and make a couple changes to it?
>
> Thanks
> Jeff
>


May 5 '06 #4
You can use the insert command to read from the table. Example:

insert into table (field1, field2, field3)
select field1, field2, 42 from table where field3 = 18

This way you ony have to supply the values that you want to change, and
just copy the unchanged values.

Jeff Brooks wrote:
I need to copy a record one access record into the same table. I just need
to modify a couple fields that the user will change. Im sure there is an
easy way to do it. I have started just reading the data and writing a SQL
insert command, but there are a ton of fields. It would take forever to get
the datatypes correct and the SQL formatted properly. Anyone know how to
just copy a record and make a couple changes to it?

Thanks
Jeff

May 5 '06 #5
I was hoping I could get away from that. It will be a long insert statement.

"Göran Andersson" wrote:
You can use the insert command to read from the table. Example:

insert into table (field1, field2, field3)
select field1, field2, 42 from table where field3 = 18

This way you ony have to supply the values that you want to change, and
just copy the unchanged values.

Jeff Brooks wrote:
I need to copy a record one access record into the same table. I just need
to modify a couple fields that the user will change. Im sure there is an
easy way to do it. I have started just reading the data and writing a SQL
insert command, but there are a ton of fields. It would take forever to get
the datatypes correct and the SQL formatted properly. Anyone know how to
just copy a record and make a couple changes to it?

Thanks
Jeff

May 5 '06 #6
I am extremely new to vb but i do something in my other language i program
in that i will soon try to re-create in vb.

Basically we have a file such as dbf with 100+ fields.

We have a function that we call and it Loops through the file defenition (
knowing now how many fields there are in a record ).
We call this "Scatter() ""
Scatter looks at the record you are sitting on, loops through the field
names ( that you know from the dbf defenition ) and then
writes each field into an array. So a Record that has 115 fields would have
115 dimension arrays.

So now this function returns the array of all the values in the field.

Now we have another function called " Gather() " and this loops through the
array copying all the values in the array to
the same field # in the database.

In between calling Scatter and Gather, you can change values in an array.
You just have to know which array# you are changin,
which is the field # in the db.

Once i get to this stage ill try to write it in vb. See me in a month or
two ;)
I had a big enough problem last night trying to call one form from another.

Miro.
"Jeff Brooks" <Je********@discussions.microsoft.com> wrote in message
news:7B**********************************@microsof t.com...
I need to copy a record one access record into the same table. I just need
to modify a couple fields that the user will change. Im sure there is an
easy way to do it. I have started just reading the data and writing a SQL
insert command, but there are a ton of fields. It would take forever to
get
the datatypes correct and the SQL formatted properly. Anyone know how to
just copy a record and make a couple changes to it?

Thanks
Jeff

May 8 '06 #7

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

Similar topics

3
by: Jason | last post by:
In enterprise manager I am copying a table from one database to another. I am using the dts wizard to import the data. After I successfully import the data, I open both tables to compare the...
1
by: John | last post by:
Hi I need to copy new records from an access table which can potentially have records added to it all the time by users. I figure I can add a flag for records that have been copied already that...
1
by: Jimbo | last post by:
Hi Guys, I'm hoping one of you will give me a leg up with a problem Im having, which I think is possible to do. Let me explain. I have 2 tables and 1 form - ie 'tableDigit', 'tableNumber' and...
5
by: Nathan Sokalski | last post by:
I am writing an ASP.NET application in which I need to copy DataRows from one DataTable to another. When I use code such as the following: temprows = nodes.Select("state='PA'")...
8
by: daD | last post by:
I'm trying to write a small database that tracks people coming and going from a small campground. I need to have the current guests in the "current" table" and then have the ability to check them...
38
by: Rex | last post by:
In a table, I have number of records belonging to a particular ID now if I enter a value in one of the fileds of this table I want it to be copied in all the records belonging to this particluar...
1
by: francesllee | last post by:
I'm having problems copying subreports when copying a report. Is there an easy way to do this? My childlink is not a AutoNumber but it is a number (which should make is simpler). I just want to...
1
by: cmrhema | last post by:
Hi, I have two tables emp and empcheck1 in the same database emp consits 50 records and empcheck1 consists of 25 records Both the tables have the same design, here empid is the unique key...
2
by: troy_lee | last post by:
What is the best way of copying all the records from a linked table into a replica table that is local on my computer? This is for development work at home where I can not access the main table. Is...
0
by: ElishaThompson | last post by:
I recently had a problem requiring me to locate and copy all records pertaining to a particular client from numerous tables in a database on a given server to tables within an identical database on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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
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.