DATABASE OBJECT

[LOULOU]

I have to mdb database.

One is DB1.mdb the other is MDB2.mdb

How can I copy a table from DB1.mdb to a table in MDB2.mdb ?

Best regards

[randallc]

Hi,

I had great difficulty finding a syntax which worked; the usual SQL copy don't seem to to me!

I have a workaround if you only have dates, numbers and text(50); lifted from someone's script; perhaps they will own up too?

Here's an example of usage;

EDIT - Works in post later [#7]** - removed from here**

Needs the udf

Best, Randallc

Edited July 20, 2006 by randallc

[LOULOU]

Hi,

I had great difficulty finding a syntax which worked; the usual SQL copy don't seem to to me!

I have a workaround if you only have dates, numbers and text(50); lifted from someone's script; perhaps they will own up too?

Here's an example of usage;

;AccessExampleSimpleCopy.au3 0_18
Local $o_Con,$o_Rs
;DATA========================================================
global  $s_dbname = "c:\test.mdb",$s_Tablename = "table1",$s_Tablename2 = "table2",$Fieldname0 = "txt1",$formatT = "Text(50)",$data0 = "Blah Blah Blah"
global $Fieldname1 = "num1",$formatN = "Number",$data1 = 99,$Fieldname2 = "date1",$formatD = "Date",$data2 = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2)
#include"AccessCOM.au3"
;CREATE========================================================
if not FileExists($s_dbname) then 
    MsgBox(0,"","not exists db=")
    _CreateDB($s_dbname,"","")
EndIf
;CONNECT========================================================
_AccessConnectConn($s_dbname,  $o_Con,0)
if _TableExists( $o_Con,$s_dbname,$s_Tablename) then _DropTable($s_dbname, $s_Tablename, $o_Con)
_CreateTable($s_dbname, $s_Tablename, $o_Con)
_CreateField($s_dbname, $s_Tablename, $Fieldname0, $formatT, $o_Con)
_CreateField($s_dbname, $s_Tablename, $Fieldname1, $formatN, $o_Con)
_CreateField($s_dbname, $s_Tablename, $Fieldname2, $formatD, $o_Con)
; Example of how to add data to a single field of a table
_AddData($s_dbname, $s_Tablename, $Fieldname0, "OPEN", $o_Con)
_AddData($s_dbname, $s_Tablename, $Fieldname0, $data0, $o_Con)
_AddData($s_dbname, $s_Tablename, $Fieldname1, $data1, $o_Con)
_AddData($s_dbname, $s_Tablename, $Fieldname2, $data2, $o_Con)
; Example of how to Copy a particular table [? don't know how to get the format of the fields when creting...?]
$sNewTable="copied"
_CopyTable($s_dbname, $s_Tablename, $sNewTable,  $o_Con)
$query = "SELECT * FROM " & $sNewTable; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch($s_dbname, $query, $o_Con)
_ArrayViewQueryTable($ar_Rows,$query)

Needs the udf [which needs the Array2d udf from my sig, but only for display..]

Best, Randallc

In Fact I have found a very short solution

Select * from table into table in 'toto.mdb' from tabble;

This is working good

[randallc]

Oh yeah! I see mine was only going to copy inside same DB anyway!

Can you show me your command in a brief example script with 2 DBs opened?

Thanks, Randallc

[LOULOU]

Oh yeah! I see mine was only going to copy inside same DB anyway!

Can you show me your command in a brief example script with 2 DBs opened?

Thanks, Randallc

Sorry this is not an autoit version it's a purebasic version because i have not time to translate it but i thinks it's easily translating with my comments

;Open an ODBC Connection
result = MakeConnection("Microsoft Access Driver (*.mdb)","DSN=PureBasic_PAR;Description=Description For Purebasic Access;DBQ="+repert +"\db1.MDB"+";") 
;Create an sql query
requete.s = "SELECT * INTO Operation IN " + "'" + repert + "\CPTSTE.MDB" + "'" + " FROM Operation;"
;Open a database
result = OpenDatabase(0,"PureBasic_PAR","","") 
;Execute the query
DatabaseQuery(0,requete) 
;execute a second query
requete.s = "SELECT * INTO Serveur IN " + "'" + repert + "\CPTSTE.MDB" + "'" + " FROM Serveur;"
DatabaseQuery(0,requete) 
;Close Database
CloseDatabase(0)
;Close connection
  result = DeleteConnection("Microsoft Access Driver (*.mdb)","PureBasic")

Edited July 20, 2006 by LOULOU

[SmOke_N]

Sorry this is not an autoit version it's a purebasic version because i have not time to translate it but i thinks it's easily translating with my comments

Haven't the time? pfft ... isn't this your request for help thread? (If you can comment it then there is time, unelss you don't know how to... now that's a different beast all together)

[randallc]

OK, thanks, got it! (eventually....)

expandcollapse popup

;AccessExampleSimpleCopy.au3 0_19
Local $o_Con,$o_Con2,$o_Rs
;~ #include "Array2D.au3"
;DATA========================================================
global  $s_dbname = "c:\test.mdb",$s_Tablename = "table1",$s_Tablename2 = "table2",$Fieldname0 = "txt1",$formatT = "Text(50)",$data0 = "Blah Blah Blah"
global $Fieldname1 = "num1",$formatN = "Number",$data1 = 99,$Fieldname2 = "date1",$formatD = "Date",$data2 = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2)
#include"AccessCOM.au3"
local $s_db2name = "c:\test2.mdb",$s_Table2name="Table8"
;CREATE========================================================
if not FileExists($s_dbname) then _CreateDB($s_dbname,"","")
;CONNECT========================================================
_AccessConnectConn($s_dbname,  $o_Con,0)
if _TableExists( $o_Con,$s_dbname,$s_Tablename) then _DropTable($s_dbname, $s_Tablename, $o_Con)
_CreateTable($s_dbname, $s_Tablename, $o_Con)
_CreateField($s_dbname, $s_Tablename, $Fieldname0, $formatT, $o_Con)
_CreateField($s_dbname, $s_Tablename, $Fieldname1, $formatN, $o_Con)
_CreateField($s_dbname, $s_Tablename, $Fieldname2, $formatD, $o_Con)
; Example of how to add data to a single field of a table
_AddData($s_dbname, $s_Tablename, $Fieldname0, "OPEN", $o_Con)
_AddData($s_dbname, $s_Tablename, $Fieldname0, $data0, $o_Con)
_AddData($s_dbname, $s_Tablename, $Fieldname1, $data1, $o_Con)
_AddData($s_dbname, $s_Tablename, $Fieldname2, $data2, $o_Con)
;DISPLAY1========================================================
$query = "SELECT * FROM " & $s_Tablename &" IN '"&$s_dbname&"'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch($s_dbname, $query, $o_Con)
_ArrayViewQueryTable($ar_Rows,$query)
;COPY to different table, same DB========================================================
_CopyTableInDB($s_dbname, $s_Tablename, $s_Table2name,  $o_Con)
;DISPLAY1T2========================================================
$query = "SELECT * FROM " & $s_Table2name &" IN '"&$s_dbname&"'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch($s_dbname, $query, $o_Con)
_ArrayViewQueryTable($ar_Rows,$query)
;CREATE2========================================================
if not FileExists($s_db2name) then  _CreateDB($s_db2name,"","")
_AccessConnectConn($s_db2name,  $o_Con2,0)
;COPY to different DB========================================================
_CopyTableToDB($s_dbname, $s_Tablename, $s_db2name,  $o_Con,  $o_Con2)
;DISPLAY2========================================================
$query = "SELECT * FROM " & $s_Tablename &" IN '"&$s_db2name&"'"; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch($s_db2name, $query, $o_Con2)
_ArrayViewQueryTable($ar_Rows,$query)
;~ _ArrayView2D1D($ar_Rows,$sNewTable&"$s_Tablename Array")
;~ _ArrayViewText($ar_Rows,$sNewTable,1,1,1,0,800,600,-1,-1);,$Displayindex=1, $ZeroRowAsHeader=0)

Best, Randall Edited July 20, 2006 by randallc

[LOULOU]

Autoit3 version :

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
$dsn= "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & @scriptdir & "\db1.mdb"
$adoCon = ObjCreate ("ADODB.Connection")
$adoCon.Open ($DSN)
$adoRs = ObjCreate ("ADODB.Recordset")
;You must Clear Operation Table in Destination mdb if not an error happen
$adoSQL = "SELECT * INTO Operation IN " & "'" & "CPTSTE.mdb" & "'" & " FROM Operation;"
$adocon.Open(  $adosql , $adors,$adOpenStatic, $adLockOptimistic)
$adoRs=""
$adocon.close
$adoCon=""

P.S db1.mdb is the entry databade and CPTSTE is the result database where i want to copy the table operation present in db1.mdb

This program is tested and works well.

[randallc]

Hi,

Thanks again for that alternative.

In my function, the reason i wa shaving difficulty was that the command I was using would not work if both DBs were still "open" with active connection; I turned one off and used the other and all was well!

Func _CopyTableToDB($s_dbname, $s_Tablename, $sNewDB,byref  $addConn,byref  $addConn2,$i_adoMDB=1,$USRName="",$PWD="");(byref $sDB1, $sDbTable1, $sNewTable,$i_Execute=1)
    if not isobj($addConn)   then
        _AccessConnectConn($s_dbname, $addConn,$i_adoMDB,$USRName,$PWD)
        $i_NeedToCloseInFunc=1
    Else
        $i_NeedToCloseInFunc=0
    EndIf
    if not isobj($addConn2)   then
        _AccessConnectConn($sNewDB, $addConn2,$i_adoMDB,$USRName,$PWD)
        $i_NeedToCloseInFunc=2
    Else
        $i_NeedToCloseInFunc=3
    EndIf
    if _TableExists( $addConn2,$sNewDB,$s_Tablename) then  _DropTable($sNewDB, $s_Tablename, $addConn2)
    _AccessCloseConn($addConn2)
    ;COPY========================================================
    $queryCommand = "SELECT * INTO " & $s_Tablename &" IN '" & $sNewDB &"' FROM " & $s_Tablename;&" IN '" & $s_dbname &"'"
    $addConn.Execute($queryCommand)
    ;CRHANGE2========================================================
    _AccessConnectConn($sNewDB,  $addConn2,0)
    if $i_NeedToCloseInFunc=1 then $addConn.Close
    if $i_NeedToCloseInFunc=2 then $addConn2.Close
EndFunc   ;==>_CopyTable

Best, Randal

(PS note the destination table is first deleted if it exists, so no error there..., as you say; the usaul SQL commands "create table if not exists" or "drop table if exists" do not seem to be supported by the DB Access or jet engines??)

Edited July 20, 2006 by randallc