How to insert an OLE-Object field into another table

[aas1611]

Hi,

I've been trying to insert a field which has OLE Object data type (in this case Text_Word) from one table into another, using the following VB code:

qryInsert = " INSERT INTO Table1(ID, Text_Word) " & _
        " VALUES( " & ID & ",'" & rst![Text_Word] & "')"
...

which rst![Text_Word] is a recordset from another table, say Table2.

Normally this code will work if no OLE Object is involved.

Is there a clue out there?

Andre

 

[CautionMP]

aas1611,
Try removing the single quotes around [tt]rst![Text_Word][/tt] since it's an object and not test.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[aas1611]

Halo,

unfortunately removing the single quote doesn't help. I receive the exact same error message:

Syntax error in query expression:..here some funny characters...(I think because of OLE Object).

I have tried the other way: using the query method in Access (the graphical interface).

It worked for the Text_Word field, but didn't work for the ID. I mean, I have to set the ID to: the maximum ID number between 5000 and 10000 + 1 (my table has a hole between 5000 and 10000, I have to fill it up). So, if the current maximum ID number in TABLE1 is 5100, then the ID to be inserted is 5101.

This ID number calculation is easy in Visual Basic, but I don't know how to do that in the graphical query method.

Can we somehow insert a calculated ID using this query feature in MS Access?

Andre

 

[CautionMP]

aas1611,
Weird, it worked on my machine, welcome to Microsoft.

As far as finding the ID number in the QBE pane, look at the [tt]DMax()[/tt] function. This will let you return the highest number less than 10000 and you can add 1 to it the same way you would in VBA.
[tt]Expr1: DMax("ID","table2","ID<10000")+1[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[kjv1611]

aas1611,

This is not related to the question here, but I was just curuios as to what aas stands for.

Thanks.

 

[aas1611]

Hello kjv1611,
aas is the initial of my name, and guess what 1611 might be!
Are you asking this question because we have the same last 4 characters?

Hello CMP,
Thanks for your effort. After taking a deep breath and thinking in peace, I came up with this solution (before I received your tips): I'm back to using VB code, with a little difference approach:

qryInsert = " INSERT INTO Table1(ID, Text_Word) " & _
        " SELECT " & ID & ", Table.Text_Word FROM Table2 "
...

So I did'nt use recordset at all.

But thanks your tips, I might try it later!

Andre

 

[kjv1611]

aas1611,

Well, if you're using 1611 for the same thing, which I would imagine, it's for the year that the King James Bible was "officially" published. There are tons of other dates, but that's the most "official" date of the whole deal. That's why I use it. I was just curious if yours was the same.