469,934 Members | 1,675 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

insert into select

i received a runtime error message 3346 when i ran the insert into
select code. i have check to see if the query values and destination
fields are the same and it appears so. atlas, i have no idea why my
insert into query does not work. if anyone has suggestions, it would
be appreciated. thanks in advance.

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("ETracking")

If rst.RecordCount > 0 Then
Do While Not rst.EOF
db.Execute " INSERT INTO ETracking " _
& " ([ET Number], [ET LN Shortname], [ET Amount], [ET EC
Code])" _
& " SELECT(Max([ET Number])+1) AS [ET Number] FROM
ETracking), tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] " _
& "FROM [tblImportET];"
rst.MoveNext

Loop

End If
Nov 12 '05 #1
4 13120
The syntax of your SELECT is not correct. It looks as if you intend for the
first select column to be a subquery result, but you don't have a SELECT
statement inside the parentheses before the MAX function call.

On 2 Jan 2004 12:21:59 -0800, pi******@yahoo.fr (JMCN) wrote:
i received a runtime error message 3346 when i ran the insert into
select code. i have check to see if the query values and destination
fields are the same and it appears so. atlas, i have no idea why my
insert into query does not work. if anyone has suggestions, it would
be appreciated. thanks in advance.

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("ETracking")

If rst.RecordCount > 0 Then
Do While Not rst.EOF
db.Execute " INSERT INTO ETracking " _
& " ([ET Number], [ET LN Shortname], [ET Amount], [ET EC
Code])" _
& " SELECT(Max([ET Number])+1) AS [ET Number] FROM
ETracking), tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] " _
& "FROM [tblImportET];"
rst.MoveNext

Loop

End If


Nov 12 '05 #2
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<4f********************************@4ax.com>. ..
The syntax of your SELECT is not correct. It looks as if you intend for the
first select column to be a subquery result, but you don't have a SELECT
statement inside the parentheses before the MAX function call.

i thought the select was my statement that added the new [et number].
thanks for the suggestion !!

jung On 2 Jan 2004 12:21:59 -0800, pi******@yahoo.fr (JMCN) wrote:
i received a runtime error message 3346 when i ran the insert into
select code. i have check to see if the query values and destination
fields are the same and it appears so. atlas, i have no idea why my
insert into query does not work. if anyone has suggestions, it would
be appreciated. thanks in advance.

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("ETracking")

If rst.RecordCount > 0 Then
Do While Not rst.EOF
db.Execute " INSERT INTO ETracking " _
& " ([ET Number], [ET LN Shortname], [ET Amount], [ET EC
Code])" _
& " SELECT(Max([ET Number])+1) AS [ET Number] FROM
ETracking), tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] " _
& "FROM [tblImportET];"
rst.MoveNext

Loop

End If

Nov 12 '05 #3
The key thing is you had 2 from clauses and only one select.

On 3 Jan 2004 16:53:11 -0800, pi******@yahoo.fr (JMCN) wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<4f********************************@4ax.com>. ..
The syntax of your SELECT is not correct. It looks as if you intend for the
first select column to be a subquery result, but you don't have a SELECT
statement inside the parentheses before the MAX function call.


i thought the select was my statement that added the new [et number].
thanks for the suggestion !!

jung
On 2 Jan 2004 12:21:59 -0800, pi******@yahoo.fr (JMCN) wrote:
>i received a runtime error message 3346 when i ran the insert into
>select code. i have check to see if the query values and destination
>fields are the same and it appears so. atlas, i have no idea why my
>insert into query does not work. if anyone has suggestions, it would
>be appreciated. thanks in advance.
>
>Set db = DBEngine(0)(0)
>Set rst = db.OpenRecordset("ETracking")
>
>If rst.RecordCount > 0 Then
> Do While Not rst.EOF
> db.Execute " INSERT INTO ETracking " _
> & " ([ET Number], [ET LN Shortname], [ET Amount], [ET EC
>Code])" _
> & " SELECT(Max([ET Number])+1) AS [ET Number] FROM
>ETracking), tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
>tblImportET.[ET EC Code] " _
> & "FROM [tblImportET];"
> rst.MoveNext
>
> Loop
>
>End If


Nov 12 '05 #4
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<vq********************************@4ax.com>. ..
The key thing is you had 2 from clauses and only one select.

thanks steve! i have finally added another select statement within my
insert into.

now what i am running to problems with is the recordset.
i have exactly 104 records. when i tested the code, the end result
was multiple records
for the same loan. where did i go wrong?
so i tried a couple of different do... loop statements and ended up
getting
the same results, that is having multiple records of the same loan.
so if anyone has any suggestions, it would be appreciated :)
thanks in advance and all who gave me advice. jung

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("ETracking")

''If rst.RecordCount > 0 Then
''Do While Not rst.EOF
Do Until rst.EOF
db.Execute " INSERT INTO ETracking([ET Number], [ET LN
Shortname], [ET Amount], [ET EC Code])" _
& " SELECT(SELECT(Max([ET Number])+1) FROM ETracking),
tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] FROM [tblImportET]; "
rst.MoveNext
Loop

Set rst = Nothing
Set tdf = Nothing
Set db = Nothing
'End If
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
20 posts views Thread by Mark Harrison | last post: by
6 posts views Thread by lenygold via DBMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.