Sign in to follow this  
Followers 0
LOULOU

DATABASE OBJECT

9 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by LOULOU

Share this post


Link to post
Share on other sites

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 :D ... 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)

[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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

;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 by randallc

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0