Append to MS SQL 2005 table

I'm getting a key violation error whenever I try and append records from a 
table in Access to a table on a MS SQL 2005 database.  The SQL db's table has 
an IID field which is the primary key for the table (and the only indexed 
field in the table) and I am NOT trying to append records to the IID field 
with my INSERT INTO tImport(...) statement.  It is a clustered index if that 
makes any difference.

Since I can't seem to add the table and write to it without an index field, 
does anyone have any ideas why I am getting the error message
0
Utf
2/11/2008 4:29:03 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
633 Views

Similar Articles

[PageSpeed] 15

Is the IID field an Identity/Autonumber type? If not, you will need to 
generate some value that is unique for each of the records you want to append.
-- 
Duane Hookom
Microsoft Access MVP


"MChrist" wrote:

> I'm getting a key violation error whenever I try and append records from a 
> table in Access to a table on a MS SQL 2005 database.  The SQL db's table has 
> an IID field which is the primary key for the table (and the only indexed 
> field in the table) and I am NOT trying to append records to the IID field 
> with my INSERT INTO tImport(...) statement.  It is a clustered index if that 
> makes any difference.
> 
> Since I can't seem to add the table and write to it without an index field, 
> does anyone have any ideas why I am getting the error message
0
Utf
2/12/2008 2:39:02 AM
Duane,

As always, thanks for your response and your assistance.

In the many iterations before posting my question, I must of removed the 
IDENTITY statement from my CREATE TABLE SQL.  

Oops.  Thanks again for your response.

Mark

"Duane Hookom" wrote:

> Is the IID field an Identity/Autonumber type? If not, you will need to 
> generate some value that is unique for each of the records you want to append.
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "MChrist" wrote:
> 
> > I'm getting a key violation error whenever I try and append records from a 
> > table in Access to a table on a MS SQL 2005 database.  The SQL db's table has 
> > an IID field which is the primary key for the table (and the only indexed 
> > field in the table) and I am NOT trying to append records to the IID field 
> > with my INSERT INTO tImport(...) statement.  It is a clustered index if that 
> > makes any difference.
> > 
> > Since I can't seem to add the table and write to it without an index field, 
> > does anyone have any ideas why I am getting the error message
0
Utf
2/12/2008 3:26:00 PM
Reply:

Similar Artilces:

CreatePointFont creates font of different size in VS 2005 vs. VS 2
Hi, I 'm converting projects from VS 2003 to VS 2005. One suprising thing is a different visible font size on the screen (at some places in my apps) It seem that : font.CreatePointFont(80, "Arial"); now creates a slightly larger font (on a 96 dpi Monitor) then before (lfHeight=-11 vs. -10). Is this indented? BR, Uwe ...

Linq to sql question
Hi. I have some simple tables. - ER_Person table (Person_Id, LogonId, FirstName, LastName, EMail) - ER_Section table (Section_Id, SectionCode, SectionName) - ER_PersonSection table (Person_Id, Section_Id)- this table takes care of a many-to-many relationship between person and section. A person can be connected to several sections and this table makes that possible. I'm using vs2008 and the objectdesigner. In my application (Winforms) I retrieve a person: var person = ProjectGlobal.rd.ER_Persons.Single(c => c.LogonId == ALogonId.ToUpper()); This is retrieved by the us...

Finding SQL and connecing in SBS 08
I have SBS 08 network and each of my 4 branch offices has a server that is not running active directory, but each of those servers is running SQL Server 08 Express so that it can host a local only sql database for a job costing program. I get the error "can not find database or login permission is not correct" when the job costing program tries to look at the database. Is this an active directory problem or sql problem? The job costing software is trying to access the database using the same login as SA. Thanks, -- Clint Walker IT Manager Disco Try using sql...

custom SQL import
I wrote a custom SQL import process to load data into 3 GP 9.0 tables. They are sc020030, sc020130, and sc020230. The data is loaded in that order. It looks like the existing data after it is loaded. I can also see it through the GP front end. I noticed after I ran the import, however, that some field labels on the GP input screen are now red. I don't remember this being the case prior to the import. I can navigate through the imported data and make / save changes through the front end. The work I did is on a dev box. When I log into the production box, the text fields are...

convert lookup column from access to sql server express
I created a database and front end in access 2007. I am trying to migrate to sql server express 2005 with the same access front end. There is a lookup column in access 2007 database that I am going to replace with a lookup table and a relation table between the main table and the lookup table to hold the multiple links between them. I have a form that at the moment works well with the lookup column for obvious reasons (you get the list of available values along with the selected values for this particular record) I need to try to recreate this behavior somehow with the SQL ...

Messages sitting on my gmail account, still can't download anything (MS Outlook 2003)
Recently I started using my gmail account both at work and at home with MS Outlook 2003. Home Outlook is the primary one, so at WORK I checked the "leave a copy on the server" option, and -as expected- messages still sit on my gmail account as I checked when I came home. Now trying to download these messages at home Outlook, I do a send/receive, but nothing gets downloaded !@#$! even though they show up on gmail. Any ideas ?? Once read, i.e. downloaded by one pc, the other will see it "as read" and will not download it. If you look at gmail the message will not be in...

SQL report as dashboard to filter for Current user cannot user CRMAF_
First off I cannot use the default filters because the report is being generated for a dashboard and not a CRM Report, however it is being created via an SRS Report. I simply need a way to filter the report for the current user only, I found this query which gives the system user but this will not match up to the Account owner because of its format. select fullname from FilteredSystemUser where domainname = SUSER_SNAME() Gives: CRM Admin I need, Admin, CRM ...

Pivot Table
When I save a Pivot Table with interactivity as a web page the groups are lost. Is there a way to include the groups? ...

combine two tables in a query
i have 2 queries that share the fields "who" "when" and "amount". One reports the value of daily labour (time out - time in * hourly wage) for the employee (who). While the other shows the amount paid on a particular date. When I create a query that includes these 2 queries and links the "who" field between them I get a result that shows 5664 records where are only 260 when you add the record count from the 2 queries that I'm using here. I'd like to have a query that would report the amount owed out at any given time. Just as an additio...

Pivot Table in Excel not reading Access Query
I use Access link to a SQL server database and then run queries in Access. I wanted to do a Pivot Table in Excel because of other users able in the office. I can't get Excel to import or even show one of the queries in Access where I use MonthName to get the month's name and not a number. If I use the month's number, all is well. It is not a big deal just wondering why it won't work. Is there a way to change the number to a name in Excel's Pivot Table as I can' get =Text(cell,"MMMM") to work in the calculated field. Thanks for any help. Lee ...

Appending tables using VBA
Hello, Say for example I have two huge tables I want to append together, out of the 200 fields, 190 of them have exactly the same name and will be mapped to exactly the same place, but the rest 10 do not have the same name although they do have a corresponding mapping to the target table (say field: "Last Name" mapped to field name "LName" and so on), now, one way to write the SQL string is of course to map the field one by one, but is there anyway I could just write out the mapping of the 10 fields that are different and save the work to write out the mapping of the rest 1...

Merge multiple records in the same table in access
Hello I am dealing with some unfriendly import files which import as: timestamp position name 001 2 Jon 001 3 Bob 001 1 Ann 001 4 Mike 002 1 Joe 002 2 Sue 003 1 Jeff 004 5 James 004 1 Andy 004 2 Beth 004 4 Mitch 004 3 Chris And would like to create a new table that displays thusly: timestamp position1 ...

Save as Picture DISTORTS FONTS
I use Pub 2002 to create compositions that I save as .jpg's for my website, and email headers. The compositions consist of "Digital images (.tiff) + WordArt + Shapes (with fills) + Text Blocks". I group them together, or do a Select All then use "Save as Picture" to save the composition as a .jpg . When I reopen the saved .jpg I find that the text that was added as a Text Block becomes fuzzy / blurry. The images, WordArt, and Shapes all remain clear. It doesn't matter what program I open the .jpg with the Text that was added as a Text Block is re...

MS' .DLL Fiasco
There are updates today for the MS .dll hacking problem for almost everything. I got the following programs needing the update: Firefox Seamonkey Thunderbird Skype Chrome There may be more programs needing the update, so check them. -- Alias "Alias" <aka@masked&anonymous.com.�nvalido> wrote in message news:i68dqs$1rk$2@news.eternal-september.org... > There are updates today for the MS .dll hacking problem for almost > everything. I got the following programs needing the update: > > Firefox > Seamonkey > Thunderbird > Skype...

MS Office 2003 wants a product key after installed for 3 years???
I have MS Office for Students and Teachers 2003. The first computer it was installed on was stolen however I re-installed it to my newer one and it has been fine for the past 3 years when I installed it to the newer PC. In Jan 2010. Word and Powerpoint won't open, the windows installer comes up and then it asks for my Product Key, which I have but it won't recognize. However Excel still opens and runs fine. Why would only some components of this package function?? And why is it asking for my product key now? I thought this software was a one-shot deal, meaning you b...

GP and ms crm integrations
I have seen some references to GP and CRM having some “out of the box” integration. I have worked with Scribe and I know there is a new tool but I am interested in the out of the box integrations. Is there a link or doc that I can read? Thank you Check links below: http://www.erpsoftwareblog.com/2009/11/highlights-of-the-new-microsoft-dynamics-crm-4-0-adapter-for-microsoft-dynamics-gp-10-0/ http://www.rosebizinc.com/gpblog/2009/10/lessons-learned-crm-40-adapter-for-gp.html Regards, -- Mohammad R. Daoud MVP, MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great ...

Any updates on 2005 online services??????
Has anybody got any update on when, and if, Money 2005 will be able to use the online services with yodlee? I stil get the "unavailable error" Thanks DS I spoke with tech support today and they said "within the next two days" We'll see... -Bruce "DS" <anonymous@discussions.microsoft.com> wrote in message news:0d5301c4a585$a8cc6f20$a601280a@phx.gbl... > Has anybody got any update on when, and if, Money 2005 > will be able to use the online services with yodlee? I > stil get the "unavailable error" > > Thanks > DS ...

what happened to the table controls
Just upgraded from Publisher 2000 to 2003 ... what happened to the control "buttons" (the small tabs on the outside) on tables that would allow you to select a row, column or the entire table? Place your cursor close to the row or column you want to highlight, an arrow will appear. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Rob Odum" <RobOdum@discussions.microsoft.com> wrote in message news:4387D516-05DE-4ECE-A158-EAC92CA31877@microsoft.com... > Just upgraded from Publisher 2000 to 2003 ... what h...

Pivot tables and drop-down lists
I want to create a pivot table that summarizes expences in different categories. To do this I use a drop-down list containing these categories, something like this: category expence amount fun beer 10 fun trip to the movies 20 insurance life insurance 40 ... In the case of creating a pivot table summarizing over the categories how do i include unused categories? Thank you ! -- lorentzen ------------------------------------------------------------------------ lorentzen's Profile: http://www.excelforum.com/member...

Printing Deposits
Posting again with my email address. There is a print option on the deposit transaction entry, but I can't find the way to actually print them. Can anyone help? ...

SQL & Exchange Server
could i install and use both SQL server and exchange server in one clinet ? In a client? What do you mean? If you're asking whether you can run SQL and Exchange on the same *server*, yes, you can install them, but this is not recommended.... shapoor wrote: > could i install and use both SQL server and exchange > server in one clinet ? They will run togeather. "shapoor" <shapoor_zarei@yahoo.com> wrote in message news:13c8201c3f8b8$b5cbd050$a301280a@phx.gbl... > could i install and use both SQL server and exchange > server in one clinet ? Theoritically ...

want 2 place a drop down window in word table
Want to add a drop down window into a word table. Can not find any useful tool on Office to perform this task. What will this "window" do? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "G" <G@discussions.microsoft.com> wrote in message news:7250C4AC-29AE-4B08-9425-8F8D03CB1BAE@microsoft.com... > Want to add a drop down window into a word table. Can not find any useful > tool on Office to perform this task. > ...

How to stop pivot table crashing in Excel 2007
In a large spreadsheet with several tabs I’m having a problem with excel crashing while making a pivot table after a specific series of events happen. On the main data tab I have both several columns populated by data from an Access query and several columns populated by assorted formulas (date 1st of month & vlookup). If I do this excel crashes: 1) Start a pivot table & chart from main data tab by clicking the icon from the toolbar 2) Put the chart on a separate tab from the pivot table 3) Try to drag & drop any field that is populated by a formula I can drag around other f...

Reports Created in MS Excel
Hello All, We're just looking into options for creating report output for our application. We've used Crystal before and found limitations, so have taken to writing reports direclty into MS Excel, (add in DLL, and create sheet and data via direct code) and it appears to be working just fine so far. I was just curious if anyone else has taken this approach and if it's worked well for them. Thanks & all the best, LR On Nov 25, 2:54=A0am, "LR" <tinma...@hotmail.com> wrote: > Hello All, > =A0 We're just looking into options fo...

SVC00106 table errors with vendor combiner
Does anyone know what table SVC00106 is for and how it is populated? We are on Dynamics 10 and use RMA and RTV, but not service. When I try to combine vendors using the professional services tool it gives me a duplicate key error referencing this table. It appears to be the existence of the line it doesn't like, not the information the line contains. This table contains vendors that were added through sometime in 2006, nothing more recent. If I combine two vendors and at least one of them was added after 2006 I received no errors. Can this table be deleted? Since it hasn'...