Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51

    New Access User needs help with Connection to different Database frm Current database

    Hi, I want to transfer data from the form texboxes to table in database2.accdb from current database form...



    Please kindly show me different ways of connecting to database2 and add records to "table2" using vba...

    Thanks alot in advance..

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Try like this:

    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO \\ servername\path\filename.accdb.tablename(ID, Condition) VALUES (" & Me.textboxID & ", " & Me.textboxCondition & ");"
    DoCmd.SetWarnings True
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51

    Thanks

    Thanks for the quick reply..'ll try and get back soon....
    Thanks again...

  4. #4
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51

    need more help.

    Whrere do i enter this code...?
    'm very new and need full support....
    I want form1, texbox1attachement to go to record in table called table_aLL

    Thanks again for assistance.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Code would go in some event, probably Click event of a button on the form. Label the button "Email Record Data". Create the button on form, with button selected open the properties window, on the Events tab select [Event Procedure] in the OnClick event. Double click the ellipses (...) and this will open the VBA editor and show you the new procedure. Type (copy/paste) the suggested code into the procedure. Change the generic references and field/textbox names as appropriate for your project.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    Quote Originally Posted by June7 View Post
    Try like this:
    Quote Originally Posted by June7 View Post

    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO \\ servername\path\filename.accdb.tablename(ID, Condition) VALUES (" & Me.textboxID & ", " & Me.textboxCondition & ");"
    DoCmd.SetWarnings True



    Hi again...
    I dont know, what i need to enter in servername. I assume path is the file path name and the condition...what condition do i enter?
    Is it possible to attach one database with this example?

    Thanks again so much for helping


  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is the project on a network server or on a local workstation? Instead of \\ servername you could refer to drive letter like C:\

    Condition is just a fieldname I used as an example. Use whatever names pertain to your data. If I understand your post, it could be like:
    DoCmd.RunSQL "INSERT INTO C:\path\filename.accdb.tablename(fieldname) VALUES ('" & Me.textbox1attachement & "');"

    What kind of data is in textbox1attachement?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    June7, thanks again for helping me...

    Code:
    DoCmd.RunSQL "INSERT INTO E:\Personal_Files\Access\myTooL 0.1.accdb.table2i" & _
    "(Attachmentfld)VALUES ('" & Me.Attachmentfld & "');"


    I tried this way and it error "Runtime error 438" Object does not support this property or method...

    Code:
    What kind of data is in textbox1attachement? 


    this is an attachment field. that needs to go to the table records. It has attachments in it....


    Thanks again..

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Possible syntax issue is missing space before VALUES. Also, remove the apostrophes. They are required for text fields. I don't know about attachment field. It is quite possible this cannot be done with attachment field. Review this article http://aspalliance.com/1138_Working_..._Access_2007.2

    It states "Adding, deleting, and editing of the attached items is only possible through an Attachments dialog box."

    But it also states "VBA can be used to work with attachments programmatically using the new Attachment Object. It has several properties and methods, and it supports events."

    At the end of the article is a VBA procedure for deleting attachments. Possibly adaptable for saving attachment in another table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    Thanks again June7, i'll review more and get back....

    Thanks again....have a great weekends!!!

  11. #11
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51

    Please help me understand this code & make it work:)

    Hi everyone!
    I have 2 table with same fields..
    Fields are qt1, Q2,Attac which is Attachment field. I want to move all records including attachements in records to the new table...
    How can i do this using any code or below code.
    Thanks in advance for helping...

    Regards
    Pedie
    Code:
    Set rstFrom = dbs.OpenRecordset("SELECT [Risk Assessment and Area Classification].* FROM [Risk Assessment and Area Classification] " & _
    "WHERE ((([Risk Assessment and Area Classification].ProjectRef)= '" & targetProjectRef & "'))" & _
    "ORDER BY [Risk Assessment and Area Classification].[Installation ID]", dbOpenSnapshot)
    
    Set rstTo = dbs.OpenRecordset("Risk Assessment and Area Classification", dbOpenDynaset)
    
    rstFrom.MoveFirst
    Do Until rstFrom.EOF
    
    rstTo.AddNew
    For iFor = 1 To rstFrom.Fields.Count - 1 ' use 1 not 0 as has ID field is Autonumber
    
    If rstFrom.Fields(iFor).Type <> 104 And rstFrom.Fields(iFor).Type <> 109 Then ' types 104 and 109 are Multi-Value field
    
    If rstTo.Fields(iFor).Name = "ProjectRef" Then
    rstTo.Fields(iFor).Value = myProjectRef
    Else
    rstTo.Fields(iFor).Value = rstFrom.Fields(iFor).Value
    End If
    
    Else
    Set rstMVFrom = rstFrom.Fields(iFor).Value
    
    If rstMVFrom.RecordCount > 0 Then
    Set rstMVTo = rstTo.Fields(iFor).Value
    
    Do While rstMVFrom.EOF = False
    rstMVTo.AddNew
    rstMVTo.Fields(0).Value = rstMVFrom.Fields(0).Value
    rstMVTo.Update
    rstMVFrom.MoveNext
    Loop
    rstMVFrom.Close
    rstMVTo.Close
    End If ' rstFrom
    
    End If ' iFor
    Next iFor
    
    rstTo.Update
    
    rstFrom.MoveNext
    Loop ' rstFrom
    
    rst2.Close
    Set rst2 = Nothing
    

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you actually try running this procedure? Put it in the click event of a button. Set a breakpoint. Step debug. What happens? I don't see any reference to other file path or name. Also, not showing DIM statements to declare the recordset variables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    Quote Originally Posted by June7 View Post
    Did you actually try running this procedure? Put it in the click event of a button. Set a breakpoint. Step debug. What happens? I don't see any reference to other file path or name. Also, not showing DIM statements to declare the recordset variables.


    I suppose "Risk Assessment and Area Classification" is table name, ProjectRef = field name and targetProjectRef his variable that he defined...

    I cant understand ' tried debugging...just dont know what to do...

    Looping accroos, then from the record looping again into child records etc....and then going for next record again i guess...

    My table name is Table2,
    feild names are ID, qt1, Q2, Attac 'which is attachment field...

    Please check this for if you can...

    June7, thanks again for assisting me in tis thread


    June7, I didnt know which one which one should be declared as what....so 'm waiting for someone if I can get the help...
    Thanks you came along...Please help me out!
    Set rstFrom = dbs.OpenRecordset("SELECT [Risk Assessment and Area Classification].* FROM [Risk Assessment and Area Classification] " & _
    "WHERE ((([Risk Assessment and Area Classification].ProjectRef)= '" & targetProjectRef & "'))" & _
    "ORDER BY [Risk Assessment and Area Classification].[Installation ID]", dbOpenSnapshot)

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You suppose "Risk Assessment and Area Classification" is table name? You did not write this code? Yes, that would be a table name judging from the way it is used in code.

    This code works with two tables from the same database. Your destination table is in another database. That requires two database objects to be declared. My suggested code does not use the database object to transfer data to another file. However, I don't know if that will work with the Attachment field type. This coding appears to be above your knowledge level. You need to gain more programming skills. Take a class, practice starting with simpler tasks, find someone locally who will sit with you and help you through this.

    If I find time this week I might build a working sample but you would have to adapt it to your specific situation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51

    This is not my code....i looked for related inquiries and found this...
    It is really a pain this multi-value field is giving me...

    I tried this way to within currentdb but wont work...
    it just transfer the value not the multi-value [ that attachments] in the records

    Can you please help?

    Thanks again

    Code:
    Option Compare Database
    Option Explicit
    Sub QWERTYU234567()
    Dim rstFrom As DAO.Recordset
    Dim rstTo As DAO.Recordset
    Dim rstMVFrom As DAO.Recordset
    Dim rstMVTo As DAO.Recordset
    Dim iFor As Long
    Set rstFrom = CurrentDb.OpenRecordset("Table2", dbOpenSnapshot)
    Set rstTo = CurrentDb.OpenRecordset("Table2i", dbOpenDynaset)
    rstFrom.MoveFirst
    Do Until rstFrom.EOF
            rstTo.AddNew
            rstTo!Field1 = rstFrom!Field1
            For iFor = 1 To rstFrom.Fields.Count - 1 ' use 1 not 0 as has ID field is Autonumber
                If rstFrom.Fields(iFor).Type = 104 And rstFrom.Fields(iFor).Type = 109 Then ' types 104 and 109 are Multi-Value field
                Set rstMVFrom = rstFrom.Fields(iFor).Value
                    If rstMVFrom.RecordCount > 0 Then
                    Set rstMVTo = rstTo.Fields(iFor).Value
                    Do While rstMVFrom.EOF = False
                    rstMVTo.AddNew
                    rstMVTo.Fields(0).Value = rstMVFrom.Fields(0).Value
                    rstMVTo.Update
                    rstMVFrom.MoveNext
                    Loop
                    rstMVFrom.Close
                    rstMVTo.Close
                    End If ' rstFrom
                End If ' iFor
            Next iFor
            rstTo.Update
    rstFrom.MoveNext
    Loop ' rstFrom
    rstFrom.Close
    rstTo.Close
    Set rstTo = Nothing
    Set rstFrom = Nothing
    End Sub
    


    Quote Originally Posted by June7 View Post
    You suppose "Risk Assessment and Area Classification" is table name? You did not write this code? Yes, that would be a table name judging from the way it is used in code.
    Quote Originally Posted by June7 View Post

    This code works with two tables from the same database. Your destination table is in another database. That requires two database objects to be declared. My suggested code does not use the database object to transfer data to another file. However, I don't know if that will work with the Attachment field type. This coding appears to be above your knowledge level. You need to gain more programming skills. Take a class, practice starting with simpler tasks, find someone locally who will sit with you and help you through this.

    If I find time this week I might build a working sample but you would have to adapt it to your specific situation.


Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 02-22-2013, 04:57 PM
  2. database connection
    By cbeganesh in forum Access
    Replies: 1
    Last Post: 06-23-2011, 09:25 AM
  3. Replies: 6
    Last Post: 11-18-2010, 04:00 AM
  4. Replies: 4
    Last Post: 08-12-2010, 08:38 AM
  5. Replies: 3
    Last Post: 12-08-2009, 01:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums