2. ASPRunnerPro General questions
  3. [SOLVED]  Temporary Connection to 2nd Database for Data Transfer
This topic is locked
[SOLVED]

 Temporary Connection to 2nd Database for Data Transfer

 		11/13/2015 17:51:30
ASPRunnerPro General questions
G
 gonzalosb author

Hi All,

I need help to open a connection to a second ACCDB Database to copy a record if mark and save as TRANSFER and close the connection.

Then delete from original database.
no need to see what is in the second database and bough databases have the same structure.
any help will be appreciated.
Sergey Kornilov admin 11/15/2015

Opening a second connection is easy and there is a lot of sample code on the Internet:

http://webcheatsheet.com/asp/database_connection.php
Transferring data will be a bit more complicated. You will have to read each record from the first database, build a proper insert SQL statement, execute it against second database and then delete the original record from first database.
G
 		gonzalosb author 11/18/2015

here is where i'm at on a basic test to copy to a temp database what i add to the main one

this is on AFTER RECORD ADDED


set ConnectionString=Server.CreateObject("ADODB.Connection")

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("db\localtest2.accdb") & ";Jet OLEDB:Database Password=;User ID="

Connection.Open ConnectionString
sql="INSERT INTO Locations (Address) VALUES ('" & values("Address") & "')"
Connection.Execute sql
Connection.Close

Set Connection=Nothing


not work, some times update the main database instead of the temporary one
Sergey Kornilov admin 11/18/2015

You are confusion connection object and connection string in your code. Should be like this:



set Connection=Server.CreateObject("ADODB.Connection")

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("db\localtest2.accdb") & ";Jet OLEDB:Database Password=;User ID="

Connection.Open ConnectionString
sql="INSERT INTO Locations (Address) VALUES ('" & values("Address") & "')"
Connection.Execute sql
Connection.Close

Set Connection=Nothing
G
 		gonzalosb author 11/18/2015

AWESOME !!!!
after an year working on this I can see the light at the end of the tunnel <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=78297&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
works perfectly, thank you so much admin...!!!