Error 2471

[JIM]

When trying to convert excel files to access I use this subroutine:

Do While Len(strFileName) <> 0
'After Dir function returns all .xl files, it returns a zero length string ""
If IsNull((DLookup("[FileName]", "WOProduction", "[FileName] = '" &
strFileName & "'"))) Then 'If file is not found then enter loop
DoCmd.TransferSpreadsheet acLink, , "WOTempTable", strPath &
strFileName, True 'Link Excel file to Temptable

CurrentDb.Execute ("AppendQuery"), dbFailOnError
'Execute
AppendQuery-query that read from TempTable and Appends the records to
production table

DoCmd.DeleteObject acTable, "Temptable"
'Delete link, but not the file

CurrentDb.Execute ("INSERT INTO WOProduction( [FileName],[ImportDate] )
Values ('" & strFileName & "', #" & Date & "#);"), dbFailOnError
'Insert
file into work orders
End If

strFileName = Dir()
Loop

Access hangs up on the IsNull line and gives me message "The expression you
entered as a query parameter produced this error, '2471'. 'The object
doesn't contain the automation object 'Filename''
I've searched help for automation objects but do not understand the
connection. What do I need to do in this subroutine?
Thanks, JIM

 

[Dirk Goldgar]

In

When trying to convert excel files to access I use this subroutine:

Do While Len(strFileName) <> 0
'After Dir function returns all .xl files, it returns a zero length
string "" If IsNull((DLookup("[FileName]", "WOProduction",
"[FileName] = '" & strFileName & "'"))) Then 'If file is not found
then enter loop DoCmd.TransferSpreadsheet acLink, , "WOTempTable",
strPath &
strFileName, True 'Link Excel file to Temptable

CurrentDb.Execute ("AppendQuery"), dbFailOnError
'Execute
AppendQuery-query that read from TempTable and Appends the records to
production table

DoCmd.DeleteObject acTable, "Temptable"
'Delete link, but not the file

CurrentDb.Execute ("INSERT INTO WOProduction(
[FileName],[ImportDate] ) Values ('" & strFileName & "', #" & Date &

"#);"), dbFailOnError 'Insert
file into work orders
End If

strFileName = Dir()
Loop

Access hangs up on the IsNull line and gives me message "The
expression you entered as a query parameter produced this error,
'2471'. 'The object doesn't contain the automation object 'Filename''
I've searched help for automation objects but do not understand the
connection. What do I need to do in this subroutine?

Are you absolutely sure that the table WOProduction contains a field
named "FileName"? Is it possible you've misspelled the field name?

I'm not sure why you're wrapping extra parentheses around your function
arguments, but I don't see that it has any harmful effect.

 

[JIM]

In

When trying to convert excel files to access I use this subroutine:

Do While Len(strFileName) <> 0
'After Dir function returns all .xl files, it returns a zero length
string "" If IsNull((DLookup("[FileName]", "WOProduction",
"[FileName] = '" & strFileName & "'"))) Then 'If file is not found
then enter loop DoCmd.TransferSpreadsheet acLink, , "WOTempTable",
strPath &
strFileName, True 'Link Excel file to Temptable

CurrentDb.Execute ("AppendQuery"), dbFailOnError
'Execute
AppendQuery-query that read from TempTable and Appends the records to
production table

DoCmd.DeleteObject acTable, "Temptable"
'Delete link, but not the file

CurrentDb.Execute ("INSERT INTO WOProduction(
[FileName],[ImportDate] ) Values ('" & strFileName & "', #" & Date &

"#);"), dbFailOnError 'Insert
file into work orders
End If

strFileName = Dir()
Loop

Access hangs up on the IsNull line and gives me message "The
expression you entered as a query parameter produced this error,
'2471'. 'The object doesn't contain the automation object 'Filename''
I've searched help for automation objects but do not understand the
connection. What do I need to do in this subroutine?

Are you absolutely sure that the table WOProduction contains a field
named "FileName"? Is it possible you've misspelled the field name?

I'm not sure why you're wrapping extra parentheses around your function
arguments, but I don't see that it has any harmful effect.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


I'm not sure how the DLookup works. I pasted directly from an answer in discussion groups. It's my understanding that before appending to a production table there is a way to check if Filename has already been added and to skip in that case. I have a primary key field that could be checked but not sure how to do that.

Thanks, JIM

 

[Dirk Goldgar]

In

I'm not sure how the DLookup works. I pasted directly from an
answer in discussion groups. It's my understanding that before
appending to a production table there is a way to check if Filename
has already been added and to skip in that case. I have a primary
key field that could be checked but not sure how to do that.

The code looks reasonable, for that purpose, but clearly there's
something wrong. In order for it to work, the following things must be
true:

1. There must exist a table or query named "WOProduction". Is there
such a table?

2. That table (or query) must contain a text field named "FileName" and
a date field named "ImportDate". Does it contain these fields? They
must be spelled exactly as shown, except that capitalization isn't
important; for example, "filename" and "Filename" are equivalent to
"FileName", but "File Name" is not.

 

[JIM]

In

The code looks reasonable, for that purpose, but clearly there's
something wrong. In order for it to work, the following things must be
true:

1. There must exist a table or query named "WOProduction". Is there
such a table?

2. That table (or query) must contain a text field named "FileName" and
a date field named "ImportDate". Does it contain these fields? They
must be spelled exactly as shown, except that capitalization isn't
important; for example, "filename" and "Filename" are equivalent to
"FileName", but "File Name" is not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Here is my new code to clear it up:

Private Sub LoadNewWorkOrders_Click()
Dim strPath As String
Dim strFileName As String

strPath = "C:\Documents and Settings\CMC\New Work Orders\"
'Path to directory where new w/os are
strFileName = Dir(strPath & "*.xls")
'Dir function returns only Excel files

Do While Len(strFileName) <> 0
'After Dir function returns all .xl files, it returns a zero length string ""
If IsNull(DLookup("[Filename]", "tblImportedFiles", "[FileName] = '" &
strFileName & "'")) Then 'If file is not found then enter loop
DoCmd.TransferSpreadsheet acLink, , "WOTempTable", strPath &
strFileName, True 'Link Excel file to Temptable

CurrentDb.Execute ("AppendNewWOs"), dbFailOnError
'Execute AppendQuery
that reads from TempTable and Appends the records to production table

DoCmd.DeleteObject acTable, "WOTemptable"
'Delete link, but not the file

CurrentDb.Execute ("INSERT INTO tblImportedFiles(
[FileName],[ImportDate] ) Values ('" & strFileName & "', #" & Date & "#);"),
dbFailOnError
'Insert file into work orders
End If

strFileName = Dir()
Loop

End Sub

I have a table-tblimportedfiles and it has two fields: FileName and
ImportDate. and it has gotten past to error 2471 and now hangs up on
CurrentDb.Execute ("AppendNewWOs"), dbFailOnError
and gives message "Run-time error '3061' Too few parameters, Expected 1.
Something is wrong with my append query.
Any help is appreciated,
JIM

 

[Dirk Goldgar]

In

I have a table-tblimportedfiles and it has two fields: FileName and
ImportDate. and it has gotten past to error 2471 and now hangs up on
CurrentDb.Execute ("AppendNewWOs"), dbFailOnError
and gives message "Run-time error '3061' Too few parameters, Expected
1. Something is wrong with my append query.

Please post the SQL of the query, AppendNewWOs.

 

[JIM]

In

Please post the SQL of the query, AppendNewWOs.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


SQL for query is:

INSERT INTO WOProduction ( WorkOrderID, Region, WODate, ClientName, JobName,
JobAddress1, JobAddress2, JobAddress3, Contact, ContactPhone, ContactMobile,
WOType )
SELECT [WOTempTable].WOTempTable.WorkOrderID AS Expr1, WOTempTable.Region AS
Expr2, WOTempTable.WODate AS Expr3, WOTempTable.ClientName AS Expr4,
WOTempTable.JobName AS Expr5, WOTempTable.JobAddress1 AS Expr6,
WOTempTable.JobAddress2 AS Expr7, WOTempTable.JobAddress3 AS Expr8,
WOTempTable.Contact AS Expr9, WOTempTable.ContactPhone AS Expr10,
WOTempTable.ContactMobile AS Expr11, WOTempTable.WOType AS Expr12
FROM WOTempTable;
Thanks,
JIM

 

[Dirk Goldgar]

In

SQL for query is:

INSERT INTO WOProduction ( WorkOrderID, Region, WODate, ClientName,
JobName, JobAddress1, JobAddress2, JobAddress3, Contact,
ContactPhone, ContactMobile, WOType )
SELECT [WOTempTable].WOTempTable.WorkOrderID AS Expr1,
WOTempTable.Region AS Expr2, WOTempTable.WODate AS Expr3,
WOTempTable.ClientName AS Expr4, WOTempTable.JobName AS Expr5,
WOTempTable.JobAddress1 AS Expr6, WOTempTable.JobAddress2 AS Expr7,
WOTempTable.JobAddress3 AS Expr8, WOTempTable.Contact AS Expr9,
WOTempTable.ContactPhone AS Expr10, WOTempTable.ContactMobile AS
Expr11, WOTempTable.WOType AS Expr12
FROM WOTempTable;

This looks odd:

SELECT [WOTempTable].WOTempTable.WorkOrderID AS Expr1,

Change that to

SELECT WOTempTable.WorkOrderID AS Expr1,

and see if it works.

 

[JIM]

In

SQL for query is:

INSERT INTO WOProduction ( WorkOrderID, Region, WODate, ClientName,
JobName, JobAddress1, JobAddress2, JobAddress3, Contact,
ContactPhone, ContactMobile, WOType )
SELECT [WOTempTable].WOTempTable.WorkOrderID AS Expr1,
WOTempTable.Region AS Expr2, WOTempTable.WODate AS Expr3,
WOTempTable.ClientName AS Expr4, WOTempTable.JobName AS Expr5,
WOTempTable.JobAddress1 AS Expr6, WOTempTable.JobAddress2 AS Expr7,
WOTempTable.JobAddress3 AS Expr8, WOTempTable.Contact AS Expr9,
WOTempTable.ContactPhone AS Expr10, WOTempTable.ContactMobile AS
Expr11, WOTempTable.WOType AS Expr12
FROM WOTempTable;

This looks odd:

SELECT [WOTempTable].WOTempTable.WorkOrderID AS Expr1,

Change that to

SELECT WOTempTable.WorkOrderID AS Expr1,

and see if it works.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Thanks, I did make recommended change and got message 3061 again but this time it says, "Too few parameters, expected 12". It outputs 5 tables: WOTempTable, WOTempTable1, WOTempTable2, WOTempTable3 and WOTempTable4. It looks like the second excel file, I have five excel spreadsheets to convert, is being put into WOTempTable and the first excel file is being put into WOTempTable1-4. I named all my fields to be converted in the excel files to match field names in table. Any other ideas would be greatly appreciated.

 

[JIM]

Going on vacation for a week + but will check response when I return, Thanks
JIM

In

SQL for query is:
INSERT INTO WOProduction ( WorkOrderID, Region, WODate, ClientName,
JobName, JobAddress1, JobAddress2, JobAddress3, Contact,
ContactPhone, ContactMobile, WOType )
SELECT [WOTempTable].WOTempTable.WorkOrderID AS Expr1,
WOTempTable.Region AS Expr2, WOTempTable.WODate AS Expr3,
WOTempTable.ClientName AS Expr4, WOTempTable.JobName AS Expr5,
WOTempTable.JobAddress1 AS Expr6, WOTempTable.JobAddress2 AS Expr7,
WOTempTable.JobAddress3 AS Expr8, WOTempTable.Contact AS Expr9,
WOTempTable.ContactPhone AS Expr10, WOTempTable.ContactMobile AS
Expr11, WOTempTable.WOType AS Expr12
FROM WOTempTable;

This looks odd:

SELECT [WOTempTable].WOTempTable.WorkOrderID AS Expr1,

Change that to

SELECT WOTempTable.WorkOrderID AS Expr1,

and see if it works.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Thanks, I did make recommended change and got message 3061 again but this time it says, "Too few parameters, expected 12". It outputs 5 tables: WOTempTable, WOTempTable1, WOTempTable2, WOTempTable3 and WOTempTable4. It looks like the second excel file, I have five excel spreadsheets to convert, is being put into WOTempTable and the first excel file is being put into WOTempTable1-4. I named all my fields to be converted in the excel files to match field names in table. Any other ideas would be greatly appreciated.

 

[Dirk Goldgar]

In

Going on vacation for a week + but will check response when I return,

I'm going on vacation, too, so I probably won't be replying until after
you get back. Anyone else who wants to pick this up, by all means feel
free.