Invalid SQL UPDATE Syntax

[agehoops]

I'm using the following SQL to update a table, however it is throwing back an "Invalid UPDATE Syntax" error message.

DoCmd.RunSQL "UPDATE MSysObjects " & _
             "SET Database = strInputFileName " & _
             "WHERE Not IsNull(Database) ;"

Could someone possibly help me as to what is wrong with it?
Thanks

 

[WayneRyan]

Hoops,

DoCmd.RunSQL "UPDATE MSysObjects " & _
             "SET Database = '" & strInputFileName & "' " & _
             "WHERE Not IsNull(Database);"

BUT, I don't think you're gonna be able to edit Access's table.
It would work on one of your tables though.

What are you trying to do?

Wayne

 

[agehoops]

Nah that's still giving the error of Invalid Syntax

Basically i've created a function where as it checks to see if the backend of the system actually exists, and if not, forces the user to select a back end file, and then use that code to write the new location into the table to access knows where it's looking in order to create the appropriate links

Unless there is another way?

 

[gemma-the-husky]

its might be the -- not isnull -- bit in the where clause

designing something similar in access gives the SQL as

UPDATE Table1 SET Table1.field1 = "test1"
WHERE (((table1.field1) Is Not Null));

 

[agehoops]

nah, still giving me the same error with this:

DoCmd.RunSQL "UPDATE MSysObjects " & _
             "SET Database = '" & strInputFileName & "' " & _
             "WHERE (((MSysObjects.Database) Is Not Null));"

 

[KeithG]

Is that one of the Access System tables you are trying to update? Aren't they read only? Obviously I don't have Access on this PC or I would check.

 

[agehoops]

Yea i'm pretty sure they were and someone else said that they were, but I thought i'd give it a shot before i posted here. Guess it wouldn't work which sucks

 

[KeithG]

Why not just try to create a Update Query with the Query Designer?

 

[agehoops]

Alright i'll give it a go. I'll try later today, as I've gotta try and find (again) where the setting to show system tables is as i've forgotten, then will have to alter things as the data that would be required for the query is currently stored in a variable so at current it can't be done. I'll post back later.

Thanks

 

[KeithG]

Its under Tools=>Options=>Show System tables

 

[WayneRyan]

Hoops,

You can definitely "write" to the table. Not directly though.

If the linked tables are moved, you'll obviously have to relink them. You can
use the File --> Get External Data --> Link tables and re-establish the link,
but if you want to do it programmatically:

You can use the TableDefs object, and assign the .Connect property to your BE database.

There are lots of examples here, just look for TDF, RELINK or CONNECT.

hth,
Wayne

 

[agehoops]

Thanks KeithG, unfortunately in access 2007 there is no tools menu. They've COMPLETELY re-vamped the entire thing so yea. haha.

WayneRyan i've had a look around and found some code, so will try and get that to work.

Thanks for all the help people, really appreciate it