Access SQL Update Query Is Not Working

[kashif.special2005]

Hi,

I am updating an table with Inner Joint, however it is not updating the column.

UPDATE GDM_Structure INNER JOIN GDM_Structure_Temp ON GDM_Structure.Unique_ID = GDM_Structure_Temp.Unique_ID SET GDM_Structure.HOD = [GDM_Structure_Temp].HOD;


When I am doing for check that selection query is working for join or not, but it is not working for join also

SELECT GDM_Structure_Temp.HOD
FROM GDM_Structure INNER JOIN GDM_Structure_Temp ON GDM_Structure.Unique_ID = GDM_Structure_Temp.Unique_ID;


Please help me where I am doing wrong

Thanks
Kashif

 

[Minty]

If the join won't work then the update definitely won't.
Do you get any errors, or do you simply not have a matching Unique_Id record in both tables?

 

[arnelgp]

check if there are
hidden text on
UNIQUE_ID on both table.

check their length if same.

if both fields are Text,
try cleaning them:

Docmd.RunSQL "Update GDM_STRUCTURE Set UNIQUE_ID = Replace(UNIQUE_ID, CHR$(0), '')"
Docmd.RunSQL "Update GDM_STRUCTURE_TEMP Set UNIQUE_ID = Replace(UNIQUE_ID, CHR$(0), '')"

 

[kashif.special2005]

Hi,

The Unique_ID's number is available in both the table, and both are numbers

My head is swimming why it is hapenning

Thanks
Kashif

 

[Minty]

Can you upload a sample data base ?

 

[Pat Hartman]

If the join returns no rows, then there are no matching values in both tables. You can verify this manually if you know what rows are supposed to match by looking up the ID in both tables.

Are you sure you are joining on the correct columns?

 

[kashif.special2005]

Hi Pat Hartman,

Thanks for reply, yes I checked and the Unique_ID is exists in both the table, and strange thing is that, when I opened and closed the database 2 or three times and again run the query it works fine, and some time when I tried to open the table it gives an error message "Not a valid bookmark".

I am not able to understand where I am doing wrong.

Thanks
Kashif

 

[Pat Hartman]

Perhaps your database is corrupted. Does compacting resolve the issue? Are you experiencing the problem on specific records? Is the error repeatable?

 

[kashif.special2005]

Actually, I have database in place and in that database I have two tables and some query, updating query with join, append query with join and delete query, and I am adding the data in the temp table through Excel VBA and running those query from Excel VBA, some time it working fine and some time gives bookmark error and some time it is not giving any error and neither performing those query, means not giving any error message neither performing those query.

My head is just stuck and I am not able to understand, why it is working sometime and sometime not working.

Yes I tried Compact and repair, and it is also not working.

Thanks
Kashif

 

[almahmood]

I believe your database is corrupted and I assume you ran compact & repair command. To check, please create a simple make table query and see that runs for you or not. Try this for both tables. If you get any error, means you have some corrupted records in your table and you need to clean those records.

 

[Minty]

You didn't mention before that these queries were being run from Excel, it would have helped to have know that.

How is Excel connected to your database?

What happens if you run the same queries directly in the Access database?

 

[Pat Hartman]

I can't imagine why you would use Excel as a FE to Access but Excel projects get corrupted also. It would be far more sound design to use an Access FE. I have no idea how you resolve corruption in an Excel workbook though. You would need to ask an Excel expert.

 

[almahmood]

Okay, when we connect from Excel to Access, there is an option of record locking. Is there any chance that you have more than one user on this tool? It can also cause this issue if you kept open your database and executing commands from your Excel. You need to check connection string as well.