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

    transfering value from 2 MDB (2000 sr 1)

    Script tage replaced by Pre tags by HansV. Script looks ridiculous.

    thsi macro transfering variuos value from the table of 2 MDB.
    My problem is to controll if the user repeat the transfer of value and make a duplicate.
    To help you in the related table("CDI_50", "PAGATI", "TOTALE", "TOTALE_STORIA") is present a column named SERVIZIO and here is present a unique index...(Hans you know that, sure).
    My problem is during the transfert mode no permit the duplicate line_record...
    <pre>Option Explicit

    Sub TRSFERISCE_VALORI()
    '
    'TRASFERISCE I VALORI DI TABELLE IN UN'ALTRO
    'GRUPPO DI TABELLE IN UN DUE DB SU PATH DIFFERENTI
    '
    Dim strProvider As String
    Dim strDataSource1 As String
    Dim strDataSource2 As String
    Dim rs1 As ADODB.Recordset
    Dim cnn1 As ADODB.Connection
    Dim rs2 As ADODB.Recordset
    Dim cnn2 As ADODB.Connection
    Dim i As Integer
    Dim strSQL As String
    Dim strSQLdelete As String
    Dim varTables As Variant
    Dim CountTables As Integer

    'Set up Private variables
    strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    strDataSource1 = "Data Source=GCD01F4500DATIPUBBLICABOUASSPROVA.MDB;"
    strDataSource2 = "Data Source=E:MACROL0785-AUTCOGLIANDROPROVA1.MDB;"
    varTables = Array("CDI_50", "PAGATI", "TOTALE", "TOTALE_STORIA")

    'Loop through the tables
    For CountTables = LBound(varTables) To UBound(varTables)
    strSQL = "Select * From " & varTables(CountTables)
    strSQLdelete = "Delete * From " & varTables(CountTables)

    'Create the connection to get the data
    Set cnn1 = New ADODB.Connection
    cnn1.Open strProvider & strDataSource1

    'Create the connection to send the data
    Set cnn2 = New ADODB.Connection
    cnn2.Open strProvider & strDataSource2

    'Create recordset reference and set its properties.
    Set rs1 = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset

    'Open first recordset
    With rs1
    Set .ActiveConnection = cnn1
    .Source = strSQL
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Open
    .MoveFirst
    End With

    'Work with Prova1
    With rs2
    Set .ActiveConnection = cnn2
    .Source = strSQLdelete 'Delete all the records in the table
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Source = strSQL
    .Open

    'Add all the new records
    Do Until rs1.EOF
    .AddNew
    For i = 0 To rs1.Fields.Count - 1
    rs2.Fields(i) = rs1.Fields(i)
    Next i
    .Update
    rs1.MoveNext
    Loop
    End With

    Next CountTables


    'Tidy up
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    cnn1.Close
    Set cnn1 = Nothing
    cnn2.Close
    Set cnn2 = Nothing

    End Sub</pre>


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

    Re: transfering value from 2 MDB (2000 sr 1)

    The person who wrote this code for you included the SQL for deleting all records in the target tables, but this SQL is never executed. The line .Source = strSQLDelete makes no sense, and is overruled by .Source = strSQL three lines later.

    Replace

    'Work with Prova1
    With rs2
    Set .ActiveConnection = cnn2
    .Source = strSQLdelete 'Delete all the records in the table
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Source = strSQL
    .Open

    with

    'Work with Prova1
    cnn2.Execute strSQLDelete 'Delete all the records in the table
    With rs2
    Set .ActiveConnection = cnn2
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Source = strSQL
    .Open

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

    Re: transfering value from 2 MDB (2000 sr 1)

    Hummm... but your piece of code delete all record and transefer the value from table to into another?
    But not is a good idea to controll no dupes from the column SERVIZIO the duplicates?
    I bilieve the time is the same or not?

    Note: when i post from my house i bilieve i am near you in your studio room, permit this because you are very,very kinde with me with your patience... I dont understand who you understand me in a flash time Boh! i write with a terrible enghlish;-)

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

    Re: transfering value from 2 MDB (2000 sr 1)

    But it would be a lot more work to check for each record if it is a duplicate.

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

    Re: transfering value from 2 MDB (2000 sr 1)

    Good, you have always the big....

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

    Re: transfering value from 2 MDB (2000 sr 1)

    Hans, one question....
    In the destination table of mdb is present a filed named NR_ASS the propetry is TEXT.
    When the macro transfer the variuos value from prova.mdb into prova1.mdb work well.
    But if i change the propetry of destination table in NUMERIC the macro go in error...
    Peraphs this macro see if the propetry of filed is the same?
    In this case i would want a solution.... Because for me is important to maintain the proptery NUMERIC in the column NR_ASS of destination table/filed...
    Have an idea?

    I have make this experiment:
    I have copy entire column of filed NR_ASS from source mdb (the propetry is TEXT) and copuy in the column of NR_ASS of destination table in to filed NR_ASS Acces tel me "many value can delete...) in my case 1679... (BOH???)

    You must know i work with Access only from 2 months...

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

    Re: transfering value from 2 MDB (2000 sr 1)

    1. What kind of number do you want NR_ASS to be? Integer, Long Integer, Single, Double?
    2. Does the NR_ASS field always have a value in the source table, or can it be null (blank) in some records?

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

    Re: transfering value from 2 MDB (2000 sr 1)

    In first time good morning...

    For this:
    1. What kind of number do you want NR_ASS to be? Integer, Long Integer, Single, Double?
    Simple number 1234567890 (in the filed can not present never a comma or dot) max lenght 10 ctr
    (remeber in to source table the column NR_ass is TEXT and in to destination table NR_ASS is number)

    For this:
    2. Does the NR_ASS field always have a value in the source table, or can it be null (blank) in some records?
    Never is always filled

    Iif you want i attach the little 2 mdb?

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

    Re: transfering value from 2 MDB (2000 sr 1)

    The largest Long Integer (whole number without decimals) that Access can store is 2147483647, so your 10-digit numbers may be too large for a Long Integer. So after setting the data type of NR_ASS in the destination table to Number, you must set its field size to Double. A Double has up to 15 signiifcant digits.

    Change this part of the code:

    'Add all the new records
    Do Until rs1.EOF
    .AddNew
    For i = 0 To rs1.Fields.Count - 1
    rs2.Fields(i) = rs1.Fields(i)
    Next i
    .Update
    rs1.MoveNext
    Loop

    to

    'Add all the new records
    Do Until rs1.EOF
    .AddNew
    For i = 0 To rs1.Fields.Count - 1
    If rs1.Fields(i).Name = "NR_ASS" Then
    If Not IsNull(rs1.Fields(i)) Then
    rs2.Fields(i) = CDbl(rs1.Fields(i))
    End If
    Else
    rs2.Fields(i) = rs1.Fields(i)
    End If
    Next i
    .Update
    rs1.MoveNext
    Loop

    Not very elegant, but it should do what you want.

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

    Re: transfering value from 2 MDB (2000 sr 1)

    Please, Hans, test it for me... (i have make this only on table TOTALE, and not for every table present in the mdb)
    The macro go in error when the value of NR_ASS is 5038462795 ion line: rs2.Fields(i) = CDbl(rs1.Fields(i))

    Option Explicit
    Sub TRSFERISCE_VALORI()
    '
    'TRASFERISCE I VALORI DI TABELLE IN UN'ALTRO
    'GRUPPO DI TABELLE IN UN DUE DB SU PATH DIFFERENTI
    '
    Dim strProvider As String
    Dim strDataSource1 As String
    Dim strDataSource2 As String
    Dim rs1 As ADODB.Recordset
    Dim cnn1 As ADODB.Connection
    Dim rs2 As ADODB.Recordset
    Dim cnn2 As ADODB.Connection
    Dim i As Integer
    Dim strSQL As String
    Dim strSQLdelete As String
    Dim varTables As Variant
    Dim CountTables As Integer

    '
    strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    strDataSource1 = "Data Source=C:PROVA.MDB;"
    strDataSource2 = "Data Source=C:TABELLA.MDB;"
    varTables = Array("TOTALE")

    '
    For CountTables = LBound(varTables) To UBound(varTables)
    strSQL = "Select * From " & varTables(CountTables)
    strSQLdelete = "Delete * From " & varTables(CountTables)

    '
    Set cnn1 = New ADODB.Connection
    cnn1.Open strProvider & strDataSource1

    '
    Set cnn2 = New ADODB.Connection
    cnn2.Open strProvider & strDataSource2

    '
    Set rs1 = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset

    '
    With rs1
    Set .ActiveConnection = cnn1
    .Source = strSQL
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Open
    .MoveFirst
    End With

    '
    With rs2
    Set .ActiveConnection = cnn2
    .Source = strSQLdelete 'Delete all the records in the table
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Source = strSQL
    .Open

    'Add all the new records
    Do Until rs1.EOF
    .AddNew
    For i = 0 To rs1.Fields.Count - 1
    If rs1.Fields(i).Name = "NR_ASS" Then
    If Not IsNull(rs1.Fields(i)) Then
    rs2.Fields(i) = CDbl(rs1.Fields(i))
    End If
    Else
    rs2.Fields(i) = rs1.Fields(i)
    End If
    Next i
    .Update
    rs1.MoveNext
    Loop

    End With

    Next CountTables


    '
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    cnn1.Close
    Set cnn1 = Nothing
    cnn2.Close
    Set cnn2 = Nothing

    End Sub

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfering value from 2 MDB (2000 sr 1)

    In Tabella.mdb you have change NR_ASS to number but it is still a Long Integer. Change it to Double.
    Francois

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

    Re: transfering value from 2 MDB (2000 sr 1)

    Good, Francois...Many tks
    And naturally tks my Teacher Hans...
    It work

Posting Permissions

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