Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

TransferSpreadSheet doesn't work properly after conversion to 2002

Discussion in 'Information Technology' started by Pradeep, Jul 28, 2006.

  1. Pradeep

    Pradeep
    Expand Collapse
    Guest

    I have an access database in 97. I have recently converted that to Access
    2002 with Access 2002 file format. After doing that I am having problems with
    the TransferSpreadsheet functionality which we use to Import data from an
    Excel worksheet. At times it imports certain rows and leaves the remaining.
    Some times it imports a few rows which are some thousands of rows below
    ignoring the first ones. The same spreadsheet, I checked with Access 97 and
    everything works fine and Imports the entire data. I am not able to find out
    the cause for this strange behaviour.

    Please Suggest.

    Thanks,

    Pradeep
     
  2. Loading...


  3. Jeff C

    Jeff C
    Expand Collapse
    Guest

    You should look vary closely at your import specification to see if there any
    changes
    --
    Jeff C
    Live Well .. Be Happy In All You Do


    "Pradeep" wrote:

    > I have an access database in 97. I have recently converted that to Access
    > 2002 with Access 2002 file format. After doing that I am having problems with
    > the TransferSpreadsheet functionality which we use to Import data from an
    > Excel worksheet. At times it imports certain rows and leaves the remaining.
    > Some times it imports a few rows which are some thousands of rows below
    > ignoring the first ones. The same spreadsheet, I checked with Access 97 and
    > everything works fine and Imports the entire data. I am not able to find out
    > the cause for this strange behaviour.
    >
    > Please Suggest.
    >
    > Thanks,
    >
    > Pradeep
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Do you get any kind of error message regarding the rows that cannot be
    imported? (You may need to turn SetWarnings on to get this message.)

    Or does Access create a table with a name such as ImportErrors?

    What data type are these fields that fail the import. If you create a table
    with the same names, but all fields of type Text, does the import complete
    successfully? (JET 4 is less capable of sorting out some of these issues
    than JET 3.5 was.)

    Are you using an import spec?

    Presumably the file name is not the problem, since you are getting some
    results. (Since A97 SR2, only registered file types work for the import.)

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    news:EDB82851-A5A6-4CA3-B4BE-B65566EF7B78@microsoft.com...
    >I have an access database in 97. I have recently converted that to Access
    > 2002 with Access 2002 file format. After doing that I am having problems
    > with
    > the TransferSpreadsheet functionality which we use to Import data from an
    > Excel worksheet. At times it imports certain rows and leaves the
    > remaining.
    > Some times it imports a few rows which are some thousands of rows below
    > ignoring the first ones. The same spreadsheet, I checked with Access 97
    > and
    > everything works fine and Imports the entire data. I am not able to find
    > out
    > the cause for this strange behaviour.
    >
    > Please Suggest.
    >
    > Thanks,
    >
    > Pradeep
     
  5. Pradeep

    Pradeep
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    it gives a message saying that 0 rows could not be imported etc. It is a huge
    message. I can understand when it creates a table with Import Errors. I dont
    specify any Import Specification as such and just use the TransferSpreadSheet
    statement and with the has column headers true because of which the first row
    is Ignored.

    I will try to check if Import Specification has got something to do with this.

    Thanks,

    Pradeep

    "Allen Browne" wrote:

    > Do you get any kind of error message regarding the rows that cannot be
    > imported? (You may need to turn SetWarnings on to get this message.)
    >
    > Or does Access create a table with a name such as ImportErrors?
    >
    > What data type are these fields that fail the import. If you create a table
    > with the same names, but all fields of type Text, does the import complete
    > successfully? (JET 4 is less capable of sorting out some of these issues
    > than JET 3.5 was.)
    >
    > Are you using an import spec?
    >
    > Presumably the file name is not the problem, since you are getting some
    > results. (Since A97 SR2, only registered file types work for the import.)
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > news:EDB82851-A5A6-4CA3-B4BE-B65566EF7B78@microsoft.com...
    > >I have an access database in 97. I have recently converted that to Access
    > > 2002 with Access 2002 file format. After doing that I am having problems
    > > with
    > > the TransferSpreadsheet functionality which we use to Import data from an
    > > Excel worksheet. At times it imports certain rows and leaves the
    > > remaining.
    > > Some times it imports a few rows which are some thousands of rows below
    > > ignoring the first ones. The same spreadsheet, I checked with Access 97
    > > and
    > > everything works fine and Imports the entire data. I am not able to find
    > > out
    > > the cause for this strange behaviour.
    > >
    > > Please Suggest.
    > >
    > > Thanks,
    > >
    > > Pradeep

    >
    >
    >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    That "huge message" contains the answer to your question.

    Read the message to understand exactly what is the reason the data cannot be
    imported.

    An import spec may or may not solve the problem, depending on what the
    problem actually is.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    news:79722283-4018-45F1-8C32-C1091B543C9E@microsoft.com...
    > it gives a message saying that 0 rows could not be imported etc. It is a
    > huge
    > message. I can understand when it creates a table with Import Errors. I
    > dont
    > specify any Import Specification as such and just use the
    > TransferSpreadSheet
    > statement and with the has column headers true because of which the first
    > row
    > is Ignored.
    >
    > I will try to check if Import Specification has got something to do with
    > this.
    >
    > Thanks,
    >
    > Pradeep
    >
    > "Allen Browne" wrote:
    >
    >> Do you get any kind of error message regarding the rows that cannot be
    >> imported? (You may need to turn SetWarnings on to get this message.)
    >>
    >> Or does Access create a table with a name such as ImportErrors?
    >>
    >> What data type are these fields that fail the import. If you create a
    >> table
    >> with the same names, but all fields of type Text, does the import
    >> complete
    >> successfully? (JET 4 is less capable of sorting out some of these issues
    >> than JET 3.5 was.)
    >>
    >> Are you using an import spec?
    >>
    >> Presumably the file name is not the problem, since you are getting some
    >> results. (Since A97 SR2, only registered file types work for the import.)
    >>
    >> "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    >> news:EDB82851-A5A6-4CA3-B4BE-B65566EF7B78@microsoft.com...
    >> >I have an access database in 97. I have recently converted that to
    >> >Access
    >> > 2002 with Access 2002 file format. After doing that I am having
    >> > problems
    >> > with
    >> > the TransferSpreadsheet functionality which we use to Import data from
    >> > an
    >> > Excel worksheet. At times it imports certain rows and leaves the
    >> > remaining.
    >> > Some times it imports a few rows which are some thousands of rows below
    >> > ignoring the first ones. The same spreadsheet, I checked with Access 97
    >> > and
    >> > everything works fine and Imports the entire data. I am not able to
    >> > find
    >> > out
    >> > the cause for this strange behaviour.
     
  7. Pradeep

    Pradeep
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    Hello Allen,

    I did go through the message and it did not give me any idea as to what was
    the actual problem. It says:

    "XYZ Database was unable to append all the data to the table

    The contents of fields in 0 record(s) and 0 record(s) were lost dues to key
    violations.

    * If data was deleted, the data that you pasted or imported does not match
    the field data types of the field size property in the destination table.

    * If records were lost, either the records you pasted contain primary key
    values that already exist in the destination table, or they violate
    referential integrity rules for a relationship defined between tables.

    Do you want to proceed any way? "

    I checked the table design to see if there are any changes but it is the
    same when compared to the access 97 one.

    I imported the data into a new table(using get External data) and all the
    rows were imported from the Excel file. I checked the design and it is the
    same as the one that I am trying to bring data into.

    Please suggest.

    Thanks,

    Pradeep

    "Allen Browne" wrote:

    > That "huge message" contains the answer to your question.
    >
    > Read the message to understand exactly what is the reason the data cannot be
    > imported.
    >
    > An import spec may or may not solve the problem, depending on what the
    > problem actually is.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > news:79722283-4018-45F1-8C32-C1091B543C9E@microsoft.com...
    > > it gives a message saying that 0 rows could not be imported etc. It is a
    > > huge
    > > message. I can understand when it creates a table with Import Errors. I
    > > dont
    > > specify any Import Specification as such and just use the
    > > TransferSpreadSheet
    > > statement and with the has column headers true because of which the first
    > > row
    > > is Ignored.
    > >
    > > I will try to check if Import Specification has got something to do with
    > > this.
    > >
    > > Thanks,
    > >
    > > Pradeep
    > >
    > > "Allen Browne" wrote:
    > >
    > >> Do you get any kind of error message regarding the rows that cannot be
    > >> imported? (You may need to turn SetWarnings on to get this message.)
    > >>
    > >> Or does Access create a table with a name such as ImportErrors?
    > >>
    > >> What data type are these fields that fail the import. If you create a
    > >> table
    > >> with the same names, but all fields of type Text, does the import
    > >> complete
    > >> successfully? (JET 4 is less capable of sorting out some of these issues
    > >> than JET 3.5 was.)
    > >>
    > >> Are you using an import spec?
    > >>
    > >> Presumably the file name is not the problem, since you are getting some
    > >> results. (Since A97 SR2, only registered file types work for the import.)
    > >>
    > >> "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > >> news:EDB82851-A5A6-4CA3-B4BE-B65566EF7B78@microsoft.com...
    > >> >I have an access database in 97. I have recently converted that to
    > >> >Access
    > >> > 2002 with Access 2002 file format. After doing that I am having
    > >> > problems
    > >> > with
    > >> > the TransferSpreadsheet functionality which we use to Import data from
    > >> > an
    > >> > Excel worksheet. At times it imports certain rows and leaves the
    > >> > remaining.
    > >> > Some times it imports a few rows which are some thousands of rows below
    > >> > ignoring the first ones. The same spreadsheet, I checked with Access 97
    > >> > and
    > >> > everything works fine and Imports the entire data. I am not able to
    > >> > find
    > >> > out
    > >> > the cause for this strange behaviour.

    >
    >
    >
     
  8. Pradeep

    Pradeep
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    It Gives the same message if it try to import into an existing table.
    Interestingly the data types for the new table and the existing table is
    still the same (design is 100% identical in terms of field size, datatypes
    etc).

    Thanks,

    Pradeep

    "Pradeep" wrote:

    > Hello Allen,
    >
    > I did go through the message and it did not give me any idea as to what was
    > the actual problem. It says:
    >
    > "XYZ Database was unable to append all the data to the table
    >
    > The contents of fields in 0 record(s) and 0 record(s) were lost dues to key
    > violations.
    >
    > * If data was deleted, the data that you pasted or imported does not match
    > the field data types of the field size property in the destination table.
    >
    > * If records were lost, either the records you pasted contain primary key
    > values that already exist in the destination table, or they violate
    > referential integrity rules for a relationship defined between tables.
    >
    > Do you want to proceed any way? "
    >
    > I checked the table design to see if there are any changes but it is the
    > same when compared to the access 97 one.
    >
    > I imported the data into a new table(using get External data) and all the
    > rows were imported from the Excel file. I checked the design and it is the
    > same as the one that I am trying to bring data into.
    >
    > Please suggest.
    >
    > Thanks,
    >
    > Pradeep
    >
    > "Allen Browne" wrote:
    >
    > > That "huge message" contains the answer to your question.
    > >
    > > Read the message to understand exactly what is the reason the data cannot be
    > > imported.
    > >
    > > An import spec may or may not solve the problem, depending on what the
    > > problem actually is.
    > >
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > > Reply to group, rather than allenbrowne at mvps dot org.
    > >
    > > "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > > news:79722283-4018-45F1-8C32-C1091B543C9E@microsoft.com...
    > > > it gives a message saying that 0 rows could not be imported etc. It is a
    > > > huge
    > > > message. I can understand when it creates a table with Import Errors. I
    > > > dont
    > > > specify any Import Specification as such and just use the
    > > > TransferSpreadSheet
    > > > statement and with the has column headers true because of which the first
    > > > row
    > > > is Ignored.
    > > >
    > > > I will try to check if Import Specification has got something to do with
    > > > this.
    > > >
    > > > Thanks,
    > > >
    > > > Pradeep
    > > >
    > > > "Allen Browne" wrote:
    > > >
    > > >> Do you get any kind of error message regarding the rows that cannot be
    > > >> imported? (You may need to turn SetWarnings on to get this message.)
    > > >>
    > > >> Or does Access create a table with a name such as ImportErrors?
    > > >>
    > > >> What data type are these fields that fail the import. If you create a
    > > >> table
    > > >> with the same names, but all fields of type Text, does the import
    > > >> complete
    > > >> successfully? (JET 4 is less capable of sorting out some of these issues
    > > >> than JET 3.5 was.)
    > > >>
    > > >> Are you using an import spec?
    > > >>
    > > >> Presumably the file name is not the problem, since you are getting some
    > > >> results. (Since A97 SR2, only registered file types work for the import.)
    > > >>
    > > >> "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > > >> news:EDB82851-A5A6-4CA3-B4BE-B65566EF7B78@microsoft.com...
    > > >> >I have an access database in 97. I have recently converted that to
    > > >> >Access
    > > >> > 2002 with Access 2002 file format. After doing that I am having
    > > >> > problems
    > > >> > with
    > > >> > the TransferSpreadsheet functionality which we use to Import data from
    > > >> > an
    > > >> > Excel worksheet. At times it imports certain rows and leaves the
    > > >> > remaining.
    > > >> > Some times it imports a few rows which are some thousands of rows below
    > > >> > ignoring the first ones. The same spreadsheet, I checked with Access 97
    > > >> > and
    > > >> > everything works fine and Imports the entire data. I am not able to
    > > >> > find
    > > >> > out
    > > >> > the cause for this strange behaviour.

    > >
    > >
    > >
     
  9. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    What I usually do is to import into a temp table that has all the fields of
    type Text.

    Then create an Update query that coerces the data into the correct data
    type, and appends to the true table, once you have establshed that
    everything is okay. See if that strategy works for you.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    news:49622381-B965-46A4-A6DA-87EABCA4BAF9@microsoft.com...
    > It Gives the same message if it try to import into an existing table.
    > Interestingly the data types for the new table and the existing table is
    > still the same (design is 100% identical in terms of field size, datatypes
    > etc).
    >
    > Thanks,
    >
    > Pradeep
    >
    > "Pradeep" wrote:
    >
    >> Hello Allen,
    >>
    >> I did go through the message and it did not give me any idea as to what
    >> was
    >> the actual problem. It says:
    >>
    >> "XYZ Database was unable to append all the data to the table
    >>
    >> The contents of fields in 0 record(s) and 0 record(s) were lost dues to
    >> key
    >> violations.
    >>
    >> * If data was deleted, the data that you pasted or imported does not
    >> match
    >> the field data types of the field size property in the destination table.
    >>
    >> * If records were lost, either the records you pasted contain primary key
    >> values that already exist in the destination table, or they violate
    >> referential integrity rules for a relationship defined between tables.
    >>
    >> Do you want to proceed any way? "
    >>
    >> I checked the table design to see if there are any changes but it is the
    >> same when compared to the access 97 one.
    >>
    >> I imported the data into a new table(using get External data) and all the
    >> rows were imported from the Excel file. I checked the design and it is
    >> the
    >> same as the one that I am trying to bring data into.
    >>
    >> Please suggest.
    >>
    >> Thanks,
    >>
    >> Pradeep
    >>
    >> "Allen Browne" wrote:
    >>
    >> > That "huge message" contains the answer to your question.
    >> >
    >> > Read the message to understand exactly what is the reason the data
    >> > cannot be
    >> > imported.
    >> >
    >> > An import spec may or may not solve the problem, depending on what the
    >> > problem actually is.
    >> >
    >> > "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    >> > news:79722283-4018-45F1-8C32-C1091B543C9E@microsoft.com...
    >> > > it gives a message saying that 0 rows could not be imported etc. It
    >> > > is a
    >> > > huge
    >> > > message. I can understand when it creates a table with Import Errors.
    >> > > I
    >> > > dont
    >> > > specify any Import Specification as such and just use the
    >> > > TransferSpreadSheet
    >> > > statement and with the has column headers true because of which the
    >> > > first
    >> > > row
    >> > > is Ignored.
    >> > >
    >> > > I will try to check if Import Specification has got something to do
    >> > > with
    >> > > this.
    >> > >
    >> > > Thanks,
    >> > >
    >> > > Pradeep
    >> > >
    >> > > "Allen Browne" wrote:
    >> > >
    >> > >> Do you get any kind of error message regarding the rows that cannot
    >> > >> be
    >> > >> imported? (You may need to turn SetWarnings on to get this message.)
    >> > >>
    >> > >> Or does Access create a table with a name such as ImportErrors?
    >> > >>
    >> > >> What data type are these fields that fail the import. If you create
    >> > >> a
    >> > >> table
    >> > >> with the same names, but all fields of type Text, does the import
    >> > >> complete
    >> > >> successfully? (JET 4 is less capable of sorting out some of these
    >> > >> issues
    >> > >> than JET 3.5 was.)
    >> > >>
    >> > >> Are you using an import spec?
    >> > >>
    >> > >> Presumably the file name is not the problem, since you are getting
    >> > >> some
    >> > >> results. (Since A97 SR2, only registered file types work for the
    >> > >> import.)
    >> > >>
    >> > >> "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    >> > >> news:EDB82851-A5A6-4CA3-B4BE-B65566EF7B78@microsoft.com...
    >> > >> >I have an access database in 97. I have recently converted that to
    >> > >> >Access
    >> > >> > 2002 with Access 2002 file format. After doing that I am having
    >> > >> > problems
    >> > >> > with
    >> > >> > the TransferSpreadsheet functionality which we use to Import data
    >> > >> > from
    >> > >> > an
    >> > >> > Excel worksheet. At times it imports certain rows and leaves the
    >> > >> > remaining.
    >> > >> > Some times it imports a few rows which are some thousands of rows
    >> > >> > below
    >> > >> > ignoring the first ones. The same spreadsheet, I checked with
    >> > >> > Access 97
    >> > >> > and
    >> > >> > everything works fine and Imports the entire data. I am not able
    >> > >> > to
    >> > >> > find
    >> > >> > out
    >> > >> > the cause for this strange behaviour.
     
  10. Pradeep

    Pradeep
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    Hello Allen,

    I was trying different things and was playing around with the
    SpreadSheetType parameter. The parameter was set to 5 when access 97 with
    which Access 97 was importing data perfectly. But Access 2002 was not
    accepting it. To test the same I started giving more than 5 but it did not
    accept that too. To try the other way, I used acSpreadsheetTypeExcel3 and it
    started working perfectly. The entire data in the Excel sheet was imported
    and it was faster too. I have been checking to see I will be having any
    issues but everything seems to look good.

    I appreciate your patience and you have been very helpful.

    Thanks,

    Pradeep

    "Allen Browne" wrote:

    > What I usually do is to import into a temp table that has all the fields of
    > type Text.
    >
    > Then create an Update query that coerces the data into the correct data
    > type, and appends to the true table, once you have establshed that
    > everything is okay. See if that strategy works for you.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > news:49622381-B965-46A4-A6DA-87EABCA4BAF9@microsoft.com...
    > > It Gives the same message if it try to import into an existing table.
    > > Interestingly the data types for the new table and the existing table is
    > > still the same (design is 100% identical in terms of field size, datatypes
    > > etc).
    > >
    > > Thanks,
    > >
    > > Pradeep
    > >
    > > "Pradeep" wrote:
    > >
    > >> Hello Allen,
    > >>
    > >> I did go through the message and it did not give me any idea as to what
    > >> was
    > >> the actual problem. It says:
    > >>
    > >> "XYZ Database was unable to append all the data to the table
    > >>
    > >> The contents of fields in 0 record(s) and 0 record(s) were lost dues to
    > >> key
    > >> violations.
    > >>
    > >> * If data was deleted, the data that you pasted or imported does not
    > >> match
    > >> the field data types of the field size property in the destination table.
    > >>
    > >> * If records were lost, either the records you pasted contain primary key
    > >> values that already exist in the destination table, or they violate
    > >> referential integrity rules for a relationship defined between tables.
    > >>
    > >> Do you want to proceed any way? "
    > >>
    > >> I checked the table design to see if there are any changes but it is the
    > >> same when compared to the access 97 one.
    > >>
    > >> I imported the data into a new table(using get External data) and all the
    > >> rows were imported from the Excel file. I checked the design and it is
    > >> the
    > >> same as the one that I am trying to bring data into.
    > >>
    > >> Please suggest.
    > >>
    > >> Thanks,
    > >>
    > >> Pradeep
    > >>
    > >> "Allen Browne" wrote:
    > >>
    > >> > That "huge message" contains the answer to your question.
    > >> >
    > >> > Read the message to understand exactly what is the reason the data
    > >> > cannot be
    > >> > imported.
    > >> >
    > >> > An import spec may or may not solve the problem, depending on what the
    > >> > problem actually is.
    > >> >
    > >> > "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > >> > news:79722283-4018-45F1-8C32-C1091B543C9E@microsoft.com...
    > >> > > it gives a message saying that 0 rows could not be imported etc. It
    > >> > > is a
    > >> > > huge
    > >> > > message. I can understand when it creates a table with Import Errors.
    > >> > > I
    > >> > > dont
    > >> > > specify any Import Specification as such and just use the
    > >> > > TransferSpreadSheet
    > >> > > statement and with the has column headers true because of which the
    > >> > > first
    > >> > > row
    > >> > > is Ignored.
    > >> > >
    > >> > > I will try to check if Import Specification has got something to do
    > >> > > with
    > >> > > this.
    > >> > >
    > >> > > Thanks,
    > >> > >
    > >> > > Pradeep
    > >> > >
    > >> > > "Allen Browne" wrote:
    > >> > >
    > >> > >> Do you get any kind of error message regarding the rows that cannot
    > >> > >> be
    > >> > >> imported? (You may need to turn SetWarnings on to get this message.)
    > >> > >>
    > >> > >> Or does Access create a table with a name such as ImportErrors?
    > >> > >>
    > >> > >> What data type are these fields that fail the import. If you create
    > >> > >> a
    > >> > >> table
    > >> > >> with the same names, but all fields of type Text, does the import
    > >> > >> complete
    > >> > >> successfully? (JET 4 is less capable of sorting out some of these
    > >> > >> issues
    > >> > >> than JET 3.5 was.)
    > >> > >>
    > >> > >> Are you using an import spec?
    > >> > >>
    > >> > >> Presumably the file name is not the problem, since you are getting
    > >> > >> some
    > >> > >> results. (Since A97 SR2, only registered file types work for the
    > >> > >> import.)
    > >> > >>
    > >> > >> "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > >> > >> news:EDB82851-A5A6-4CA3-B4BE-B65566EF7B78@microsoft.com...
    > >> > >> >I have an access database in 97. I have recently converted that to
    > >> > >> >Access
    > >> > >> > 2002 with Access 2002 file format. After doing that I am having
    > >> > >> > problems
    > >> > >> > with
    > >> > >> > the TransferSpreadsheet functionality which we use to Import data
    > >> > >> > from
    > >> > >> > an
    > >> > >> > Excel worksheet. At times it imports certain rows and leaves the
    > >> > >> > remaining.
    > >> > >> > Some times it imports a few rows which are some thousands of rows
    > >> > >> > below
    > >> > >> > ignoring the first ones. The same spreadsheet, I checked with
    > >> > >> > Access 97
    > >> > >> > and
    > >> > >> > everything works fine and Imports the entire data. I am not able
    > >> > >> > to
    > >> > >> > find
    > >> > >> > out
    > >> > >> > the cause for this strange behaviour.

    >
    >
    >
     
  11. Pradeep

    Pradeep
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    Allen,

    I would appreciate if you can throw some light on the reason for this
    strange behaviour.

    Thanks,

    Pradeep

    "Pradeep" wrote:

    > Hello Allen,
    >
    > I was trying different things and was playing around with the
    > SpreadSheetType parameter. The parameter was set to 5 when access 97 with
    > which Access 97 was importing data perfectly. But Access 2002 was not
    > accepting it. To test the same I started giving more than 5 but it did not
    > accept that too. To try the other way, I used acSpreadsheetTypeExcel3 and it
    > started working perfectly. The entire data in the Excel sheet was imported
    > and it was faster too. I have been checking to see I will be having any
    > issues but everything seems to look good.
    >
    > I appreciate your patience and you have been very helpful.
    >
    > Thanks,
    >
    > Pradeep
    >
    > "Allen Browne" wrote:
    >
    > > What I usually do is to import into a temp table that has all the fields of
    > > type Text.
    > >
    > > Then create an Update query that coerces the data into the correct data
    > > type, and appends to the true table, once you have establshed that
    > > everything is okay. See if that strategy works for you.
    > >
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > > Reply to group, rather than allenbrowne at mvps dot org.
    > >
    > > "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > > news:49622381-B965-46A4-A6DA-87EABCA4BAF9@microsoft.com...
    > > > It Gives the same message if it try to import into an existing table.
    > > > Interestingly the data types for the new table and the existing table is
    > > > still the same (design is 100% identical in terms of field size, datatypes
    > > > etc).
    > > >
    > > > Thanks,
    > > >
    > > > Pradeep
    > > >
    > > > "Pradeep" wrote:
    > > >
    > > >> Hello Allen,
    > > >>
    > > >> I did go through the message and it did not give me any idea as to what
    > > >> was
    > > >> the actual problem. It says:
    > > >>
    > > >> "XYZ Database was unable to append all the data to the table
    > > >>
    > > >> The contents of fields in 0 record(s) and 0 record(s) were lost dues to
    > > >> key
    > > >> violations.
    > > >>
    > > >> * If data was deleted, the data that you pasted or imported does not
    > > >> match
    > > >> the field data types of the field size property in the destination table.
    > > >>
    > > >> * If records were lost, either the records you pasted contain primary key
    > > >> values that already exist in the destination table, or they violate
    > > >> referential integrity rules for a relationship defined between tables.
    > > >>
    > > >> Do you want to proceed any way? "
    > > >>
    > > >> I checked the table design to see if there are any changes but it is the
    > > >> same when compared to the access 97 one.
    > > >>
    > > >> I imported the data into a new table(using get External data) and all the
    > > >> rows were imported from the Excel file. I checked the design and it is
    > > >> the
    > > >> same as the one that I am trying to bring data into.
    > > >>
    > > >> Please suggest.
    > > >>
    > > >> Thanks,
    > > >>
    > > >> Pradeep
    > > >>
    > > >> "Allen Browne" wrote:
    > > >>
    > > >> > That "huge message" contains the answer to your question.
    > > >> >
    > > >> > Read the message to understand exactly what is the reason the data
    > > >> > cannot be
    > > >> > imported.
    > > >> >
    > > >> > An import spec may or may not solve the problem, depending on what the
    > > >> > problem actually is.
    > > >> >
    > > >> > "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > > >> > news:79722283-4018-45F1-8C32-C1091B543C9E@microsoft.com...
    > > >> > > it gives a message saying that 0 rows could not be imported etc. It
    > > >> > > is a
    > > >> > > huge
    > > >> > > message. I can understand when it creates a table with Import Errors.
    > > >> > > I
    > > >> > > dont
    > > >> > > specify any Import Specification as such and just use the
    > > >> > > TransferSpreadSheet
    > > >> > > statement and with the has column headers true because of which the
    > > >> > > first
    > > >> > > row
    > > >> > > is Ignored.
    > > >> > >
    > > >> > > I will try to check if Import Specification has got something to do
    > > >> > > with
    > > >> > > this.
    > > >> > >
    > > >> > > Thanks,
    > > >> > >
    > > >> > > Pradeep
    > > >> > >
    > > >> > > "Allen Browne" wrote:
    > > >> > >
    > > >> > >> Do you get any kind of error message regarding the rows that cannot
    > > >> > >> be
    > > >> > >> imported? (You may need to turn SetWarnings on to get this message.)
    > > >> > >>
    > > >> > >> Or does Access create a table with a name such as ImportErrors?
    > > >> > >>
    > > >> > >> What data type are these fields that fail the import. If you create
    > > >> > >> a
    > > >> > >> table
    > > >> > >> with the same names, but all fields of type Text, does the import
    > > >> > >> complete
    > > >> > >> successfully? (JET 4 is less capable of sorting out some of these
    > > >> > >> issues
    > > >> > >> than JET 3.5 was.)
    > > >> > >>
    > > >> > >> Are you using an import spec?
    > > >> > >>
    > > >> > >> Presumably the file name is not the problem, since you are getting
    > > >> > >> some
    > > >> > >> results. (Since A97 SR2, only registered file types work for the
    > > >> > >> import.)
    > > >> > >>
    > > >> > >> "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    > > >> > >> news:EDB82851-A5A6-4CA3-B4BE-B65566EF7B78@microsoft.com...
    > > >> > >> >I have an access database in 97. I have recently converted that to
    > > >> > >> >Access
    > > >> > >> > 2002 with Access 2002 file format. After doing that I am having
    > > >> > >> > problems
    > > >> > >> > with
    > > >> > >> > the TransferSpreadsheet functionality which we use to Import data
    > > >> > >> > from
    > > >> > >> > an
    > > >> > >> > Excel worksheet. At times it imports certain rows and leaves the
    > > >> > >> > remaining.
    > > >> > >> > Some times it imports a few rows which are some thousands of rows
    > > >> > >> > below
    > > >> > >> > ignoring the first ones. The same spreadsheet, I checked with
    > > >> > >> > Access 97
    > > >> > >> > and
    > > >> > >> > everything works fine and Imports the entire data. I am not able
    > > >> > >> > to
    > > >> > >> > find
    > > >> > >> > out
    > > >> > >> > the cause for this strange behaviour.

    > >
    > >
    > >
     
  12. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    Probably another case of a program being too smart for our own good?

    The excel spreadsheet type is going to tell Access which internal
    conversion routine to use. Not all spreadsheets are equal.

    We had a situation where we were downloading a document and the web
    page gave us the choice of text or spreadsheet. We said spreadsheet,
    and told our access system to import the spreadsheet. Everything worked
    fine for months.
    Then they made some "enhancements" to the webpage. On the surface
    everything looked the same but suddenly we could no longer import the
    data. We tried all of the different import options but still nothing
    would work. We ended up having to use automation to open the downloaded
    "spreadsheet" with excell and then turn around and save it and then go
    through with the import process. With that extra step everything worked
    fine, AGAIN.

    All this to simply say that Access 97 had a different internal
    conversion routine for spreadsheets than Access 2002 resulting in the
    degraded import process until you, by changing the excell spreadsheet
    type, hit upon an import conversion process that would handle the data.
     
  13. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Re: TransferSpreadSheet doesn't work properly after conversion to

    No idea.

    Perhaps someone else can explain why the older Excel version would be
    interpreted better than the more recent one.

    What I can say is that Microsoft is aware that the import from Excel has
    been less than ideal in the last couple of versions, and they are working
    hard at making it better in A2007. While I have not tested it yet, I expect
    that this work will be beneficial for us all.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
    news:048E4A81-711E-49C3-955F-E704770E0731@microsoft.com...
    > Allen,
    >
    > I would appreciate if you can throw some light on the reason for this
    > strange behaviour.
    >
    > Thanks,
    >
    > Pradeep
    >
    > "Pradeep" wrote:
    >
    >> Hello Allen,
    >>
    >> I was trying different things and was playing around with the
    >> SpreadSheetType parameter. The parameter was set to 5 when access 97 with
    >> which Access 97 was importing data perfectly. But Access 2002 was not
    >> accepting it. To test the same I started giving more than 5 but it did
    >> not
    >> accept that too. To try the other way, I used acSpreadsheetTypeExcel3 and
    >> it
    >> started working perfectly. The entire data in the Excel sheet was
    >> imported
    >> and it was faster too. I have been checking to see I will be having any
    >> issues but everything seems to look good.
    >>
    >> I appreciate your patience and you have been very helpful.
    >>
    >> Thanks,
    >>
    >> Pradeep
     

Share This Page