Append Import Issue - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Append Import Issue

Ashley Draper

4 years ago

Hi all - this is my first post, and sorry in advance if this presents as a "rookie" question.

(It's not my intention to break any forum rules - but I am a rookie :))

Anyway, I'm in a position where (to use a old saying) I'm forced to "put the cart before the horse". What I mean is I'm trying to create a simple client/job tracking database using one of Richard's examples as a starting point, however it needs to be available to multiple, geographically-dispersed clients IN A HURRY !!!

(At this point, I've completed his "Beginner" level lessons)

So to that end, I've created an Azure SQL database instance through the client's 365 subscription (at the lowest level cost-wise for now), split the (incomplete) database, run the ODBC SQL driver ("msodbcsql.msi"), set the Azure firewall rules for the server and created the ACCDE file for distribution.

I can connect to the database (once I input SQL credentials), and run queries and reports happily (and I've tested it with at least one additional external instance for confirmation of sharing), but I cannot seem to append any records to the "ClientT" table from a csv file (using the Import Wizard, with exactly matching fields) or via an append query.

I can however, import records if the "ClientT" table is local?

The exact error message is:

Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to NULL due to a type conversion failure, and it didn't add 55 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway?
(More lines after that........)

Am I missing something (or more than 1 thing !) where SQL Server is involved as the backend of the split database?

Please note that I am doing this stuff backwards out of necessity, not preference.

Thank you in advance,

Cheers,

Ashley

Scott Axton @Reply

4 years ago

Ashley
I don't use SQL server so can't help you much there.
When you did the import did Access create a Import Errors table for you?

The error is telling me that you are trying to double up some records that have your primary key doubled up in some way.  In order to correct that issue you have to figure out which records those are.

You say that you can import the ClientT if it is local.  Do you have the same validations and rules in the local table as in your server?

Instead of importing the CSV - can you import from the local ClientT into the server ClientT?

Sorry I don't have a magic bullet for you.  Just trying to help you trouble shoot.

	Ashley Draper OP @Reply 4 years ago
Thanks Scott. I've worked it out (sort of). Turns out the Azure SQL back-end adds an identifying field to the table, which is set as a "Binary" type, and unique. That field is not present in a local table, hence the ability to append without errors. (Wish I'd copied the exact field name :() I'll add to this question in the future if you're interested (as I go along)? Ashley

	Scott Axton @Reply 4 years ago
Wouldn't hurt Ashley. Might help someone else in the future.

Richard Rost @Reply

4 years ago

Yeah there's a lot of little changes you have to make with SQL Server. Access yes/no fields are changed to bit values. You have to use single quotes in your SQL statements. Things like that. I cover a lot of this stuff in my Access SQL Server Online seminar.

Honestly, Ashley, if you're a beginner and you have to have this database "available to multiple, geographically-dispersed clients IN A HURRY" then I would highly recommend Access Database Cloud. It's a lot easier and faster to set up than SQL Server.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View