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

[ped]

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..

[June7]

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

[ped]

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

[ped]

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.

[June7]

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.

[ped]

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

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

[June7]

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?

[ped]

June7, thanks again for helping me...

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...

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..

[June7]

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.

[ped]

Thanks again June7, i'll review more and get back....

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

[ped]

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

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

[June7]

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.

[ped]

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)

[June7]

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.

[ped]

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

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

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.