move record {Resolved}

hi, is it possible to move a record from one db to antoher???

i'm using ADO and SQL

thanx in advance


splazz

Move:

cn.Execute "insert into newTable select * from oldTable"
cn.Execute "delete from oldTable"

Copy:

cn.Execute "insert into newTable select * from oldTable"

thanx, but i've never heard of cn.Execute...

what about:

mySQL = "INSERT INTO historie SELECT * FROM bestilling WHERE id=" & bestid


splazz

Quote:

---

Originally posted by splazz
thanx, but i've never heard of cn.Execute...

what about:

mySQL = "INSERT INTO historie SELECT * FROM bestilling WHERE id=" & bestid


splazz

---

All that is doing is allocating a SQL string to a variable mySQL. You still need to execute it...

mySQL = "INSERT INTO historie SELECT * FROM bestilling WHERE id=" & bestid

YourConnection.Execute mySQL

i don't understand much of it, but is this right?:

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

mystring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=GMDB.mdb;Persist Security Info=False"
mySQL = "INSERT INTO historie SELECT * FROM bestilling WHERE id=" & bestid

con.Open (mystring)
rs.Open mySQL, con, adOpenKeyset, adLockOptimistic

con.Execute (mySQL)

rs.close
con.close


splazz

yes, but you don't need to open a recordset to execute it. just use this:

VB Code:

Set con = New ADODB.Connection
 
mystring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=GMDB.mdb;Persist Security Info=False"
mySQL = "INSERT INTO historie SELECT * FROM bestilling WHERE id=" & bestid
 
con.Open (mystring)
con.Execute (mySQL)
 
con.close

it won't work, i don't get any error-messages when i run it, but i don't moves anything...


splazz

If you don't suply column names and values you have to be sure all columns are identical in table historie and table bestilling and that they are of the same name , type and in the same order .

Are your ADO references set?

thanx a lot guys, it works!!

it was just the column-problem...


splazz