asp sql table copy bet. 2 databases access

realmeteo · #1 (**permalink**) 04-10-11, 09:17 AM

I have two databases access on my asp server.

First is on ----------------- first = Server.MapPath("/public/sourcedb.mdb")
Second is on is on ---------- second = Server.MapPath("/public/destinationdb.mdb")

I like to copy table "Users" from First to Second with asp sql code same at "SELECT * INTO second.Users FROM first.Users" but cant write correct sintax

Help me please

include code used

Code:

file config.asp
<%
    Option Explicit
    On Error Resume Next
    Response.Buffer = True
    Response.Expires = 0
    ' ----- ----- ----- ----- -----
    Dim sc, cn, rs, cn_bk, rs_bk
    ' ----- ----- ----- ----- -----
    Function ConnOpen()
        sc = "driver={Microsoft Access Driver (*.mdb)};dbq="
        sc = sc & Server.MapPath("/mdb-database/ecom.mdb")
        Set cn = Server.CreateObject("ADODB.Connection")
        Set rs = Server.CreateObject("ADODB.Recordset")
        cn.Open sc
    End Function
    ' ----- ----- ----- ----- -----
    Function ConnClose()
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    End Function
    ' ----- ----- ----- ----- -----
    Function BackupOpen()
        sc = "driver={Microsoft Access Driver (*.mdb)};dbq="
        sc = sc & Server.MapPath("/mdb-database/master.mdb")
        Set cn_bk = Server.CreateObject("ADODB.Connection")
        Set rs_bk = Server.CreateObject("ADODB.Recordset")
        cn_bk.Open sc
    End Function
    ' ----- ----- ----- ----- -----
    Function BackupClose()
        Set rs_bk = Nothing
        cn_bk.Close
        Set cn_bk = Nothing
    End Function
%>



file copitable.asp
<%@LANGUAGE = VBScript%>
<!--#include file="config.asp"-->
<%
    If Session("ADMIN") <> "OK" Then
        Response.Redirect "default.asp"
    End If
    Dim backup
    backup = Request.QueryString("backup")
%>
<html>
<head>
<title>Dump e backup di un database con ASP</title>
</head>
<body>

<h1>Effettua il dump del database</h1>

<form method="post" action="dump.asp?backup=OK">
    <input type="submit" value="DUMP">
</form>

<a href="logout.asp">Logout</a><br><br>

<%
    If backup = "OK" Then
    	


	  Dim SQL
      Call ConnOpen()
          Call BackupOpen()
           		SQL= " INSERT INTO  prodotti  (Codice, SCategoria, Categoria, Prodotto, 	PDescrizione) SELECT  FROM "& Server.MapPath("/mdb-database/master.mdb").prodotti  (Codice, SCategoria, Categoria, Prodotto, 	PDescrizione)
           		Set RS = cn.Execute(SQL)
           		RS.Close
           
           Call BackupClose()
      Call ConnClose()


    End If
%>

</body>
</html>

it not work!
cable is not copied
help me please

job0107 · #2 (**permalink**) 04-11-11, 12:17 AM

Try using the The SQL SELECT INTO Statement.
SQL SELECT INTO Statement

realmeteo · #3 (**permalink**) 04-11-11, 01:31 AM

Thank you,
my problem is that I don't know how the correct sintax for use this command in my asp scrpt.

I done some test but all fails:

Code:

Dim SQL
      Call ConnOpen()
          Call BackupOpen()
           		_???_ SQL= " INSERT INTO  prodotti  (Codice, SCategoria, Categoria, Prodotto, PDescrizione) SELECT  (Codice, SCategoria, Categoria, Prodotto, PDescrizione) FROM "& Server.MapPath("/mdb-database/master.mdb").prodotti 
           		_???_ Set RS = cn.Execute(SQL)
           		RS.Close           
           Call BackupClose()
      Call ConnClose()

my problem is how to run this command in asp (look for _???_)
can you help me?

job0107 · #4 (**permalink**) 04-12-11, 12:44 AM

No, I don't know enough about ASP to help you.
But I did notice that you are trying to copy the contents of one table to another table in a different database on the same server.
You are trying to use the INSERT INTO command, but after some investigation I believe you should be using the SELECT INTO command to perform your task.
From what I can understand, it looks like it is fairly easy to setup.