Solved

Not able to insert data - access - sql - vba

Posted on 2014-04-28
4
565 Views
Last Modified: 2014-04-28
Hi Experts,
I am not able to run the insert query because of some errors, Here I am attaching the db with the table and and the query, Please kindly help,
Thanks
test.accdb
0
Comment
Question by:gtmathewDallas
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40027480
Please tell us what the errors are.  Not everyone can download your database.
0
 

Author Comment

by:gtmathewDallas
ID: 40027515
While I am running the insert query through vba, its not appending to the table and shows no errors, so i tested it directly with the query (which got from immediate window) I am getting a big message including the errors type conversion violation - key violation - lock violation - validation rule violation etc. I checked the properties of each fields, but it seems ok there.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40027662
The problem is that the primary key field is not being filled.  Either change it to be an autonumber so it will automatically generate a new number for each insert or add the ID field to your append query.

Comments -
Access generates "ID" as the PK name because it can't possibly guess at a meaningful name.  You should change it yourself.  for this table, InventoryID would be a good choice.

I would suggest filling "empty" fields with null rather than zero length strings.  It can be problematic to allow both therefore, I always set the AllowZeroLengthStrings property to no to avoid the problem.  Either a field has a value or it is Null.  ZLS just adds one more thing you need to test for in your queries.

Dates are delimited by # NOT '.
0
 

Author Comment

by:gtmathewDallas
ID: 40027695
Thanks a lot PatHartman, sorry to miss that, I created a new Id field and set that to auto number as you said, Its working fine now,
Thanks again
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now