CODEWITHSUNDEEP
sqlsql-serverms-access
Stormer
Stormer

Reputation: 35

Appending Data to SQL Server from Access Query Results in Error

I am appending data from an Access query into an existing table in SQL Server (2019) and sometimes NULL values cause a "Record is deleted" msgbox (no error number).

For instance, I have 3 columns (Text1, Text2, Text3) all are nvarchar(255) and Text1 accepts NULL values but sometimes Text2 doesn't... they are literally the same field with the same data. There is absolutely nothing different with the columns in SQL Server nor the fields in the query. This shouldn't be happening.

The other thing is that I made a make-table query off of the query and using that new table instead of the query caused no problems at all! Why is this? and how do I get the query to append data consistently?

I have tried append queries as well as straight up SQL in a DoCmd.RunSQL

The SqlSRV table is connected via custom ODBC string in Linked Table Manager.

From a query; this gives errors:

INSERT INTO tmakContact ( DataAsOf, ContactId, FullName, LoanNum, LoanId, Name, JobTitle, Email, Relationship, Company, Address, CityStateZip, [Number], PhoneNumType )
                   SELECT DataAsOf, ContactId, FullName, LoanNum, LoanId, Name, JobTitle, Email, Relationship, Company, Address, CityStateZip, [Number], PhoneNumType
FROM qryContact;

When I take out "Relationship" and "PhoneNumType" fields, the INSERT from this query works fine. These two fields come from outer joined tables. These tables are from another SQL Server and database I link to from within Access via custom ODBC string in Linked Table Manager.

From a table which I made in a make table query from qryContact gives no errors!

INSERT INTO tmakContact ( DataAsOf, ContactId, FullName, LoanNum, LoanId, Name, JobTitle, Email, Relationship, Company, Address, CityStateZip, [Number], PhoneNumType )
                   SELECT DataAsOf, ContactId, FullName, LoanNum, LoanId, Name, JobTitle, Email, Relationship, Company, Address, CityStateZip, [Number], PhoneNumType
FROM tmptblContact;

Originally I just ran DoCmd.OpenQuery "apdContact" which doesn’t work, which is just a saved append query using the same code as above.

SQL for qryContact:

SELECT Now() AS DataAsOf, dbo_Contact.Id AS ContactId, dbo_UserInfo.FullName, dbo_Loaninfo.LoanNum, dbo_ContactLoanLink.LoanId, dbo_Contact.Name, dbo_Contact.JobTitle, dbo_Email.Addr AS Email, Trim([dbo_ContactRelationship]![Descr]) AS Relationship, dbo_Contact.Company, [Add1] & " " & [Add2] AS Address, StrConv([City],3) & ", " & [StateCode] & " " & [Zip] AS CityStateZip, qryPhone.Number, qryPhone.PhoneNumType
FROM ((((dbo_Loaninfo INNER JOIN ((dbo_ContactLoanLink INNER JOIN dbo_Contact ON dbo_ContactLoanLink.ContactId = dbo_Contact.Id) LEFT JOIN dbo_Address ON dbo_Contact.BusAddrAId = dbo_Address.Id) ON dbo_Loaninfo.Id = dbo_ContactLoanLink.LoanId) LEFT JOIN dbo_ContactRelationship ON dbo_ContactLoanLink.ContactRelationshipId = dbo_ContactRelationship.Id) LEFT JOIN dbo_Email ON dbo_Contact.Id = dbo_Email.ContactId) LEFT JOIN qryPhone ON dbo_Contact.Id = qryPhone.ContactId) LEFT JOIN dbo_UserInfo ON dbo_Loaninfo.AssignedUserId = dbo_UserInfo.Id
WHERE (((dbo_Contact.InactiveFlag)="N") AND ((dbo_Loaninfo.LoanStatusId)<>1105) AND ((dbo_Loaninfo.InactiveFlag)="N") AND ((dbo_Loaninfo.PaidOffFlag)="N"));

Upvotes: 0

Views: 187

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

Hum, does the table in question have any true/false columns - even if not used in your query? (a bit column in the table?).

Double, triple check that the target table in SQL server (no doublt a linked table to Access) has any bit columns, and if yes, MAKE SURE the column has a deafult value (0), for false.

Next up: You don't mention if the target table in question has a autonumber PK column (I suspect it must have - but do check, and make sure that such a table has a PK).

next up: Are their any real, or single/double columns in that target table - Again EVEN IF THEY ARE NOT part of your query, make sure such columns have a default setting in sql server (0).

last up: Add a row version column to the sql server target table. That so called "row version" column in sql is named timestamp. (this is the worlds WORST name, since that timestamp column has ZERO to do with "time" or date or whatever. it is a ACTUAL row version system, and access supports this feature.

It also means that access will not do a column by column compare to the record when doing updates, or inserts. So, try adding a timestamp (aka: row version) column to the target table, and re-link from access.

Upvotes: 1

Related Questions