Runtime Error 424: Object Required. I have crosscheck and has object 'm referring to; still errors.

[pedie]

Hi, this work perfect within the tables in same db however the errors comes when i try to use this codes to transfer object/files attached in the records.


Please advice if there is something 'm missing...
maybe not the right connection type...or the way 'm referring to something is wrong...

Thanks in advance for helping.

OrginalLink

[/FONT]
[FONT=Courier New]Option Compare Database
Option Explicit
Sub vbxvbncvbncvbnd()
Dim strCNXN As String
Dim CNXN As New ADODB.Connection
Dim rstFrom As ADODB.Recordset
Dim rstTo As ADODB.Recordset
Dim rstMVF As ADODB.Recordset
Dim rstMVT As ADODB.Recordset[/FONT]
[FONT=Courier New]Set CNXN = New ADODB.Connection
strCNXN = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=E:\TestDb.accbd;Uid=;Pwd=;"
CNXN.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=E:\TestDb.accbd;Uid=;Pwd=;"[/FONT]
[FONT=Courier New]Set rstFrom = New ADODB.Recordset
rstFrom.Open "a", CNXN, adOpenDynamic, adLockOptimistic[/FONT]
[FONT=Courier New]Set rstTo = New ADODB.Recordset
rstTo.Open "b", CurrentProject.Connection, adOpenDynamic, adLockOptimistic[/FONT]
[FONT=Courier New]Do While rstFrom.EOF = False
rstTo.AddNew
rstTo!CLIENTIDS = rstFrom![CLIENTIDS]
[COLOR=teal][I][U]Set rstMVF = rstFrom!Attachments.Value
[/U][/I][/COLOR]Set rstMVT = rstTo!Attachments.Value
Do While rstMVF.EOF = False
rstMVT.AddNew
rstMVT!FileData = rstMVF!FileData
rstMVT!FileFlags = rstMVF!FileFlags
rstMVT!FileName = rstMVF!FileName
rstMVT!FileTimeStamp = rstMVF!FileTimeStamp
rstMVT!FileType = rstMVF!FileType
rstMVT!FileURL = rstMVF!FileURL
rstMVT.Update
rstMVF.MoveNext
Loop[/FONT]
[FONT=Courier New]Set rstMVF = Nothing
Set rstMVT = Nothing
rstTo.Update[/FONT]
[FONT=Courier New]rstFrom.MoveNext
Loop[/FONT]
[FONT=Courier New]rstFrom.Close
rstTo.Close[/FONT]
[FONT=Courier New]CNXN.Close
Set CNXN = Nothing
End Sub

 

[Norie]

Pedie

What exactly are in these attachments?

You appear to be trying to create/open/something a recordset here using the attachment in another recordset

Set rstMVF = rstFrom!Attachments.Value

What is it you are actually trying to do?

 

[xenou]

What happens if you just use SQL?

Can you run a query such as:

INSERT INTO B SELECT * FROM A;

Maybe Access will handle all the murky attachment stuff for you. The ADO looks complicated and unpleasant working with these attachment fields - I imagine if anyone is an expert it will (pretty soon) be you.

 

[pedie]

What happens if you just use SQL?

Can you run a query such as:

INSERT INTO B SELECT * FROM A;

Maybe Access will handle all the murky attachment stuff for you. The ADO looks complicated and unpleasant working with these attachment fields - I imagine if anyone is an expert it will (pretty soon) be you.

Yes, one of the field is attachment field, how exactly do i use that SQl line Xen?

 

[Norie]

Pedie

If you are trying to import data from one table to another including attachments then this code isn't going to do it.

If anything the attachments are OLEObjects, but they are definitely not recordsets.

Also why are you looping?

If you just want to transfer all the data from one table to another just do the whole thing in one go.

That should be able to be done with SQL like xenou suggests.

 

[Norie]

PS Forgot to add, this could all be done without ADO.

There's the CopyObject method which you can use to copy entire objects, including tables.

DoCmd.CopyObject "B", acTable, "A"

There's also a DestinationDatabase argument, which just might copy an object to another database.

 

[pedie]

Thanks again guys! for helping...
Tried like this and error, it startes INSERT INTO STATEMENT cannot contain Multi Value fields [which is attachement field]

Thanks again.
Maybe copying in the whole table at once and deleting not required data is easiest way out now...

[/FONT]
[FONT=Courier New]Sub INSERTINTO()
Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute " INSERT INTO b " _
        & "SELECT * " _
        & "FROM [a];"
        
dbs.Close
End Sub

 

[xenou]

Tried like this and error, it startes INSERT INTO STATEMENT cannot contain Multi Value fields [which is attachement field]

Okay. I guess that answers that question. Too bad.
ξ

 

[pedie]

Okay. I guess that answers that question. Too bad.

ξ

No worries...not bad at all! It is good to know that this wont make it work with SQL on Multi value fields...

Thanks again Xen!!!