Run-Time Error 3127

[Amour]

Hi I am new to access and was hoping someone could help me.

I am getting this message:

The INSERT INTO statement contains the following unknown field name: 'CELL''
Make sure you have typed the name correctly, and try the operation again.
Error 3127)

when I run this:

maketablestring = " INSERT INTO tblTempBTO IN '" & tmpLetterDB & "' " _
& " SELECT tblPersonnel.*, StrConv([FIRST_NAME],3) AS lcfirst,
StrConv([LAST_NAME],3) AS lclast, StrConv([addr1],3) AS lcaddr1,
StrConv([addr2],3) AS lcaddr2, StrConv([city],3) AS lccity,
StrConv([state],3) AS lcstate, StrConv([zip],3) AS lczip,
StrConv([company],3) AS lccompany, StrConv([title],3) AS lctitle" _
& " FROM tblPersonnel "
If qrystring <> "" Then
maketablestring = maketablestring & " where " & qrystring & "
order by last_name"
End If
dbLocal.Execute (maketablestring)

maketablestring is where I am getting it but I can not find anything that
says:

CELL

So I did some changing where the ("' " & " Select) is in the statement
yet I get different error's.

I tried:

Debug.Print maketablestring

and got this in the window:

INSERT INTO tblTempBTO IN 'c:\asb6\datafiles\letterfile03.mdb' SELECT
tblPersonnel.*, StrConv([first_name],3) AS lcfirst, StrConv([LAST_NAME],3) AS
lclast, StrConv([addr1],3) AS lcaddr1, StrConv([addr2],3) AS lcaddr2,
StrConv([city],3) AS lccity, StrConv([state],3) AS lcstate, StrConv([zip],3)
AS lczip, StrConv([company],3) AS lccompany, StrConv([title],3) AS lctitle
FROM tblPersonnel where [SSN] like '999-99-9999' order by last_name


What am I doing wrong. I know that it may be storing data redundantly in
the new table, but this is the way the old system was written and I am just
trying to fix the problem for now. Later I would like to change the whole
program.

Maybe if I put the table: tblTempBTO within the same database that is
actively running and not use: 'c:\asb6\datafiles\letterfile03.mdb' But I
would need to know how to write that SQL Statement.


Please help

Thank You!

 

[Paolo]

Hi,
I think the problem is that in your source table you have a field called
CELL but in your target table does't exist a field called in this way.
You are executing an append query with the * key and so all the field in the
source table MUST exist in the target table. You also have to create in your
target table the fields you define in the query (e.g. lcfirst, lclast etc.).
Access specify the field CELL in the error because it's the first it find
executing the query.

HTH Paolo

 

[Amour]

Thank You that did it....

Hi,
I think the problem is that in your source table you have a field called
CELL but in your target table does't exist a field called in this way.
You are executing an append query with the * key and so all the field in the
source table MUST exist in the target table. You also have to create in your
target table the fields you define in the query (e.g. lcfirst, lclast etc.).
Access specify the field CELL in the error because it's the first it find
executing the query.

HTH Paolo

Hi I am new to access and was hoping someone could help me.

I am getting this message:

The INSERT INTO statement contains the following unknown field name: 'CELL''
Make sure you have typed the name correctly, and try the operation again.
Error 3127)

when I run this:

maketablestring = " INSERT INTO tblTempBTO IN '" & tmpLetterDB & "' " _
& " SELECT tblPersonnel.*, StrConv([FIRST_NAME],3) AS lcfirst,
StrConv([LAST_NAME],3) AS lclast, StrConv([addr1],3) AS lcaddr1,
StrConv([addr2],3) AS lcaddr2, StrConv([city],3) AS lccity,
StrConv([state],3) AS lcstate, StrConv([zip],3) AS lczip,
StrConv([company],3) AS lccompany, StrConv([title],3) AS lctitle" _
& " FROM tblPersonnel "
If qrystring <> "" Then
maketablestring = maketablestring & " where " & qrystring & "
order by last_name"
End If
dbLocal.Execute (maketablestring)

maketablestring is where I am getting it but I can not find anything that
says:

CELL

So I did some changing where the ("' " & " Select) is in the statement
yet I get different error's.

I tried:

Debug.Print maketablestring

and got this in the window:

INSERT INTO tblTempBTO IN 'c:\asb6\datafiles\letterfile03.mdb' SELECT
tblPersonnel.*, StrConv([first_name],3) AS lcfirst, StrConv([LAST_NAME],3) AS
lclast, StrConv([addr1],3) AS lcaddr1, StrConv([addr2],3) AS lcaddr2,
StrConv([city],3) AS lccity, StrConv([state],3) AS lcstate, StrConv([zip],3)
AS lczip, StrConv([company],3) AS lccompany, StrConv([title],3) AS lctitle
FROM tblPersonnel where [SSN] like '999-99-9999' order by last_name


What am I doing wrong. I know that it may be storing data redundantly in
the new table, but this is the way the old system was written and I am just
trying to fix the problem for now. Later I would like to change the whole
program.

Maybe if I put the table: tblTempBTO within the same database that is
actively running and not use: 'c:\asb6\datafiles\letterfile03.mdb' But I
would need to know how to write that SQL Statement.


Please help

Thank You!