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

    passing data from mdb to anoter (2000 sr 1)

    I have 2 mdb.
    server1mdb1 and server2mdb2.
    in all mdb are present a same table named PROVA.
    in PROVA are present many fileds

    my problem is to transfering the data from mdb1 to mdb2. Consider if into table of mdb2 is present a filed SERVIZIO with the unique index. (in effect this controll the duplicate when import the data from mdb1)
    how to make this?

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

    Re: passing data from mdb to anoter (2000 sr 1)

    Please explain exactly what you want to do. Should records with the same value of SERVIZIO be copied, or records without a matching value of SERVIZIO?

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

    Re: passing data from mdb to anoter (2000 sr 1)

    Hi Hans, example:
    when the macro copy from mdb1, and if is present entire line with value in SERVIZIO=36-01030-04601-679441930 and the same line is present into mdb2 with SERVIZIO=36-01030-04601-679441930 not import into mdb2 and process the next line from mdb1...
    Hope i am be clear...:-(

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

    Re: passing data from mdb to anoter (2000 sr 1)

    You can create an append query in MDB2.mdb:

    INSERT INTO PROVA
    SELECT t.*
    FROM PROVA AS t IN 'F:AccessMDB1.mdb'
    WHERE t.SERVIZIO Not In (SELECT SERVIZIO FROM PROVA)

    where F:Access is the path. If you want to do this in code, you can assemble the SQL string
    <code>
    strDatabase = "F:Access.mdb"
    strSQL = "INSERT INTO PROVA SELECT t.* FROM PROVA AS t IN '" & strDatabase & _
    "' WHERE t.SERVIZIO Not In (SELECT SERVIZIO FROM PROVA)"
    </code>
    and then use ADO or DAO or (in Access itself) DoCmd.RunSQL to execute the SQL.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: passing data from mdb to anoter (2000 sr 1)

    If you happen to do this kind of actions a lot, dbPAL might be worth having a look at.
    I bumped into Mike Gunderloy's review of this software by accident, a while ago.
    It looked promising but I didn't take the time yet to explore it in detail...
    They offer a payed version, a free trial and a free version for mySQL only.

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

    Re: passing data from mdb to anoter (2000 sr 1)

    First good happy easter!
    This is a good idea?

    dim rstSrc as ADODB.Recordset, rstDest as ADODB.Recordset
    dim conSrc as ADODB.Connection, conDest as ADODB.Connection
    dim lngMaxRcs as long, lngCurRc as long, lngMFlds as long, lngFld as long


    'SOURCE
    Set MDB2 = Server.CreateObject("ADODB.Connection")'DESTINATIO N
    path1 = Server.MapPath("DBMDB2.MDB")
    conDest.connectionstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path1 & ";"
    MDB2 .Open strConnection
    Set rstSRC = Server.CreateObject("ADODB.Recordset")
    'SOURCE

    'DESTINATION
    Set MDB1 = Server.CreateObject("ADODB.Connection")'DESTINATIO N
    path2 = Server.MapPath("DBMDB1.MDB")
    conDest.connectionstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path2 & ";"
    MDB1 .Open strConnection
    Set rstdest= Server.CreateObject("ADODB.Recordset")
    'DESTINATION

    '
    rstsrc.open "Select * from EXTRA_OUT", conRst, 3, 3, 1 'static, optimistic, adcmdtext
    rstdest.open "Select * from EXTRA_IN", conDest, 2, 3, 1 'dynamic, optimistic, adcmdtext

    rstsrc.movelast
    rstsrc.movefirst
    lngMaxRcs = rstsrc.recordcount
    lngMFlds = rstsrc.fields.count

    for lngCurRc = 1 to lngMaxRcs
    rstdest.addnew
    for lngFld = 0 to lngMFlds-1
    rstsrc(lngfld) = rstdest(lngfld)
    next
    rstdest.update
    rstsrc.movenext
    next

    '
    rstsrc.close
    rstdest.close
    consrc.close
    condest.close

    '
    set rstsrc=nothing
    set rstdest=nothing
    set consrc=nothing
    set condest=nothing

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

    Re: passing data from mdb to anoter (2000 sr 1)

    Where do you want to run this code? In Access, or in Excel, or in an ASP page?

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

    Re: passing data from mdb to anoter (2000 sr 1)

    First i want run in Excel after if the mnacro work i arrange for ASP;-)

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: passing data from mdb to anoter (2000 sr 1)

    My books on ASP say that all variables in VBscript are of type variant, so when you declare them you don't give them a type.

    e.g. dim conn

    When I add "as object" I always get an error.
    Regards
    John



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

    Re: passing data from mdb to anoter (2000 sr 1)

    Added: See John Hutchison's reply below!

    When you use this code in ASP, you should change the declarations such as

    Dim rstSrc As ADODB.Recordset

    to

    Dim rstSrc As Object

    since you can't set references in ASP, as far as I know (others will correct me if I'm wrong).

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

    Re: passing data from mdb to anoter (2000 sr 1)

    Thanks for the correction. I don't do ASP at all.

    I have added a remark to my reply to Sal21.

Posting Permissions

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