By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,867 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

Importing excel into Access: Reports primary key violation when there is not!

P: n/a
Hello!
I am writing VBA code in Access 97 to import an Excel 2000
Spreadsheet. I had hoped to do this using ODBC, but that was not to
be, for who knows what reason.

The problem is that I import the excel file into a table in Access
that I name X. Then I run an append query to import the data into
another table.

I get an error message that says it "can't append all the records" due
to "validation rule violations"

There ARE NO validation rules! If I copy and paste these rows manully,
Access accepts it with no errors.

The rows in the excel file look perfectly okay.

Any ideas??!!
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"Validation Rule violations" include things like:
- field is marked Required, but the entry is blank;

- field is marded No for Allow Zero Length (as it should be), but the entry
is a zero-length string (as distinct from blank/null);

- field is a foreign key with referential integrity, and the value is not in
the lookup table

as well as the validation rule on the fields, and on the table.

--
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.

"Bonnie R" <bo****@aol.com> wrote in message
news:72*************************@posting.google.co m...

I am writing VBA code in Access 97 to import an Excel 2000
Spreadsheet. I had hoped to do this using ODBC, but that was not to
be, for who knows what reason.

The problem is that I import the excel file into a table in Access
that I name X. Then I run an append query to import the data into
another table.

I get an error message that says it "can't append all the records" due
to "validation rule violations"

There ARE NO validation rules! If I copy and paste these rows manully,
Access accepts it with no errors.

The rows in the excel file look perfectly okay.

Any ideas??!!

Nov 12 '05 #2

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
"Validation Rule violations" include things like:
- field is marked Required, but the entry is blank;

- field is marded No for Allow Zero Length (as it should be), but the entry
is a zero-length string (as distinct from blank/null);

- field is a foreign key with referential integrity, and the value is not in
the lookup table

as well as the validation rule on the fields, and on the table.

--
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.


===================
Thanks for your reply, but I certainly had checked all that out. THat
is why it is such a mystery and why I posted a question to the group.

The table will accept the data if I copy and paste the rows one by
one. It is the same data, but the "violation" doesn't occur.

Bonnie
Nov 13 '05 #3

P: n/a
Here is some code you could try (copy and paste directly into a Standard
code modue)that links your Excel sheet to Access and then reads the data
in the sheet with a recordset object variable. I think I got most of
this code from the Access97 Help Files on tabledefs connect property
(except for the $ which you have to append to the sheet name- which here
is "sheet1$" - they forgot to mention about the $). Note: if the
ConnectOutput... line errors out, just get rid of the underscores and
put everything on one line - I broke it up with the underscores so it
would fit on the page here.

Sub TestConnect()
Dim DB As Database
Set DB = CurrentDb
ConnectOutput DB, "ExcelTbl", _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\dir\Test1.xls", _
"Sheet1$" '--if this line errors out - get rid of "_"
End Sub 'and put it all on one line

Sub ConnectOutput(dbsTemp As Database, strTable As String, strConnect As
String, strSourceTable As String)
Dim tdfLinked As TableDef
Dim rstLinked As Recordset
Dim intTemp As Integer

' Create a new TableDef, set its Connect and
' SourceTableName properties based on the passed
' arguments, and append it to the TableDefs collection.
Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef(strTable)

tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked
Application.RefreshDatabaseWindow
Set rstLinked = dbsTemp.OpenRecordset(strTable)
' Display the first three records of the linked table.
intTemp = 1
With rstLinked
Do While Not .EOF And intTemp <= 3
Debug.Print , .Fields(0), .Fields(1)
intTemp = intTemp + 1
.MoveNext
Loop
.Close
End With

End Sub

Rich

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a
Bonnie R wrote:
Hello!
I am writing VBA code in Access 97 to import an Excel 2000
Spreadsheet. I had hoped to do this using ODBC, but that was not to
be, for who knows what reason.

The problem is that I import the excel file into a table in Access
that I name X. Then I run an append query to import the data into
another table.

I get an error message that says it "can't append all the records" due
to "validation rule violations"

There ARE NO validation rules! If I copy and paste these rows manully,
Access accepts it with no errors.

The rows in the excel file look perfectly okay.

Any ideas??!!


You didn't specify what type of data, but remember that numberical data
in excel defaults to "double" whilst access defaults to "long integer"
for numerical data. May not be your problem, but it's worth mentioning.

If you have decimals in your spreadsheet, make sure you set the
corresponding field to "double" in your access table.

Cheers
Chris

Nov 13 '05 #5

P: n/a
I hadn't checked back here for a while - I have been using my screwy
version with the unsolved data violation mystery.

Thanks for posting this code and I'll check it out. I do recall when I
was wrestling with the thing earlier that I connected in a similar way
using a Access module (Access 97) that I found that Access and/or
Excel would hang and in general behave quite badly. In fact, I got a
message box error message that I took a screen shot of, printed out,
and hung up on my cube wall for some small amusement value (it seemed
absurdly dire to me):

Automation error: Catastrophic Failure

Perhaps my problem was using Access97 and Excel 2000. I used Access97
due to the bug in Access2000 sendobject when sending emails.

So, thanks again and I'll experiment with your code.
Bonnie
Nov 13 '05 #6

P: n/a
I tried the code and, while I was easily able to connect to the excel
file and edit/query, etc., there was the old problem of the
misinterpretation of the data types - I had some fields that had dates
in them and in the linked table the values were represented as #Num!
Oh how I hate that.
Bonnie
Nov 13 '05 #7

P: n/a
bo****@aol.com (Bonnie R) wrote in message news:<72**************************@posting.google. com>...
I tried the code and, while I was easily able to connect to the excel
file and edit/query, etc., there was the old problem of the
misinterpretation of the data types - I had some fields that had dates
in them and in the linked table the values were represented as #Num!
Oh how I hate that.
Bonnie


What if you create an import specification and explicitly state that a
field is a date?
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.