import

[Guest]

I would like to capture files that failed to import. If the question is
clear, then please ask.

It works fine. But i just want the program displays files that failed to
import. So i can open failing files to check for the errors


My code is


dim myfile
dim my path
mypath = "C:\your folder and path\"

myfile = dir(mypath & "*.xls")
while <> ""

DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

HERE: I need the code to capture files that failed. Please help.

myfile = dir

wend

 

[John Nurick]

How do you define "failed to import"?

If this is to be a regular operation, it's preferable IMHO either to
ensure that the data is well-formed before you try to import it, or to
start by linking or importing it to a temporary table and then using an
append query to move the data to the main table, "cleaning" it en route.

 

[Guest]

Hi John Nurick,

I hope you or anyone can help. let me descibe what i did what messages i got

When importing, if a file is failed, then I got the windows message. The
message like this ...bla bla bla.. ...etc. Do you want to proceed it? Yes,
No, Help. If I click yes, then the file is imported, but the fields that are
incorrect data type will be blank in MS Access Table (Temptable). If I click
"NO", then file is not imported and it brings me to the windows with message
bla lba bla... End, Debug. If I click on Debug, then it brings me to the
Coding windows for debugging.


I have too many files to import. Thus, i have not checked for data
conversion (fields)in Excel. I just want to import all files at once. If file
is failed, then display a message with file name and an OK button. When
clicking on OK, then this file will not be imported and the process will go
ahead to import the other files. I can keep track with file(s) is (are)
failed, so i can open excel file(s) to check for error data.


Somthing like this:

If (DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath &
myfile) then

MsgBox "Transfer Failed: " & vbCrLf & "XLS: " & myfile
end if

End If

 

[John Nurick]

It sounds as if you're importing to a temporary table, always a good
idea. One way of proceeding would be

1) Instead of using TransferSpreadsheet, create and execute an append
query that puts the records into the temporary table.

2) Run another query to check whether there are any records in the
temporary table that have null values where there should be actual
values. If so, count the file as "failed", store its name (and maybe the
primary keys of the guilty records), empty the temp table and go on to
the next file.

The query code might be something like this:

Dim myfile As String
Dim mypath As String
Dim strSQL As String
Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

mypath = "C:\your folder and path\"
myfile = Dir(mypath & "*.xls")
Set dbD = CurrentDB()

Do While Len(myfile) > 0
'Delete records from temp table
strSQL = "DELETE FROM TempTable;"
dbD.Execute strSQL, dbFailOnError

'Append records from worksheet
strSQL = "INSERT INTO TempTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=" & mypath & myfile _
& ";].[Sheet2$];"
dbD.Execute strSQL, dbFailOnError

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)
If rsR.RecordCount > 0 Then
'There's at least one bad record
'Do whatever's necessary
Else
'All is well
'Transfer records to main table
End If

rsR.Close
myfile = Dir()

Loop

 

[Guest]

Hi John Nurick,

I will put all the code together to see how it works\

First of all, thanks for your answering

Second, I have two department spreadsheets. For Example, the first
department spreadsheet has 40 fields, and the second one has 30 fields. When
importing these two spreadsheets into TempTable, the one with fewer fields
(the second one) will be having blank fields (NULL) in TempTable. It is the
same with lines below

When I was importing, if a file is failed, then I got the windows message.
The message likes this ...bla bla bla.....etc. Do you want to proceed it?
Yes, No, Help. If I click yes, then the file is imported, but the fields that
are incorrect data type will be blank in MS Access Table (Temptable).

Thus, I will always get blank fields even all files are imported without any
error.

So what can I do with these two spreadsheets?

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)

I have too many fields. So I have to check for all fields (For example: A =
null, B = null…ect.)?


If rsR.RecordCount > 0 Then

How does program show files that are failed here?

'There's at least one bad record
'Do whatever's necessary
Else

It sounds as if you're importing to a temporary table, always a good
idea. One way of proceeding would be

1) Instead of using TransferSpreadsheet, create and execute an append
query that puts the records into the temporary table.

2) Run another query to check whether there are any records in the
temporary table that have null values where there should be actual
values. If so, count the file as "failed", store its name (and maybe the
primary keys of the guilty records), empty the temp table and go on to
the next file.

The query code might be something like this:

Dim myfile As String
Dim mypath As String
Dim strSQL As String
Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

mypath = "C:\your folder and path\"
myfile = Dir(mypath & "*.xls")
Set dbD = CurrentDB()

Do While Len(myfile) > 0
'Delete records from temp table
strSQL = "DELETE FROM TempTable;"
dbD.Execute strSQL, dbFailOnError

'Append records from worksheet
strSQL = "INSERT INTO TempTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=" & mypath & myfile _
& ";].[Sheet2$];"
dbD.Execute strSQL, dbFailOnError

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)
If rsR.RecordCount > 0 Then
'There's at least one bad record
'Do whatever's necessary
Else
'All is well
'Transfer records to main table
End If

rsR.Close
myfile = Dir()

Loop

Hi John Nurick,

I hope you or anyone can help. let me descibe what i did what messages i got

When importing, if a file is failed, then I got the windows message. The
message like this ...bla bla bla.. ...etc. Do you want to proceed it? Yes,
No, Help. If I click yes, then the file is imported, but the fields that are
incorrect data type will be blank in MS Access Table (Temptable). If I click
"NO", then file is not imported and it brings me to the windows with message
bla lba bla... End, Debug. If I click on Debug, then it brings me to the
Coding windows for debugging.


I have too many files to import. Thus, i have not checked for data
conversion (fields)in Excel. I just want to import all files at once. If file
is failed, then display a message with file name and an OK button. When
clicking on OK, then this file will not be imported and the process will go
ahead to import the other files. I can keep track with file(s) is (are)
failed, so i can open excel file(s) to check for error data.


Somthing like this:

If (DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath &
myfile) then

MsgBox "Transfer Failed: " & vbCrLf & "XLS: " & myfile
end if

End If

 

[John Nurick]

I don't know a way of trapping the message you get from
TransferSpreadsheet. What I'm suggesting is that you import the records
to the temp table regardless of whether they are good or bad, and then
decide by examining the records (i.e. using a sutable query) whether the
Excel file "passes" or "fails".

But from what you say, you're importing records with different
structures (some with 30 fields and some with 40) into the same table.
This doesn't make sense in normal database operations, unless the 30
fields in one also appear in the other, and the 10 extra fields can all
be Null.

I still feel that the best approach would be to write code to test each
Excel file before trying to import the data it contains.

Hi John Nurick,

I will put all the code together to see how it works\

First of all, thanks for your answering

Second, I have two department spreadsheets. For Example, the first
department spreadsheet has 40 fields, and the second one has 30 fields. When
importing these two spreadsheets into TempTable, the one with fewer fields
(the second one) will be having blank fields (NULL) in TempTable. It is the
same with lines below

When I was importing, if a file is failed, then I got the windows message.
The message likes this ...bla bla bla.....etc. Do you want to proceed it?
Yes, No, Help. If I click yes, then the file is imported, but the fields that
are incorrect data type will be blank in MS Access Table (Temptable).

Thus, I will always get blank fields even all files are imported without any
error.

So what can I do with these two spreadsheets?

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)

I have too many fields. So I have to check for all fields (For example: A =
null, B = null…ect.)?


If rsR.RecordCount > 0 Then

How does program show files that are failed here?

'There's at least one bad record
'Do whatever's necessary
Else

It sounds as if you're importing to a temporary table, always a good
idea. One way of proceeding would be

1) Instead of using TransferSpreadsheet, create and execute an append
query that puts the records into the temporary table.

2) Run another query to check whether there are any records in the
temporary table that have null values where there should be actual
values. If so, count the file as "failed", store its name (and maybe the
primary keys of the guilty records), empty the temp table and go on to
the next file.

The query code might be something like this:

Dim myfile As String
Dim mypath As String
Dim strSQL As String
Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

mypath = "C:\your folder and path\"
myfile = Dir(mypath & "*.xls")
Set dbD = CurrentDB()

Do While Len(myfile) > 0
'Delete records from temp table
strSQL = "DELETE FROM TempTable;"
dbD.Execute strSQL, dbFailOnError

'Append records from worksheet
strSQL = "INSERT INTO TempTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=" & mypath & myfile _
& ";].[Sheet2$];"
dbD.Execute strSQL, dbFailOnError

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)
If rsR.RecordCount > 0 Then
'There's at least one bad record
'Do whatever's necessary
Else
'All is well
'Transfer records to main table
End If

rsR.Close
myfile = Dir()

Loop

DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

Hi John Nurick,

I hope you or anyone can help. let me descibe what i did what messages i got

When importing, if a file is failed, then I got the windows message. The
message like this ...bla bla bla.. ...etc. Do you want to proceed it? Yes,
No, Help. If I click yes, then the file is imported, but the fields that are
incorrect data type will be blank in MS Access Table (Temptable). If I click
"NO", then file is not imported and it brings me to the windows with message
bla lba bla... End, Debug. If I click on Debug, then it brings me to the
Coding windows for debugging.


I have too many files to import. Thus, i have not checked for data
conversion (fields)in Excel. I just want to import all files at once. If file
is failed, then display a message with file name and an OK button. When
clicking on OK, then this file will not be imported and the process will go
ahead to import the other files. I can keep track with file(s) is (are)
failed, so i can open excel file(s) to check for error data.


Somthing like this:

If (DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath &
myfile) then

MsgBox "Transfer Failed: " & vbCrLf & "XLS: " & myfile
end if

End If


:

How do you define "failed to import"?

If this is to be a regular operation, it's preferable IMHO either to
ensure that the data is well-formed before you try to import it, or to
start by linking or importing it to a temporary table and then using an
append query to move the data to the main table, "cleaning" it en route.


On Tue, 8 Mar 2005 11:59:07 -0800, "Minh Le"

I would like to capture files that failed to import. If the question is
clear, then please ask.

It works fine. But i just want the program displays files that failed to
import. So i can open failing files to check for the errors


My code is


dim myfile
dim my path
mypath = "C:\your folder and path\"

myfile = dir(mypath & "*.xls")
while <> ""

DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

HERE: I need the code to capture files that failed. Please help.

myfile = dir

wend

 

[Guest]

Hi,

Thanks so so much,

I really don't know what to do. I currently have imported each file
manually. I created 2 TempTables. One is for 40 fields and the other is for
30 fields. After that, i updated by appending data in TempTables into
MainTable (has 40 fields). It's working really well. It just takes time. But
i have tried to do like what i have posted on here. I hope someday i will
make it work or you or someone can solve this problem. I still have to play
around with this to see how it comes up.

If you have new idea please let me know

Thanks

I don't know a way of trapping the message you get from
TransferSpreadsheet. What I'm suggesting is that you import the records
to the temp table regardless of whether they are good or bad, and then
decide by examining the records (i.e. using a sutable query) whether the
Excel file "passes" or "fails".

But from what you say, you're importing records with different
structures (some with 30 fields and some with 40) into the same table.
This doesn't make sense in normal database operations, unless the 30
fields in one also appear in the other, and the 10 extra fields can all
be Null.

I still feel that the best approach would be to write code to test each
Excel file before trying to import the data it contains.

Hi John Nurick,

I will put all the code together to see how it works\

First of all, thanks for your answering

Second, I have two department spreadsheets. For Example, the first
department spreadsheet has 40 fields, and the second one has 30 fields. When
importing these two spreadsheets into TempTable, the one with fewer fields
(the second one) will be having blank fields (NULL) in TempTable. It is the
same with lines below

When I was importing, if a file is failed, then I got the windows message.
The message likes this ...bla bla bla.....etc. Do you want to proceed it?
Yes, No, Help. If I click yes, then the file is imported, but the fields that
are incorrect data type will be blank in MS Access Table (Temptable).

Thus, I will always get blank fields even all files are imported without any
error.

So what can I do with these two spreadsheets?

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)

I have too many fields. So I have to check for all fields (For example: A =
null, B = null…ect.)?


If rsR.RecordCount > 0 Then

How does program show files that are failed here?

'There's at least one bad record
'Do whatever's necessary
Else

It sounds as if you're importing to a temporary table, always a good
idea. One way of proceeding would be

1) Instead of using TransferSpreadsheet, create and execute an append
query that puts the records into the temporary table.

2) Run another query to check whether there are any records in the
temporary table that have null values where there should be actual
values. If so, count the file as "failed", store its name (and maybe the
primary keys of the guilty records), empty the temp table and go on to
the next file.

The query code might be something like this:

Dim myfile As String
Dim mypath As String
Dim strSQL As String
Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

mypath = "C:\your folder and path\"
myfile = Dir(mypath & "*.xls")
Set dbD = CurrentDB()

Do While Len(myfile) > 0
'Delete records from temp table
strSQL = "DELETE FROM TempTable;"
dbD.Execute strSQL, dbFailOnError

'Append records from worksheet
strSQL = "INSERT INTO TempTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=" & mypath & myfile _
& ";].[Sheet2$];"
dbD.Execute strSQL, dbFailOnError

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)
If rsR.RecordCount > 0 Then
'There's at least one bad record
'Do whatever's necessary
Else
'All is well
'Transfer records to main table
End If

rsR.Close
myfile = Dir()

Loop



DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

On Wed, 9 Mar 2005 10:23:02 -0800, "Minh Le"

Hi John Nurick,

I hope you or anyone can help. let me descibe what i did what messages i got

When importing, if a file is failed, then I got the windows message. The
message like this ...bla bla bla.. ...etc. Do you want to proceed it? Yes,
No, Help. If I click yes, then the file is imported, but the fields that are
incorrect data type will be blank in MS Access Table (Temptable). If I click
"NO", then file is not imported and it brings me to the windows with message
bla lba bla... End, Debug. If I click on Debug, then it brings me to the
Coding windows for debugging.


I have too many files to import. Thus, i have not checked for data
conversion (fields)in Excel. I just want to import all files at once. If file
is failed, then display a message with file name and an OK button. When
clicking on OK, then this file will not be imported and the process will go
ahead to import the other files. I can keep track with file(s) is (are)
failed, so i can open excel file(s) to check for error data.


Somthing like this:

If (DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath &
myfile) then

MsgBox "Transfer Failed: " & vbCrLf & "XLS: " & myfile
end if

End If


:

How do you define "failed to import"?

If this is to be a regular operation, it's preferable IMHO either to
ensure that the data is well-formed before you try to import it, or to
start by linking or importing it to a temporary table and then using an
append query to move the data to the main table, "cleaning" it en route.


On Tue, 8 Mar 2005 11:59:07 -0800, "Minh Le"

I would like to capture files that failed to import. If the question is
clear, then please ask.

It works fine. But i just want the program displays files that failed to
import. So i can open failing files to check for the errors


My code is


dim myfile
dim my path
mypath = "C:\your folder and path\"

myfile = dir(mypath & "*.xls")
while <> ""

DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

HERE: I need the code to capture files that failed. Please help.

myfile = dir

wend

 

[Guest]

Hi John Nurick,

When i have tried the code, but I got the message " User not defined" for
the following lines

Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

I don't know a way of trapping the message you get from
TransferSpreadsheet. What I'm suggesting is that you import the records
to the temp table regardless of whether they are good or bad, and then
decide by examining the records (i.e. using a sutable query) whether the
Excel file "passes" or "fails".

But from what you say, you're importing records with different
structures (some with 30 fields and some with 40) into the same table.
This doesn't make sense in normal database operations, unless the 30
fields in one also appear in the other, and the 10 extra fields can all
be Null.

I still feel that the best approach would be to write code to test each
Excel file before trying to import the data it contains.

Hi John Nurick,

I will put all the code together to see how it works\

First of all, thanks for your answering

Second, I have two department spreadsheets. For Example, the first
department spreadsheet has 40 fields, and the second one has 30 fields. When
importing these two spreadsheets into TempTable, the one with fewer fields
(the second one) will be having blank fields (NULL) in TempTable. It is the
same with lines below

When I was importing, if a file is failed, then I got the windows message.
The message likes this ...bla bla bla.....etc. Do you want to proceed it?
Yes, No, Help. If I click yes, then the file is imported, but the fields that
are incorrect data type will be blank in MS Access Table (Temptable).

Thus, I will always get blank fields even all files are imported without any
error.

So what can I do with these two spreadsheets?

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)

I have too many fields. So I have to check for all fields (For example: A =
null, B = null…ect.)?


If rsR.RecordCount > 0 Then

How does program show files that are failed here?

'There's at least one bad record
'Do whatever's necessary
Else

It sounds as if you're importing to a temporary table, always a good
idea. One way of proceeding would be

1) Instead of using TransferSpreadsheet, create and execute an append
query that puts the records into the temporary table.

2) Run another query to check whether there are any records in the
temporary table that have null values where there should be actual
values. If so, count the file as "failed", store its name (and maybe the
primary keys of the guilty records), empty the temp table and go on to
the next file.

The query code might be something like this:

Dim myfile As String
Dim mypath As String
Dim strSQL As String
Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

mypath = "C:\your folder and path\"
myfile = Dir(mypath & "*.xls")
Set dbD = CurrentDB()

Do While Len(myfile) > 0
'Delete records from temp table
strSQL = "DELETE FROM TempTable;"
dbD.Execute strSQL, dbFailOnError

'Append records from worksheet
strSQL = "INSERT INTO TempTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=" & mypath & myfile _
& ";].[Sheet2$];"
dbD.Execute strSQL, dbFailOnError

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)
If rsR.RecordCount > 0 Then
'There's at least one bad record
'Do whatever's necessary
Else
'All is well
'Transfer records to main table
End If

rsR.Close
myfile = Dir()

Loop



DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

On Wed, 9 Mar 2005 10:23:02 -0800, "Minh Le"

Hi John Nurick,

I hope you or anyone can help. let me descibe what i did what messages i got

When importing, if a file is failed, then I got the windows message. The
message like this ...bla bla bla.. ...etc. Do you want to proceed it? Yes,
No, Help. If I click yes, then the file is imported, but the fields that are
incorrect data type will be blank in MS Access Table (Temptable). If I click
"NO", then file is not imported and it brings me to the windows with message
bla lba bla... End, Debug. If I click on Debug, then it brings me to the
Coding windows for debugging.


I have too many files to import. Thus, i have not checked for data
conversion (fields)in Excel. I just want to import all files at once. If file
is failed, then display a message with file name and an OK button. When
clicking on OK, then this file will not be imported and the process will go
ahead to import the other files. I can keep track with file(s) is (are)
failed, so i can open excel file(s) to check for error data.


Somthing like this:

If (DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath &
myfile) then

MsgBox "Transfer Failed: " & vbCrLf & "XLS: " & myfile
end if

End If


:

How do you define "failed to import"?

If this is to be a regular operation, it's preferable IMHO either to
ensure that the data is well-formed before you try to import it, or to
start by linking or importing it to a temporary table and then using an
append query to move the data to the main table, "cleaning" it en route.


On Tue, 8 Mar 2005 11:59:07 -0800, "Minh Le"

I would like to capture files that failed to import. If the question is
clear, then please ask.

It works fine. But i just want the program displays files that failed to
import. So i can open failing files to check for the errors


My code is


dim myfile
dim my path
mypath = "C:\your folder and path\"

myfile = dir(mypath & "*.xls")
while <> ""

DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

HERE: I need the code to capture files that failed. Please help.

myfile = dir

wend

 

[Guest]

Hi John,

I just set up all the fields in default value to 0(s). Thus, i don't have
any blank fields in TempTable when importing two different structures
spreadsheet. I will try with what you helped to see if it works or not, but i
have a problem with a message "User-defined type not defined" for
DAO.database and DAO.recordset. I have looked in ActiveX Control to see if i
can add DAO in Access, but i haven't seen it. Thanks so much for your reply

Hi,

Thanks so so much,

I really don't know what to do. I currently have imported each file
manually. I created 2 TempTables. One is for 40 fields and the other is for
30 fields. After that, i updated by appending data in TempTables into
MainTable (has 40 fields). It's working really well. It just takes time. But
i have tried to do like what i have posted on here. I hope someday i will
make it work or you or someone can solve this problem. I still have to play
around with this to see how it comes up.

If you have new idea please let me know

Thanks

I don't know a way of trapping the message you get from
TransferSpreadsheet. What I'm suggesting is that you import the records
to the temp table regardless of whether they are good or bad, and then
decide by examining the records (i.e. using a sutable query) whether the
Excel file "passes" or "fails".

But from what you say, you're importing records with different
structures (some with 30 fields and some with 40) into the same table.
This doesn't make sense in normal database operations, unless the 30
fields in one also appear in the other, and the 10 extra fields can all
be Null.

I still feel that the best approach would be to write code to test each
Excel file before trying to import the data it contains.

Hi John Nurick,

I will put all the code together to see how it works\

First of all, thanks for your answering

Second, I have two department spreadsheets. For Example, the first
department spreadsheet has 40 fields, and the second one has 30 fields. When
importing these two spreadsheets into TempTable, the one with fewer fields
(the second one) will be having blank fields (NULL) in TempTable. It is the
same with lines below

When I was importing, if a file is failed, then I got the windows message.
The message likes this ...bla bla bla.....etc. Do you want to proceed it?
Yes, No, Help. If I click yes, then the file is imported, but the fields that
are incorrect data type will be blank in MS Access Table (Temptable).

Thus, I will always get blank fields even all files are imported without any
error.

So what can I do with these two spreadsheets?

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)

I have too many fields. So I have to check for all fields (For example: A =
null, B = null…ect.)?


If rsR.RecordCount > 0 Then

How does program show files that are failed here?

'There's at least one bad record
'Do whatever's necessary
Else



:

It sounds as if you're importing to a temporary table, always a good
idea. One way of proceeding would be

1) Instead of using TransferSpreadsheet, create and execute an append
query that puts the records into the temporary table.

2) Run another query to check whether there are any records in the
temporary table that have null values where there should be actual
values. If so, count the file as "failed", store its name (and maybe the
primary keys of the guilty records), empty the temp table and go on to
the next file.

The query code might be something like this:

Dim myfile As String
Dim mypath As String
Dim strSQL As String
Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

mypath = "C:\your folder and path\"
myfile = Dir(mypath & "*.xls")
Set dbD = CurrentDB()

Do While Len(myfile) > 0
'Delete records from temp table
strSQL = "DELETE FROM TempTable;"
dbD.Execute strSQL, dbFailOnError

'Append records from worksheet
strSQL = "INSERT INTO TempTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=" & mypath & myfile _
& ";].[Sheet2$];"
dbD.Execute strSQL, dbFailOnError

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)
If rsR.RecordCount > 0 Then
'There's at least one bad record
'Do whatever's necessary
Else
'All is well
'Transfer records to main table
End If

rsR.Close
myfile = Dir()

Loop



DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

On Wed, 9 Mar 2005 10:23:02 -0800, "Minh Le"

Hi John Nurick,

I hope you or anyone can help. let me descibe what i did what messages i got

When importing, if a file is failed, then I got the windows message. The
message like this ...bla bla bla.. ...etc. Do you want to proceed it? Yes,
No, Help. If I click yes, then the file is imported, but the fields that are
incorrect data type will be blank in MS Access Table (Temptable). If I click
"NO", then file is not imported and it brings me to the windows with message
bla lba bla... End, Debug. If I click on Debug, then it brings me to the
Coding windows for debugging.


I have too many files to import. Thus, i have not checked for data
conversion (fields)in Excel. I just want to import all files at once. If file
is failed, then display a message with file name and an OK button. When
clicking on OK, then this file will not be imported and the process will go
ahead to import the other files. I can keep track with file(s) is (are)
failed, so i can open excel file(s) to check for error data.


Somthing like this:

If (DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath &
myfile) then

MsgBox "Transfer Failed: " & vbCrLf & "XLS: " & myfile
end if

End If


:

How do you define "failed to import"?

If this is to be a regular operation, it's preferable IMHO either to
ensure that the data is well-formed before you try to import it, or to
start by linking or importing it to a temporary table and then using an
append query to move the data to the main table, "cleaning" it en route.


On Tue, 8 Mar 2005 11:59:07 -0800, "Minh Le"

I would like to capture files that failed to import. If the question is
clear, then please ask.

It works fine. But i just want the program displays files that failed to
import. So i can open failing files to check for the errors


My code is


dim myfile
dim my path
mypath = "C:\your folder and path\"

myfile = dir(mypath & "*.xls")
while <> ""

DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

HERE: I need the code to capture files that failed. Please help.

myfile = dir

wend

 

[John Nurick]

Hi Minh Le,

Go to Tools|References and add a reference to the Microsoft DAO 3.6
Object Library.

Hi John Nurick,

When i have tried the code, but I got the message " User not defined" for
the following lines

Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

I don't know a way of trapping the message you get from
TransferSpreadsheet. What I'm suggesting is that you import the records
to the temp table regardless of whether they are good or bad, and then
decide by examining the records (i.e. using a sutable query) whether the
Excel file "passes" or "fails".

But from what you say, you're importing records with different
structures (some with 30 fields and some with 40) into the same table.
This doesn't make sense in normal database operations, unless the 30
fields in one also appear in the other, and the 10 extra fields can all
be Null.

I still feel that the best approach would be to write code to test each
Excel file before trying to import the data it contains.

Hi John Nurick,

I will put all the code together to see how it works\

First of all, thanks for your answering

Second, I have two department spreadsheets. For Example, the first
department spreadsheet has 40 fields, and the second one has 30 fields. When
importing these two spreadsheets into TempTable, the one with fewer fields
(the second one) will be having blank fields (NULL) in TempTable. It is the
same with lines below

When I was importing, if a file is failed, then I got the windows message.
The message likes this ...bla bla bla.....etc. Do you want to proceed it?
Yes, No, Help. If I click yes, then the file is imported, but the fields that
are incorrect data type will be blank in MS Access Table (Temptable).

Thus, I will always get blank fields even all files are imported without any
error.

So what can I do with these two spreadsheets?

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)

I have too many fields. So I have to check for all fields (For example: A =
null, B = null…ect.)?


If rsR.RecordCount > 0 Then

How does program show files that are failed here?

'There's at least one bad record
'Do whatever's necessary
Else



:

It sounds as if you're importing to a temporary table, always a good
idea. One way of proceeding would be

1) Instead of using TransferSpreadsheet, create and execute an append
query that puts the records into the temporary table.

2) Run another query to check whether there are any records in the
temporary table that have null values where there should be actual
values. If so, count the file as "failed", store its name (and maybe the
primary keys of the guilty records), empty the temp table and go on to
the next file.

The query code might be something like this:

Dim myfile As String
Dim mypath As String
Dim strSQL As String
Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

mypath = "C:\your folder and path\"
myfile = Dir(mypath & "*.xls")
Set dbD = CurrentDB()

Do While Len(myfile) > 0
'Delete records from temp table
strSQL = "DELETE FROM TempTable;"
dbD.Execute strSQL, dbFailOnError

'Append records from worksheet
strSQL = "INSERT INTO TempTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=" & mypath & myfile _
& ";].[Sheet2$];"
dbD.Execute strSQL, dbFailOnError

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)
If rsR.RecordCount > 0 Then
'There's at least one bad record
'Do whatever's necessary
Else
'All is well
'Transfer records to main table
End If

rsR.Close
myfile = Dir()

Loop



DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

On Wed, 9 Mar 2005 10:23:02 -0800, "Minh Le"

Hi John Nurick,

I hope you or anyone can help. let me descibe what i did what messages i got

When importing, if a file is failed, then I got the windows message. The
message like this ...bla bla bla.. ...etc. Do you want to proceed it? Yes,
No, Help. If I click yes, then the file is imported, but the fields that are
incorrect data type will be blank in MS Access Table (Temptable). If I click
"NO", then file is not imported and it brings me to the windows with message
bla lba bla... End, Debug. If I click on Debug, then it brings me to the
Coding windows for debugging.


I have too many files to import. Thus, i have not checked for data
conversion (fields)in Excel. I just want to import all files at once. If file
is failed, then display a message with file name and an OK button. When
clicking on OK, then this file will not be imported and the process will go
ahead to import the other files. I can keep track with file(s) is (are)
failed, so i can open excel file(s) to check for error data.


Somthing like this:

If (DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath &
myfile) then

MsgBox "Transfer Failed: " & vbCrLf & "XLS: " & myfile
end if

End If


:

How do you define "failed to import"?

If this is to be a regular operation, it's preferable IMHO either to
ensure that the data is well-formed before you try to import it, or to
start by linking or importing it to a temporary table and then using an
append query to move the data to the main table, "cleaning" it en route.


On Tue, 8 Mar 2005 11:59:07 -0800, "Minh Le"

I would like to capture files that failed to import. If the question is
clear, then please ask.

It works fine. But i just want the program displays files that failed to
import. So i can open failing files to check for the errors


My code is


dim myfile
dim my path
mypath = "C:\your folder and path\"

myfile = dir(mypath & "*.xls")
while <> ""

DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

HERE: I need the code to capture files that failed. Please help.

myfile = dir

wend

 

[Guest]

Hi John Nurick,

I am sorry. I haven't have a chance to work on it. Thus, i haven't known how
it works. I have been busy doing something else. Thanks so much for your help.

Hi Minh Le,

Go to Tools|References and add a reference to the Microsoft DAO 3.6
Object Library.

Hi John Nurick,

When i have tried the code, but I got the message " User not defined" for
the following lines

Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

I don't know a way of trapping the message you get from
TransferSpreadsheet. What I'm suggesting is that you import the records
to the temp table regardless of whether they are good or bad, and then
decide by examining the records (i.e. using a sutable query) whether the
Excel file "passes" or "fails".

But from what you say, you're importing records with different
structures (some with 30 fields and some with 40) into the same table.
This doesn't make sense in normal database operations, unless the 30
fields in one also appear in the other, and the 10 extra fields can all
be Null.

I still feel that the best approach would be to write code to test each
Excel file before trying to import the data it contains.

On Wed, 9 Mar 2005 14:09:06 -0800, "Minh Le"

Hi John Nurick,

I will put all the code together to see how it works\

First of all, thanks for your answering

Second, I have two department spreadsheets. For Example, the first
department spreadsheet has 40 fields, and the second one has 30 fields. When
importing these two spreadsheets into TempTable, the one with fewer fields
(the second one) will be having blank fields (NULL) in TempTable. It is the
same with lines below

When I was importing, if a file is failed, then I got the windows message.
The message likes this ...bla bla bla.....etc. Do you want to proceed it?
Yes, No, Help. If I click yes, then the file is imported, but the fields that
are incorrect data type will be blank in MS Access Table (Temptable).

Thus, I will always get blank fields even all files are imported without any
error.

So what can I do with these two spreadsheets?

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)

I have too many fields. So I have to check for all fields (For example: A =
null, B = null…ect.)?


If rsR.RecordCount > 0 Then

How does program show files that are failed here?

'There's at least one bad record
'Do whatever's necessary
Else



:

It sounds as if you're importing to a temporary table, always a good
idea. One way of proceeding would be

1) Instead of using TransferSpreadsheet, create and execute an append
query that puts the records into the temporary table.

2) Run another query to check whether there are any records in the
temporary table that have null values where there should be actual
values. If so, count the file as "failed", store its name (and maybe the
primary keys of the guilty records), empty the temp table and go on to
the next file.

The query code might be something like this:

Dim myfile As String
Dim mypath As String
Dim strSQL As String
Dim dbD as DAO.Database
Dim rsR As DAO.Recordset

mypath = "C:\your folder and path\"
myfile = Dir(mypath & "*.xls")
Set dbD = CurrentDB()

Do While Len(myfile) > 0
'Delete records from temp table
strSQL = "DELETE FROM TempTable;"
dbD.Execute strSQL, dbFailOnError

'Append records from worksheet
strSQL = "INSERT INTO TempTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=" & mypath & myfile _
& ";].[Sheet2$];"
dbD.Execute strSQL, dbFailOnError

'Check for "bad" records
strSQL = "SELECT PrimaryKeyField FROM TempTable " _
& "WHERE FieldThatShouldn'tBeNull IS NULL;"
Set rsR = dbD.OpenRecordset(strSQL)
If rsR.RecordCount > 0 Then
'There's at least one bad record
'Do whatever's necessary
Else
'All is well
'Transfer records to main table
End If

rsR.Close
myfile = Dir()

Loop



DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

On Wed, 9 Mar 2005 10:23:02 -0800, "Minh Le"

Hi John Nurick,

I hope you or anyone can help. let me descibe what i did what messages i got

When importing, if a file is failed, then I got the windows message. The
message like this ...bla bla bla.. ...etc. Do you want to proceed it? Yes,
No, Help. If I click yes, then the file is imported, but the fields that are
incorrect data type will be blank in MS Access Table (Temptable). If I click
"NO", then file is not imported and it brings me to the windows with message
bla lba bla... End, Debug. If I click on Debug, then it brings me to the
Coding windows for debugging.


I have too many files to import. Thus, i have not checked for data
conversion (fields)in Excel. I just want to import all files at once. If file
is failed, then display a message with file name and an OK button. When
clicking on OK, then this file will not be imported and the process will go
ahead to import the other files. I can keep track with file(s) is (are)
failed, so i can open excel file(s) to check for error data.


Somthing like this:

If (DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath &
myfile) then

MsgBox "Transfer Failed: " & vbCrLf & "XLS: " & myfile
end if

End If


:

How do you define "failed to import"?

If this is to be a regular operation, it's preferable IMHO either to
ensure that the data is well-formed before you try to import it, or to
start by linking or importing it to a temporary table and then using an
append query to move the data to the main table, "cleaning" it en route.


On Tue, 8 Mar 2005 11:59:07 -0800, "Minh Le"

I would like to capture files that failed to import. If the question is
clear, then please ask.

It works fine. But i just want the program displays files that failed to
import. So i can open failing files to check for the errors


My code is


dim myfile
dim my path
mypath = "C:\your folder and path\"

myfile = dir(mypath & "*.xls")
while <> ""

DoCmd.TransferSpreadsheet acimport, 8, "your table name here", mypath & myfile

HERE: I need the code to capture files that failed. Please help.

myfile = dir

wend