Importing Data

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello,

I am trying to import data into an existing database. The data comes from
an excel file. Specifically, what I am trying to do is use the "order
entry" template and I want to import my own products (about 6,000) into the
products table. I have tried all kinds of sencerios, I've tried adding
fields to both the excel speadsheet as well as the table. I continue to get
an error, not allowing me to import. On the form, when I type in the drop
down, I want to use a UPC number and auto fill the rest. First challenge
though, is the import. Any help would be appreciated. (I use Access 2002).

thanks for any help you can give me.


Mike


ps. fields I need to import, if this is important, are:

Stock Number
UPC Number
Title
Publisher
Unit Price
 
Mike,
What method are you trying to use to import the spreadsheet and what kind of
errors are you getting. Please be specific, because there are multiple ways
to import from Excel. Each has its own problems, but any will work if done
correctly.
 
I am using the "File, Get External Data, Import" wizard. I select
theexisting table (Products) I want to import into and when I hit the finish
button, a error comes up not allowing me to import. Could it be because of
relationships in the existing table? Could it be because the existing table
field of "ProductID is an autonumber? Thanks forany help you can gve me.

Mike
 
The autonumber field could be a problem. Any fields that are used for
foreign keys may be a problem in that relationships could be lost. Try
creating a temporary table that has the same structure as the table you are
trying to import into to see if you still get the error. Also, what,
exactly, is the error you are getting?
 
Here is what I've done...

1. I imported into a new table and called it "Products1."
2. I then deleted the relationship of the table "Products" and then deleted
the "Products" table.
3. I renamed the "Products1" table to "Products" and reestablished the
relationship of "Products" to "OrderDetails."

When I go to enter an order, the dropdown menu will give me a list of the
products (I need to ISBN/UPC number), when I select the product, the debug
box appears. It says the "Value entered isn't valid for this field." When
I say "Debug" the following message appears: "Me![UnitPrice] =
Me![ProductID].Column(2)".

That's where I am in the process. Thanks for any help you can give me.

Mike
 
Interesting in that text boxes don't care about data types unless there is an
input mask in UnitPrice that doesn't like what is coming from the combo box.
What about the data types in the control sources, are they the same?

Me![UnitPrice] = Me![ProductID].Column(2)

Mike said:
Here is what I've done...

1. I imported into a new table and called it "Products1."
2. I then deleted the relationship of the table "Products" and then deleted
the "Products" table.
3. I renamed the "Products1" table to "Products" and reestablished the
relationship of "Products" to "OrderDetails."

When I go to enter an order, the dropdown menu will give me a list of the
products (I need to ISBN/UPC number), when I select the product, the debug
box appears. It says the "Value entered isn't valid for this field." When
I say "Debug" the following message appears: "Me![UnitPrice] =
Me![ProductID].Column(2)".

That's where I am in the process. Thanks for any help you can give me.

Mike

Klatuu said:
The autonumber field could be a problem. Any fields that are used for
foreign keys may be a problem in that relationships could be lost. Try
creating a temporary table that has the same structure as the table you
are
trying to import into to see if you still get the error. Also, what,
exactly, is the error you are getting?
Click to expand...
Click to expand...
 
the original fields in the original "Products" table were:
1. ProductID - Autonumber
2. Productname - Text (50 char.)
3. UnitPrice - Currency

My new table is:
1. ProductID - Autonumber
2. Publisher - Text (255 Char.)
3. Productname - Text (255 Char.)
4. StockNumber - Text (this is because the number could have a letter in
it).
5. ISBN/UPC - Text (this too is becasuse there could be a letter in it).
6. UnitPrice - Currency

It has been awhile since I've built a database. I kinda forgot what an
input mask is. Sorry.

Mike

Klatuu said:
Interesting in that text boxes don't care about data types unless there is
an
input mask in UnitPrice that doesn't like what is coming from the combo
box.
What about the data types in the control sources, are they the same?

Me![UnitPrice] = Me![ProductID].Column(2)

Mike said:
Here is what I've done...

1. I imported into a new table and called it "Products1."
2. I then deleted the relationship of the table "Products" and then
deleted
the "Products" table.
3. I renamed the "Products1" table to "Products" and reestablished the
relationship of "Products" to "OrderDetails."

When I go to enter an order, the dropdown menu will give me a list of the
products (I need to ISBN/UPC number), when I select the product, the
debug
box appears. It says the "Value entered isn't valid for this field."
When
I say "Debug" the following message appears: "Me![UnitPrice] =
Me![ProductID].Column(2)".

That's where I am in the process. Thanks for any help you can give me.

Mike

Klatuu said:
The autonumber field could be a problem. Any fields that are used for
foreign keys may be a problem in that relationships could be lost. Try
creating a temporary table that has the same structure as the table you
are
trying to import into to see if you still get the error. Also, what,
exactly, is the error you are getting?

:

I am using the "File, Get External Data, Import" wizard. I select
theexisting table (Products) I want to import into and when I hit the
finish
button, a error comes up not allowing me to import. Could it be
because
of
relationships in the existing table? Could it be because the existing
table
field of "ProductID is an autonumber? Thanks forany help you can gve
me.

Mike

Mike,
What method are you trying to use to import the spreadsheet and what
kind
of
errors are you getting. Please be specific, because there are
multiple
ways
to import from Excel. Each has its own problems, but any will work
if
done
correctly.

:

Hello,

I am trying to import data into an existing database. The data
comes
from
an excel file. Specifically, what I am trying to do is use the
"order
entry" template and I want to import my own products (about 6,000)
into
the
products table. I have tried all kinds of sencerios, I've tried
adding
fields to both the excel speadsheet as well as the table. I
continue
to
get
an error, not allowing me to import. On the form, when I type in
the
drop
down, I want to use a UPC number and auto fill the rest. First
challenge
though, is the import. Any help would be appreciated. (I use
Access
2002).

thanks for any help you can give me.


Mike


ps. fields I need to import, if this is important, are:

Stock Number
UPC Number
Title
Publisher
Unit Price
Click to expand...
Click to expand...
Click to expand...
 
An Input Mask is a property of a text box. It defines what kind and format
of data will be accepted. I don't think this is the problem if you are not
familiar with it, you probably did not put one in there. If this is an
inherited database, then you should check it.
Now, the question is what kind of data is in column(2) of your combo box's
record source? Be aware also, that combo box column numbering is zero based,
which means that .column(2) is actually the 3rd column, (0) is the first.

Mike said:
the original fields in the original "Products" table were:
1. ProductID - Autonumber
2. Productname - Text (50 char.)
3. UnitPrice - Currency

My new table is:
1. ProductID - Autonumber
2. Publisher - Text (255 Char.)
3. Productname - Text (255 Char.)
4. StockNumber - Text (this is because the number could have a letter in
it).
5. ISBN/UPC - Text (this too is becasuse there could be a letter in it).
6. UnitPrice - Currency

It has been awhile since I've built a database. I kinda forgot what an
input mask is. Sorry.

Mike

Klatuu said:
Interesting in that text boxes don't care about data types unless there is
an
input mask in UnitPrice that doesn't like what is coming from the combo
box.
What about the data types in the control sources, are they the same?

Me![UnitPrice] = Me![ProductID].Column(2)

Mike said:
Here is what I've done...

1. I imported into a new table and called it "Products1."
2. I then deleted the relationship of the table "Products" and then
deleted
the "Products" table.
3. I renamed the "Products1" table to "Products" and reestablished the
relationship of "Products" to "OrderDetails."

When I go to enter an order, the dropdown menu will give me a list of the
products (I need to ISBN/UPC number), when I select the product, the
debug
box appears. It says the "Value entered isn't valid for this field."
When
I say "Debug" the following message appears: "Me![UnitPrice] =
Me![ProductID].Column(2)".

That's where I am in the process. Thanks for any help you can give me.

Mike

The autonumber field could be a problem. Any fields that are used for
foreign keys may be a problem in that relationships could be lost. Try
creating a temporary table that has the same structure as the table you
are
trying to import into to see if you still get the error. Also, what,
exactly, is the error you are getting?

:

I am using the "File, Get External Data, Import" wizard. I select
theexisting table (Products) I want to import into and when I hit the
finish
button, a error comes up not allowing me to import. Could it be
because
of
relationships in the existing table? Could it be because the existing
table
field of "ProductID is an autonumber? Thanks forany help you can gve
me.

Mike

Mike,
What method are you trying to use to import the spreadsheet and what
kind
of
errors are you getting. Please be specific, because there are
multiple
ways
to import from Excel. Each has its own problems, but any will work
if
done
correctly.

:

Hello,

I am trying to import data into an existing database. The data
comes
from
an excel file. Specifically, what I am trying to do is use the
"order
entry" template and I want to import my own products (about 6,000)
into
the
products table. I have tried all kinds of sencerios, I've tried
adding
fields to both the excel speadsheet as well as the table. I
continue
to
get
an error, not allowing me to import. On the form, when I type in
the
drop
down, I want to use a UPC number and auto fill the rest. First
challenge
though, is the import. Any help would be appreciated. (I use
Access
2002).

thanks for any help you can give me.


Mike


ps. fields I need to import, if this is important, are:

Stock Number
UPC Number
Title
Publisher
Unit Price
Click to expand...
Click to expand...
Click to expand...
 
I will go ahead and play with this over the next couple of days (as time
permits) and get back to you.

Thanks,

Mike

Klatuu said:
An Input Mask is a property of a text box. It defines what kind and
format
of data will be accepted. I don't think this is the problem if you are
not
familiar with it, you probably did not put one in there. If this is an
inherited database, then you should check it.
Now, the question is what kind of data is in column(2) of your combo box's
record source? Be aware also, that combo box column numbering is zero
based,
which means that .column(2) is actually the 3rd column, (0) is the first.

Mike said:
the original fields in the original "Products" table were:
1. ProductID - Autonumber
2. Productname - Text (50 char.)
3. UnitPrice - Currency

My new table is:
1. ProductID - Autonumber
2. Publisher - Text (255 Char.)
3. Productname - Text (255 Char.)
4. StockNumber - Text (this is because the number could have a letter in
it).
5. ISBN/UPC - Text (this too is becasuse there could be a letter in it).
6. UnitPrice - Currency

It has been awhile since I've built a database. I kinda forgot what an
input mask is. Sorry.

Mike

Klatuu said:
Interesting in that text boxes don't care about data types unless there
is
an
input mask in UnitPrice that doesn't like what is coming from the combo
box.
What about the data types in the control sources, are they the same?

Me![UnitPrice] = Me![ProductID].Column(2)

:

Here is what I've done...

1. I imported into a new table and called it "Products1."
2. I then deleted the relationship of the table "Products" and then
deleted
the "Products" table.
3. I renamed the "Products1" table to "Products" and reestablished
the
relationship of "Products" to "OrderDetails."

When I go to enter an order, the dropdown menu will give me a list of
the
products (I need to ISBN/UPC number), when I select the product, the
debug
box appears. It says the "Value entered isn't valid for this field."
When
I say "Debug" the following message appears: "Me![UnitPrice] =
Me![ProductID].Column(2)".

That's where I am in the process. Thanks for any help you can give
me.

Mike

The autonumber field could be a problem. Any fields that are used
for
foreign keys may be a problem in that relationships could be lost.
Try
creating a temporary table that has the same structure as the table
you
are
trying to import into to see if you still get the error. Also,
what,
exactly, is the error you are getting?

:

I am using the "File, Get External Data, Import" wizard. I select
theexisting table (Products) I want to import into and when I hit
the
finish
button, a error comes up not allowing me to import. Could it be
because
of
relationships in the existing table? Could it be because the
existing
table
field of "ProductID is an autonumber? Thanks forany help you can
gve
me.

Mike

Mike,
What method are you trying to use to import the spreadsheet and
what
kind
of
errors are you getting. Please be specific, because there are
multiple
ways
to import from Excel. Each has its own problems, but any will
work
if
done
correctly.

:

Hello,

I am trying to import data into an existing database. The data
comes
from
an excel file. Specifically, what I am trying to do is use the
"order
entry" template and I want to import my own products (about
6,000)
into
the
products table. I have tried all kinds of sencerios, I've tried
adding
fields to both the excel speadsheet as well as the table. I
continue
to
get
an error, not allowing me to import. On the form, when I type
in
the
drop
down, I want to use a UPC number and auto fill the rest. First
challenge
though, is the import. Any help would be appreciated. (I use
Access
2002).

thanks for any help you can give me.


Mike


ps. fields I need to import, if this is important, are:

Stock Number
UPC Number
Title
Publisher
Unit Price
Click to expand...
Click to expand...
Click to expand...
 
Good luck

Mike said:
I will go ahead and play with this over the next couple of days (as time
permits) and get back to you.

Thanks,

Mike

Klatuu said:
An Input Mask is a property of a text box. It defines what kind and
format
of data will be accepted. I don't think this is the problem if you are
not
familiar with it, you probably did not put one in there. If this is an
inherited database, then you should check it.
Now, the question is what kind of data is in column(2) of your combo box's
record source? Be aware also, that combo box column numbering is zero
based,
which means that .column(2) is actually the 3rd column, (0) is the first.

Mike said:
the original fields in the original "Products" table were:
1. ProductID - Autonumber
2. Productname - Text (50 char.)
3. UnitPrice - Currency

My new table is:
1. ProductID - Autonumber
2. Publisher - Text (255 Char.)
3. Productname - Text (255 Char.)
4. StockNumber - Text (this is because the number could have a letter in
it).
5. ISBN/UPC - Text (this too is becasuse there could be a letter in it).
6. UnitPrice - Currency

It has been awhile since I've built a database. I kinda forgot what an
input mask is. Sorry.

Mike

Interesting in that text boxes don't care about data types unless there
is
an
input mask in UnitPrice that doesn't like what is coming from the combo
box.
What about the data types in the control sources, are they the same?

Me![UnitPrice] = Me![ProductID].Column(2)

:

Here is what I've done...

1. I imported into a new table and called it "Products1."
2. I then deleted the relationship of the table "Products" and then
deleted
the "Products" table.
3. I renamed the "Products1" table to "Products" and reestablished
the
relationship of "Products" to "OrderDetails."

When I go to enter an order, the dropdown menu will give me a list of
the
products (I need to ISBN/UPC number), when I select the product, the
debug
box appears. It says the "Value entered isn't valid for this field."
When
I say "Debug" the following message appears: "Me![UnitPrice] =
Me![ProductID].Column(2)".

That's where I am in the process. Thanks for any help you can give
me.

Mike

The autonumber field could be a problem. Any fields that are used
for
foreign keys may be a problem in that relationships could be lost.
Try
creating a temporary table that has the same structure as the table
you
are
trying to import into to see if you still get the error. Also,
what,
exactly, is the error you are getting?

:

I am using the "File, Get External Data, Import" wizard. I select
theexisting table (Products) I want to import into and when I hit
the
finish
button, a error comes up not allowing me to import. Could it be
because
of
relationships in the existing table? Could it be because the
existing
table
field of "ProductID is an autonumber? Thanks forany help you can
gve
me.

Mike

Mike,
What method are you trying to use to import the spreadsheet and
what
kind
of
errors are you getting. Please be specific, because there are
multiple
ways
to import from Excel. Each has its own problems, but any will
work
if
done
correctly.

:

Hello,

I am trying to import data into an existing database. The data
comes
from
an excel file. Specifically, what I am trying to do is use the
"order
entry" template and I want to import my own products (about
6,000)
into
the
products table. I have tried all kinds of sencerios, I've tried
adding
fields to both the excel speadsheet as well as the table. I
continue
to
get
an error, not allowing me to import. On the form, when I type
in
the
drop
down, I want to use a UPC number and auto fill the rest. First
challenge
though, is the import. Any help would be appreciated. (I use
Access
2002).

thanks for any help you can give me.


Mike


ps. fields I need to import, if this is important, are:

Stock Number
UPC Number
Title
Publisher
Unit Price
Click to expand...
Click to expand...
Click to expand...
 
Back
Top