'qryxxxxxxx' is not a valid name??? Help (1 Viewer)

[gwunta]

Hi all,

I created an update query that is designed to update the data in a linked SharePoint. I have created the query fine but when I try to execute the update query I get an error stating:

'qryAMYPOCUpdater' is not a valid name. Make sure that it does not include invalid characters or punctuation blah blah blah

The SQL for the query is posted below. The strange part is I can view this query in datasheet view without a problem, but when I try and execute the query the issue appears. Im wondering if its anything to do with the field names in the linked SharePoint list (see the SQL for details). I cannot change the SharePoint list names as I dont have the permissions to do so. If anyone can shed some light on this issue that would really be appreciated. This is the first time I have come across this issue in 10 years of using Access.

If it helps, I am using Access 2007. The access dbase resides in my usual dev databases directory on my server and I have plenty of other Access applications in there that run update queries without an issue, so I know its not a path problem.

SQL:

UPDATE qryPOCMMTLogUpdater INNER JOIN [POC MMT Log] ON qryPOCMMTLogUpdater.ID = [POC MMT Log].ID SET [POC MMT Log].MMT = [qryPOCMMTLogUpdater].[MMT], [POC MMT Log].Supplier = [qryPOCMMTLogUpdater].[Supplier], [POC MMT Log].[Date MMT Sent] = [qryPOCMMTLogUpdater].[Date MMT Sent], [POC MMT Log].[Material Description] = [qryPOCMMTLogUpdater].[Material Description], [POC MMT Log].[Commercial Invoice Value] = [qryPOCMMTLogUpdater].[Commercial Invoice Value], [POC MMT Log].[Currency] = [qryPOCMMTLogUpdater].[Currency], [POC MMT Log].[Shipment Value (AUD)] = [qryPOCMMTLogUpdater].[Shipment Value (AUD)], [POC MMT Log].[Package Qty] = [qryPOCMMTLogUpdater].[Package Qty], [POC MMT Log].[Total CBM] = [qryPOCMMTLogUpdater].[Total CBM], [POC MMT Log].[Gross Weight (kgs)] = [qryPOCMMTLogUpdater].[Gross Weight (kgs)], [POC MMT Log].FRT = [qryPOCMMTLogUpdater].[FRT], [POC MMT Log].TEU = [qryPOCMMTLogUpdater].[TEU], [POC MMT Log].FEU = [qryPOCMMTLogUpdater].[FEU], [POC MMT Log].[DBS Operator] = [qryPOCMMTLogUpdater].[DBS Operator], [POC MMT Log].[Planning Number] = [qryPOCMMTLogUpdater].[Planning Number], [POC MMT Log].SCN = [qryPOCMMTLogUpdater].[SCN], [POC MMT Log].MOT = [qryPOCMMTLogUpdater].[MOT], [POC MMT Log].[MOT Type] = [qryPOCMMTLogUpdater].[MOT Type], [POC MMT Log].[Flight / Vessel Name] = [qryPOCMMTLogUpdater].[Flight / Vessel Name], [POC MMT Log].[Waybill No#] = [qryPOCMMTLogUpdater].[Waybill No.], [POC MMT Log].[Origin Country] = [qryPOCMMTLogUpdater].[Origin Country], [POC MMT Log].[Origin Port] = [qryPOCMMTLogUpdater].[Origin Port], [POC MMT Log].[Destination Port] = [qryPOCMMTLogUpdater].[Destination Port], [POC MMT Log].Consignee = [qryPOCMMTLogUpdater].[Consignee], [POC MMT Log].Collected = [qryPOCMMTLogUpdater].[Collected], [POC MMT Log].ETD = [qryPOCMMTLogUpdater].[ETD], [POC MMT Log].ATD = [qryPOCMMTLogUpdater].[ATD], [POC MMT Log].ETA = [qryPOCMMTLogUpdater].[ETA], [POC MMT Log].Delivered = [qryPOCMMTLogUpdater].[Delivered], [POC MMT Log].[KJV Referral] = [qryPOCMMTLogUpdater].[KJV Referral], [POC MMT Log].Remarks = [qryPOCMMTLogUpdater].[Remarks], [POC MMT Log].[ProCarS File Number] = [qryPOCMMTLogUpdater].[ProCarS File Number], [POC MMT Log].[Delivered / Transit / Planning] = [qryPOCMMTLogUpdater].[Delivered / Transit / Planning], [POC MMT Log].[Open / Closed] = [qryPOCMMTLogUpdater].[Open / Closed], [POC MMT Log].[Purchased / Upstream] = [qryPOCMMTLogUpdater].[Purchased / Upstream], [POC MMT Log].ATA = [qryPOCMMTLogUpdater].[ATA], [POC MMT Log].[Vessel ID] = [qryPOCMMTLogUpdater].[Vessel ID], [POC MMT Log].[AU Destination] = [qryPOCMMTLogUpdater].[EDR (AU Dest.)], [POC MMT Log].[Calculated EDR (AU Dest#)] = [qryPOCMMTLogUpdater].[Calculated EDR (AU Dest.)], [POC MMT Log].[EDR (AU Dest#)] = [qryPOCMMTLogUpdater].[EDR (AU Dest.)];

 

[Galaxiom]

Take a close look at your spelling in the command where you call the query.

 

[gwunta]

Thanks for the suggestion but I am not calling the query in any code at this point. I wanted to make sure the query executed correctly before I implemented any code that leveraged the query.

 

[Galaxiom]

Maybe it is a corruption. Copy the SQL and paste it into a new query.

 

[gwunta]

OK so I found the issue - the hashes were being converted to full stops - arrrgghhh the curse of the programmer - cut and paste :banghead::banghead::banghead:

So it now executes - to a point. Now I am getting a message saying that the records were not updated due to record locks. Could this be because I am updating a table using the results of a query that is based on that same table?

 

[Galaxiom]

OK so I found the issue - the hashes were being converted to full stops

Another reason to never use special characters in object names.

I would highly recommend you abandon all special characters and spaces from names. Then you won't need to use the square brackets when referring to them in code either.

Now I am getting a message saying that the records were not updated due to record locks. Could this be because I am updating a table using the results of a query that is based on that same table?

A Select shouldn't cause a lock. Do you have any bound forms open against the same records?

 

[gwunta]

Thanks Galaxion. Yes, I completely agree with you, special characters should never be used, unfortunately the people that developed the SharePoint lists didn't have the knowledge to do so, so even if I change the names on my local copy of the lists, the query to update the SharePoint version of them will still need to include them (since I dont have the permissions to change the SP list design). I dont have anything bound to either of the queries at the moment, they are simply just queries. I rewrote the referencing query and that seems to have rid the lock issue, now its down to type mismatch errors which I should be able to deal with quite easily. I will update here on the results of the solution to that issue, cheers

 

[gwunta]

After some investigation, I found that the table I am trying to update with the query is slightly different in its definition to the source table, hence the type mismatch error. The table I am trying to update is linked from a SharePoint list and uses a lookup to populate the field in question (its bound datatype is Number). The 'source' table which is to provide the data for the update query uses text as its datatype. So I redefined the 'source' table to use the same lookup data to, what I thought, would resolve the issue.

Now, when I run the update query I get an error message saying that Access for unable to update the records because of validation rule violations (in this run there were 15 records to be updated and all 15 were flagged for validation rule violations. I have checked the target table definitions and there are no fields that are required or that have any user defined validation rules.

Any suggestions as to what might be causing this?