move record {Resolved}

[splazz]

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

i'm using ADO and SQL

thanx in advance


splazz

[Gaffer]

Move:

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

Copy:

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

[splazz]

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

what about:

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


splazz

[Gaffer]

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...

[fllewis]

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

YourConnection.Execute mySQL

[splazz]

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

[tr0n]

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

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

[splazz]

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


splazz

[swatty]

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 .

[mendhak]

Are your ADO references set?

[splazz]

thanx a lot guys, it works!!

it was just the column-problem...


splazz