Strange problem when importing excel data to Access table

[Guest]

Hi,

Sorry to post this in General as well as in Import/Export Data.

I know its most likely something I setup in access, but for the life of me,
I can't figure out what it is.

The problem is, when I import data from an excel file, instead of the data
going to row 2 in the data table, it jumps down to row 103. When I import
another excel file, the data is in row 205, it keep incrementing 102 rows.

The data being imported from excel is in column A to BL, Row 1 is the named
fields while row 2 is the data. The access table, the field names equal the
named fields from Excel. I do have an ID column with autonumber and set with
the primary key in the access table.

So if anyone out there that can point me in the right direction, I would
really appreciate it. It's probably something very simple, but
unfortunately, I'm very new to Access. Thank you

 

[Chris2]

Hi,

Sorry to post this in General as well as in Import/Export Data.

I know its most likely something I setup in access, but for the life of me,
I can't figure out what it is.

The problem is, when I import data from an excel file, instead of the data
going to row 2 in the data table, it jumps down to row 103. When I import
another excel file, the data is in row 205, it keep incrementing 102 rows.

The data being imported from excel is in column A to BL, Row 1 is the named
fields while row 2 is the data. The access table, the field names equal the
named fields from Excel. I do have an ID column with autonumber and set with
the primary key in the access table.

So if anyone out there that can point me in the right direction, I would
really appreciate it. It's probably something very simple, but
unfortunately, I'm very new to Access. Thank you

Hell-fire,

You are describing the normal behavior of MS Access.

When importing to a table with an Autonumber column, the Autonumber
column may have a gap in its range.

Every import may cause another gap to appear.

*Attempt the following on copies of your MS Excel and MS Access
databases.*

If you want your MS Excel data to have an absolute order when imported
into MS Access, insert a column into the spreadsheet. In the first
row, type 1. In the second row, type =SUM(A1 + 1). Copy that formula
all the way to the bottom row of the data (MS Excel should auto-update
the cell reference). This should produce a numbered column from 1 to
X, where X is the last row of your data.

When you import into MS Access, have the receiving table have a column
for that new column created above, and have it be the Primary Key of
the table.


Sincerely,

Chris O.

 

[Guest]

Hi Chris,

Thank you very much for answering my post. I do have another question
though. For the excel data that is being imported, it is only one row worth.
I'm just importing row 2 data that is from each report. Is there a way to
ensure the data being imported will go to the next row in the Access table?

Thank you

 

[Chris2]

Hi Chris,

Thank you very much for answering my post. I do have another question
though. For the excel data that is being imported, it is only one row worth.
I'm just importing row 2 data that is from each report. Is there a way to
ensure the data being imported will go to the next row in the Access table?

Thank you

Hell-fire,

I would like to give a flat no and say that autonumber columns do
whatever they want.

Frankly, I accept the limitation and never design anything that relies
on the values of this type of column.

Why? One, because that is the recommended practice. Two, because the
various work-around solutions are almost never portable between
database products (yes, that does mean something).

You may be able to adapt: http://allenbrowne.com/ser-26.html to your
purposes. (If you review this and find it incomprehensible, you can
try studying-up on VBA, or stick with "no" as the answer.)


Sincerely,

Chris O.

 

[Guest]

Hi Chris,

Thank you very much for your help. I will just have to muddle through it.
Unfortunately, its not my design for either the excel file or access. I was
tasked to work on this, even though I have very little knowledge of access.

Thank you again.

 

[david12]

Hi,

Sorry to post this in General as well as in Import/Export Data.

I know its most likely something I setup inaccess, but for the life of me,
I can't figure out what it is.

The problem is, when I import data from an excel file, instead of the data
going to row 2 in the data table, it jumps down to row 103. When I import
another excel file, the data is in row 205, it keep incrementing 102 rows.

The data being imported from excel is in column A to BL, Row 1 is the named
fields while row 2 is the data. Theaccesstable, the field names equal the
named fields from Excel. I do have an ID column with autonumber and set with
the primary key in theaccesstable.

So if anyone out there that can point me in the right direction, I would
really appreciate it. It's probably something very simple, but
unfortunately, I'm very new toAccess. Thank you

Sounds like you've got data of some kind in the Excel spreadsheet that
looks like blank rows.
Try making sure the spreadsheet contains only the rows you want
imported by either selecting all 100 blank rows below your data and
deleting them or copying the rows you want and pasting them to a new
sheet which becomes your import source.
Dave