Copy / Archive DB table - Xtreme Visual Basic Talk

04-11-2003, 02:19 PM

ArmyBoy

Newcomer

Join Date: Jan 2003

Posts: 17

Copy / Archive DB table

Hi, I got a table CustomerInfo. I want to copy/archive the records in it to another table CustomerHistory.

I have to do it thru VB. And I'm using ADODC. The source of connection is Data Link File.

Any idea how i can do it?

Thanks! URGENT.........

04-11-2003, 03:38 PM

loquin

Google Hound

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Arizona, USA

Posts: 12,378

The simplest way would be to NOT use the data control, establish a data connection through code, and issue a SQL command to the database to have IT insert the records into the CustomerHistory table from the CustomerInfo table.

Code:

Dim cnDB as Adodb.Connection

Set cnDB = new Adodb.Connection
cnDB.Open YourConnectionString

cnDB.Execute "Insert into CustomerHistory Select * From CustomerInfo"

cnDB.Close
set cnDB = nothing

__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

04-11-2003, 07:02 PM

ArmyBoy

Newcomer

Join Date: Jan 2003

Posts: 17

Thanks loquin! But is there no way to do it using the data control? Cos i really have to use it.

04-11-2003, 09:14 PM

loquin

Google Hound

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Arizona, USA

Posts: 12,378

Is there a reason that you must use the data control? because the only reason that I could think of for a requirement of using something as ineffecient as the data control is for homework.

04-11-2003, 11:46 PM

ArmyBoy

Newcomer

Join Date: Jan 2003

Posts: 17

Haha! You are definitely right loquin.

But it is also because I need the ADODC so that I can use the Data Link File. I'll use ur codes if no other way.

04-12-2003, 03:59 AM

ArmyBoy

Newcomer

Join Date: Jan 2003

Posts: 17

Loquin, ur code worked great! thanks again

But now I have another problem....

Say if I need to insert records whose entry date is today, how can i do it? This is what i've tried in addition to ur code.

Dim Today As Date
.........
.........
cnDB.Execute "INSERT INTO CusHistory SELECT * FROM CusInfo WHERE cus_date = Today"

The cus_date is a field in a Access Database table with Data Type Date/Time.

I keep getting the error num -2147217904 which says "No value given for one or more parameters"

What's the problem???..........

Last edited by ArmyBoy; 04-12-2003 at 04:13 AM.

04-12-2003, 04:59 AM

ArmyBoy

Newcomer

Join Date: Jan 2003

Posts: 17

haha solved!

nsDB.Execute "INSERT INTO NoShows SELECT * FROM Reservation WHERE arr_date = #" & Format(Now, "mm/dd/yyyy") & "#"

04-12-2003, 01:04 PM

Thinker

Iron-Fisted Programmer

Retired Moderator
* Guru *

Join Date: Jul 2001

Location: Fayetteville Arkansas USA

Posts: 18,127

There is no requirement to use the ADO datacontrol just to use a data-
link file. In fact there is no requirement to use a data-link file anyway,
but if you insist on using it, you can specify it as the ConnectionString
for a Connection object using...

Code:

Dim cnDB As ADODB.Connection

Set cnDB = New ADODB.Connection
cnDB.ConnectionString = "File Name=" & App.Path & "\mydatalink.udl"
cnDB.Open

__________________
Posting Guidelines