append query to ms sql server help

[patrichek]

hi,
I am in the process of creating an append query that sends the fields of an open form to a table in ms sql 2000. I've successfully linked the tables and created the query to pull data from my form but when i run the append i get a key violation error and nothing gets appended. I know which field is the primary key in ms sql i just don't know how to change my access table to suit, any ideas?

To start I've made my append query simple and it just pulls the contact name. Here's my sql statement if that helps.

INSERT INTO dbo_PatientMaster ( PatientFirstName, PatientLastName, PatientAccountNumber )
SELECT [Forms]![Contacts]![FirstName] AS Firstname, [Forms]![Contacts]![LastName] AS [Last], [Contacts]![PatientAccountNumber] AS [Patient Account Number]
FROM Contacts, dbo_PatientMaster
GROUP BY [Forms]![Contacts]![FirstName], [Forms]![Contacts]![LastName], [Contacts]![PatientAccountNumber];

In access the patient account number is empty and I just created that field in my access table today.

thanks in advance!

 

[dhookom]

Try remove dbo_PatientMaster from the "FROM" clause. It shouldn't be there unless you want to append one record for each record in dbo_PatientMaster and Contacts multiplied.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HubbleO]

I'm having the same exact problem as the original poster but the answer given doesn't apply to my case. Here is the SQL I'm using in Access to try to append to an SQL Server table.

INSERT INTO dbo_FRCBILLD ( Bill_ID, TrackingID, ChargeType, ChargeAmount )
SELECT tblBillDetail.Bill_ID, tblEnroll.TrackingID, tblBillDetail.ChargeType, tblBillDetail.ChargeAmount
FROM tblBillDetail LEFT JOIN tblEnroll ON tblBillDetail.Enroll_ID = tblEnroll.Enroll_ID;

There is a field in the SQL Server table that is an autonumber field and I am not trying to insert anything into it. Also, the Bill_ID field and the autonumbered field are both KEY field in the dbo table in Access. The Bill_ID field however is not indexed.

I assumed the SQL Server table would populate the autonumbered field with the next autonumber but I am getting the key violation error.

 

[patrichek]

Hi, the reply above didn't work for me either. I have yet to figure this out.

 

[stix4t2]

Are you using autonumbering in the SQL Server table and then trying to put a value into it? If you set the identity for the sql server table, then let it populate the value. If you want to use the Access autonumber then turn the identity off in sql server. You can't set values to autonumber/identity fields.

 

[dhookom]

patrichek,
I don't understand why you are attempting to append values from a form rather than a table/query.

You should be able to view the datasheet of your sql to see the values that would be inserted. Then open the target table and attempt to type in the values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

And what about the following syntax ?
INSERT INTO ... VALUES (...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[patrichek]

Dhookum,

I have a database of contacts that we use for marketing purposes, we also have a program that we schedule appts., surgeries and do billing from that runs on ms sql. I didn't build the ms sql tables or interface. So i have to do some reverse engineering in order to get what i want. speaking of, what we want is the ability to input the lead into the marketing db then once an appointment is scheduled be able to send the contact info to the ms sql database.
The easiest way i could think of for the user would be from a form. I have tried append using tables as well just to try to make it work, no luck...

If you guys have any ideas please feel free to share them!! I'd really like to cut down on double data entry.

There are 3 fields in the ms sql tables that give me problems.

1. and Identity key (PatientIDENTITY)
2. an autonumber key that is a P/K (PatientAccountNumber)
3. another number field that cannot contain a null


If there is anymore i can offer please let me know and i'll post it.

P

 

[stix4t2]

Can you supply some value for the other number that can't be null. And if pulling off the field, wouldn't the PatientAccountNumber be passed as a string.

 

[dhookom]

Can you manually type values into the SQL Server table? Did you try the suggestion from PH?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[patrichek]

i can manually type in the values, in fact i made a temp table in access and with the fields i wanted to append in the sql table and tried it that way, if i type in the values i'm able to append the data.

I'll try PHV's suggestion asap.

Thanks