Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    passing record from mdb to other mdb (2000 sr -1)

    Have this 2 connection in DAO.

    Dim DB As DAO.Database
    Dim RSD As DAO.Recordset
    Dim DB1 As DAO.Database
    Dim RSD1 As DAO.Recordset


    Set DB = DBEngine.OpenDatabase("GCD01F4500DATIPUBBLICAAPPLI CAZIONIANAGRAFICA.MDB")
    Set RSD = DB.OpenRecordset("ANAGRAFICA1")
    Set DB1 = DBEngine.OpenDatabase("GCD01F4500DATIPUBBLICAAPPLI CAZIONICOPE_TROVATI.MDB")
    Set RSD1 = DB.OpenRecordset("COPE_TROVATI")
    (in table ANAGRAFICA1 have 2 field COPE and COPLIST, in table COPE_TROVATI have 1 field COPE)

    My problem is to delete entire recordset from ANAGRAFICA1 if COPLIST is blank and insert , with append, the record COPE in table COPE_TROVATI filed COPE...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passing record from mdb to other mdb (2000 sr -1)

    Wouldn't it be more efficient to store the tables in the same database?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing record from mdb to other mdb (2000 sr

    OK.... Like it!
    i add the table of destination in source mdb..

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passing record from mdb to other mdb (2000 sr

    You can do something like this:

    Dim strSQL As String
    ' Append
    strSQL = "INSERT INTO COPE_TROVATI SELECT COPE FROM ANAGRAFICA1 WHERE COPLIST Is Null"
    db.Execute strSQL, dbFailOnError
    ' Delete
    strSQL = "DELETE * FROM ANAGRAFICA1 WHERE COPLIST Is Null"
    db.Execute strSQL, dbFailOnError

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing record from mdb to other mdb (2000 sr

    excellent!!!!!!!!!!!!!!!

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing record from mdb to other mdb (2000 sr

    Before to lunch code...take a look
    Sub MOVE_COPE()

    Dim DB As DAO.Database
    Dim RSD As DAO.Recordset
    Dim strSQL As String

    Set DB = DBEngine.OpenDatabase("GCD01F4500DATIPUBBLICAAPPLI CAZIONIANAGRAFICA.MDB")
    Set RSD = DB.OpenRecordset("ANAGRAFICA1")
    Set RSD = DB.OpenRecordset("COPE_TROVATI_TOT")

    ' Append
    strSQL = "INSERT INTO COPE_TROVATI_TOT SELECT COPE FROM ANAGRAFICA1 WHERE COPLIST Is Null"
    DB.Execute strSQL, dbFailOnError
    ' Delete
    strSQL = "DELETE * FROM ANAGRAFICA1 WHERE COPLIST Is Null"
    DB.Execute strSQL, dbFailOnError

    DB.Close
    Set DB = Nothing

    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passing record from mdb to other mdb (2000 sr

    You don't need the recordsets any more, so you can remove a few lines:

    Sub MOVE_COPE()
    Dim DB As DAO.Database
    Dim strSQL As String
    Set DB = DBEngine.OpenDatabase("GCD01F4500DATIPUBBLICAAPPLI CAZIONIANAGRAFICA.MDB")
    ' Append
    strSQL = "INSERT INTO COPE_TROVATI_TOT SELECT COPE FROM ANAGRAFICA1 WHERE COPLIST Is Null"
    DB.Execute strSQL, dbFailOnError
    ' Delete
    strSQL = "DELETE * FROM ANAGRAFICA1 WHERE COPLIST Is Null"
    DB.Execute strSQL, dbFailOnError
    DB.Close
    Set DB = Nothing
    End Sub

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing record from mdb to other mdb (2000 sr

    based on this scriprt, is correct to transfering (without delete from source), all record from COPE in MDB ANAGRAFICA1 to COPE IN COPE_TROVATI in MDB ANAGRAFICA1

    Dim strSQL As String
    ' Append
    strSQL = "INSERT INTO COPE_TROVATI SELECT COPE FROM ANAGRAFICA1 WHERE COPLIST Is not Null"
    db.Execute strSQL, dbFailOnError

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passing record from mdb to other mdb (2000 sr

    Looks OK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •