Appending using SQL

[lazydays]

I am trying to append data from a database held on a webserver, to a replica database on our work server.

I have the following code.

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("Passwords")
objConn.Open

Dim objRS, strSQL
strSQL = "INSERT INTO tblEmailUpdates ( ContactName, CompanyName, Website, Email )" & _
"IN 'F:\Web Sites\SoftSource\Database Information\Passwords.mdb'" & _
"SELECT tblEmailUpdates.ContactName, tblEmailUpdates.CompanyName, tblEmailUpdates.Website, " & _
"tblEmailUpdates.Email " & _
"FROM tblEmailUpdates;"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing


If I take out the INSERT INTO line and just start it from the SELECT part of the statement, it works fine.

Any ideas?

 

[vbkris]

i think u might require two connection strings, another question, why not overwrite the mdb file itself???

Known is handfull, Unknown is worldfull

 

[lazydays]

I don't really want to overwrite.

The plan is to have a database on the server that collects the info required, and then append the data to the other database on the work server, and delete the info on the web server - to keep the file size down.
Therefore, I need the info to keep undating the database on the work server!

Does that all make sense?!!

 

[vbkris]

then i guess u should use 2 connections, one for the local database and one for the server, u have to use both the connection strings to insert data:

sql="select data from server"
rs.open sql,con
do until rs.eof
sql="insert into table values"
con1.execute


con is server's connection while con1 is local connection...

Known is handfull, Unknown is worldfull

 

[lazydays]

OK I'll give it a go!!