Way O/T: ASP/SQL problem
Way O/T: ASP/SQL problem
Author
Discussion

judas

Original Poster:

6,251 posts

290 months

Sunday 27th April 2003
quotequote all
I posted this on the Dreamweaver developers forum, but got no reply so far, so I'm throwing it over to all the IT bods here

BTW: Using ASP3/VBScript/Access DB on IIS

I need to be able to import (ie copy) a record from one Access database into another in my web app. Table and data structure is identical in both DBs. The DB I want to write to uses a DSN connection, the DB I'm reading from uses a DSN-less connection built from form data. Both connections work fine.

What's the best way to go about this short of reading all the data in from one DB and then writing it all back out to the other DB the long winded way (there are over 400 fields so I don't want to have to do that!). I've been looking into Record.CopyRecord but can't figure it out and also using a sub-query in the Insert statement, but again I can't figure this out either

Please help before I go insane...

PetrolTed

34,468 posts

334 months

Monday 28th April 2003
quotequote all
Is this a one off or does it need to be done on a regular basis?

tim_s

299 posts

285 months

Monday 28th April 2003
quotequote all
maybe you could try something like this (not too sure about the exact syntax)...

while not rs.eof

'build the insert into statement

sql = "INSERT INTO tbl ("

for i = 0 to rs.fields.count

if i < rs.fields.count then
sql = sql & rs(i).Name & ","
else
sql = sql & rs(i).Name & ")"
end if

next

sql = sql & " VALUES ("

for i = 0 to rs.fields.count

if i < rs.fields.count then
sql = sql & rs(i) & ","
else
sql = sql & rs(i) & ")"
end if

next

conn2.execute(sql)

rs.movenext
wend

PetrolTed

34,468 posts

334 months

Monday 28th April 2003
quotequote all
Interesting question.

The only solution I can think of at the moment is to enumerate through the results from a query. That allows you to get the field name and value from each column. Then you could build an insert query from that. There must be an easier way though...

--Edited because Tim's beaten me to it...

judas

Original Poster:

6,251 posts

290 months

Monday 28th April 2003
quotequote all
Thanks chaps! I knew there must be some straightforward solution. These kinda things are hard when you're falling asleep at the keyboard

I'll let you know when I get it working