Append table from Complex SQL in Access 2000

V

vman92

I am not having any troubles appending data to a table using the
following SQL in Access 2003. However, when I try to run it in Access
2000 I get the 'Run time error 5": Invalid procedure call or argument'
I don't understand what is going on.


1st query

INSERT INTO TempHoldforEOM ( Field8, Field11, Field4, Field5, Field6,
Vendor )
SELECT [StringExtract-Concatenation].Field8,
[StringExtract-Concatenation].Field11,
[StringExtract-Concatenation].Field4,
[StringExtract-Concatenation].Field5,
[StringExtract-Concatenation].Field6,
[StringExtract-Concatenation].Vendor
FROM [StringExtract-Concatenation], [StringExtract-ConcatenationB]
WHERE ((([StringExtract-Concatenation].Expr2)=[Expr4]));


Concatenation Query

SELECT Left([CleanInterim]![Field11],InStr(1,[CleanInterim]![Field11],"
")-1) AS Expr1, CleanInterim.Field6, [Expr1] & [CleanInterim]![Field6]
AS Expr2, CleanInterim.Field8, CleanInterim.Field4,
CleanInterim.Field5, CleanInterim.Vendor, CleanInterim.Field11
FROM CleanInterim;


Concatenation Query B

SELECT Left(CleanedHistory!Field11,InStr(1,CleanedHistory!Field11,"
")-1) AS Expr3, CleanedHistory.Field6, [Expr3] & CleanedHistory!Field6
AS Expr4
FROM CleanedHistory;


Any help would be greatly appreciated.

P.S. I already moved DAO 3.6 up underneath the Acces 9.0 library & ran
a Compact/Restore.
 
G

Guest

Are you able to run each of the source queries, which appear to be named
"StringExtract-Concatenation" and "StringExtract-ConcatenationB", in Access
2000 (on the same machine) without a problem?

Have you checked for any references marked as MISSING (since these queries
use functions such the built-in Left and Instr functions)?

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
V

vman92

Tom,

I have checked the references. None of the four I am using say
MISSING (VB for Apps, MS Access 9 Obj Lib, MS DAO 3.6 Obj Lib, OLE
Automation). I have tried what Allen suggested by unregistering and
re-registering the dao360.dll.
I did develop the app on a different machine using Access 2002
(could that be the problem?) and the code works fine. Unfortunately, I
need to run the code on the box with Access 2000.


Tom said:
Are you able to run each of the source queries, which appear to be named
"StringExtract-Concatenation" and "StringExtract-ConcatenationB", in Access
2000 (on the same machine) without a problem?

Have you checked for any references marked as MISSING (since these queries
use functions such the built-in Left and Instr functions)?

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

vman92 said:
I am not having any troubles appending data to a table using the
following SQL in Access 2003. However, when I try to run it in Access
2000 I get the 'Run time error 5": Invalid procedure call or argument'
I don't understand what is going on.


1st query

INSERT INTO TempHoldforEOM ( Field8, Field11, Field4, Field5, Field6,
Vendor )
SELECT [StringExtract-Concatenation].Field8,
[StringExtract-Concatenation].Field11,
[StringExtract-Concatenation].Field4,
[StringExtract-Concatenation].Field5,
[StringExtract-Concatenation].Field6,
[StringExtract-Concatenation].Vendor
FROM [StringExtract-Concatenation], [StringExtract-ConcatenationB]
WHERE ((([StringExtract-Concatenation].Expr2)=[Expr4]));


Concatenation Query

SELECT Left([CleanInterim]![Field11],InStr(1,[CleanInterim]![Field11],"
")-1) AS Expr1, CleanInterim.Field6, [Expr1] & [CleanInterim]![Field6]
AS Expr2, CleanInterim.Field8, CleanInterim.Field4,
CleanInterim.Field5, CleanInterim.Vendor, CleanInterim.Field11
FROM CleanInterim;


Concatenation Query B

SELECT Left(CleanedHistory!Field11,InStr(1,CleanedHistory!Field11,"
")-1) AS Expr3, CleanedHistory.Field6, [Expr3] & CleanedHistory!Field6
AS Expr4
FROM CleanedHistory;


Any help would be greatly appreciated.

P.S. I already moved DAO 3.6 up underneath the Acces 9.0 library & ran
a Compact/Restore.
Click to expand...
Click to expand...
 
V

vman92

Also, I don't seem to be getting the numerical portion of the error
message anymore. All it says now is 'Invalid procedure call or
argument'.

Tom,

I have checked the references. None of the four I am using say
MISSING (VB for Apps, MS Access 9 Obj Lib, MS DAO 3.6 Obj Lib, OLE
Automation). I have tried what Allen suggested by unregistering and
re-registering the dao360.dll.
I did develop the app on a different machine using Access 2002
(could that be the problem?) and the code works fine. Unfortunately, I
need to run the code on the box with Access 2000.


Tom said:
Are you able to run each of the source queries, which appear to be named
"StringExtract-Concatenation" and "StringExtract-ConcatenationB", in Access
2000 (on the same machine) without a problem?

Have you checked for any references marked as MISSING (since these queries
use functions such the built-in Left and Instr functions)?

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

vman92 said:
I am not having any troubles appending data to a table using the
following SQL in Access 2003. However, when I try to run it in Access
2000 I get the 'Run time error 5": Invalid procedure call or argument'
I don't understand what is going on.


1st query

INSERT INTO TempHoldforEOM ( Field8, Field11, Field4, Field5, Field6,
Vendor )
SELECT [StringExtract-Concatenation].Field8,
[StringExtract-Concatenation].Field11,
[StringExtract-Concatenation].Field4,
[StringExtract-Concatenation].Field5,
[StringExtract-Concatenation].Field6,
[StringExtract-Concatenation].Vendor
FROM [StringExtract-Concatenation], [StringExtract-ConcatenationB]
WHERE ((([StringExtract-Concatenation].Expr2)=[Expr4]));


Concatenation Query

SELECT Left([CleanInterim]![Field11],InStr(1,[CleanInterim]![Field11],"
")-1) AS Expr1, CleanInterim.Field6, [Expr1] & [CleanInterim]![Field6]
AS Expr2, CleanInterim.Field8, CleanInterim.Field4,
CleanInterim.Field5, CleanInterim.Vendor, CleanInterim.Field11
FROM CleanInterim;


Concatenation Query B

SELECT Left(CleanedHistory!Field11,InStr(1,CleanedHistory!Field11,"
")-1) AS Expr3, CleanedHistory.Field6, [Expr3] & CleanedHistory!Field6
AS Expr4
FROM CleanedHistory;


Any help would be greatly appreciated.

P.S. I already moved DAO 3.6 up underneath the Acces 9.0 library & ran
a Compact/Restore.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

If you are willing to send me a compacted & zipped copy, I will try it using
Access 2000 and 2002. Offhand, I do not see any reason that your query fails
to work in Access 2000, but does work in A2002.

My e-mail address is available at the bottom of the contributor's page
indicated below. Please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
D

dbahooker

shouldn't this be with dots and not the bang? (exclamation point)


Also, I don't seem to be getting the numerical portion of the error
message anymore. All it says now is 'Invalid procedure call or
argument'.

Tom,

I have checked the references. None of the four I am using say
MISSING (VB for Apps, MS Access 9 Obj Lib, MS DAO 3.6 Obj Lib, OLE
Automation). I have tried what Allen suggested by unregistering and
re-registering the dao360.dll.
I did develop the app on a different machine using Access 2002
(could that be the problem?) and the code works fine. Unfortunately, I
need to run the code on the box with Access 2000.


Tom said:
Are you able to run each of the source queries, which appear to be named
"StringExtract-Concatenation" and "StringExtract-ConcatenationB", in Access
2000 (on the same machine) without a problem?

Have you checked for any references marked as MISSING (since these queries
use functions such the built-in Left and Instr functions)?

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I am not having any troubles appending data to a table using the
following SQL in Access 2003. However, when I try to run it in Access
2000 I get the 'Run time error 5": Invalid procedure call or argument'
I don't understand what is going on.


1st query

INSERT INTO TempHoldforEOM ( Field8, Field11, Field4, Field5, Field6,
Vendor )
SELECT [StringExtract-Concatenation].Field8,
[StringExtract-Concatenation].Field11,
[StringExtract-Concatenation].Field4,
[StringExtract-Concatenation].Field5,
[StringExtract-Concatenation].Field6,
[StringExtract-Concatenation].Vendor
FROM [StringExtract-Concatenation], [StringExtract-ConcatenationB]
WHERE ((([StringExtract-Concatenation].Expr2)=[Expr4]));


Concatenation Query

SELECT Left([CleanInterim]![Field11],InStr(1,[CleanInterim]![Field11],"
")-1) AS Expr1, CleanInterim.Field6, [Expr1] & [CleanInterim]![Field6]
AS Expr2, CleanInterim.Field8, CleanInterim.Field4,
CleanInterim.Field5, CleanInterim.Vendor, CleanInterim.Field11
FROM CleanInterim;


Concatenation Query B

SELECT Left(CleanedHistory!Field11,InStr(1,CleanedHistory!Field11,"
")-1) AS Expr3, CleanedHistory.Field6, [Expr3] & CleanedHistory!Field6
AS Expr4
FROM CleanedHistory;


Any help would be greatly appreciated.

P.S. I already moved DAO 3.6 up underneath the Acces 9.0 library & ran
a Compact/Restore.
Click to expand...
Click to expand...
Click to expand...
 
V

vman92

I did check and made sure that all of the required references were
present on both the development machine and the production box. It
ended up being dirty data causing the
Query 2 to fail.


Tom said:
Are you able to run each of the source queries, which appear to be named
"StringExtract-Concatenation" and "StringExtract-ConcatenationB", in Access
2000 (on the same machine) without a problem?

Have you checked for any references marked as MISSING (since these queries
use functions such the built-in Left and Instr functions)?

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

vman92 said:
I am not having any troubles appending data to a table using the
following SQL in Access 2003. However, when I try to run it in Access
2000 I get the 'Run time error 5": Invalid procedure call or argument'
I don't understand what is going on.


1st query

INSERT INTO TempHoldforEOM ( Field8, Field11, Field4, Field5, Field6,
Vendor )
SELECT [StringExtract-Concatenation].Field8,
[StringExtract-Concatenation].Field11,
[StringExtract-Concatenation].Field4,
[StringExtract-Concatenation].Field5,
[StringExtract-Concatenation].Field6,
[StringExtract-Concatenation].Vendor
FROM [StringExtract-Concatenation], [StringExtract-ConcatenationB]
WHERE ((([StringExtract-Concatenation].Expr2)=[Expr4]));


Concatenation Query

SELECT Left([CleanInterim]![Field11],InStr(1,[CleanInterim]![Field11],"
")-1) AS Expr1, CleanInterim.Field6, [Expr1] & [CleanInterim]![Field6]
AS Expr2, CleanInterim.Field8, CleanInterim.Field4,
CleanInterim.Field5, CleanInterim.Vendor, CleanInterim.Field11
FROM CleanInterim;


Concatenation Query B

SELECT Left(CleanedHistory!Field11,InStr(1,CleanedHistory!Field11,"
")-1) AS Expr3, CleanedHistory.Field6, [Expr3] & CleanedHistory!Field6
AS Expr4
FROM CleanedHistory;


Any help would be greatly appreciated.

P.S. I already moved DAO 3.6 up underneath the Acces 9.0 library & ran
a Compact/Restore.
Click to expand...
Click to expand...
 
Top