Help running SQL query

[Noncents]

Close Actinic and then run the following SQL query on the Actinic Database.

DELETE NewProducts.*
FROM NewProducts LEFT JOIN Product ON NewProducts.sProductRef = Product.[Product Reference]
WHERE (((Product.[Product Reference]) Is Null));

This will clear the list and allow you to recreate it once you are back in Actinic.

This is the assistance I've been given to solve an issue where old 'New products' are displayed. I'm confident it will work but can someone point me in the right direction for how to run a query as i've only ever done this using PHPMyAdmin and MySQL in the past.

All help appreciated as ever.

[CymraegKev]

You'll need to run Access, and open the acatalog.mdb file. Create a new query and switch the SQL view and paste in the SQL above. Then run the query. Remember to snapshot before hand (and probably a good idea to take a copy of the mdb too!).

[Noncents]

Thanks for your advice Kevin, I don't have Access but I do have Base so i'm assuming I can use this. Can you point me in the right direction for locating the file you mentioned.

[CymraegKev]

Hi Ricky - I'm not sure what "Base" is to be honest with you. Is that an OpenOffice tool? Either way, I would only open the .mdb in Access as it is an MS Access Database.

Sorry I couldn't be any more help at this point.

Kevin

[Nadeem]

Hi Ricky,

Where did you get that SQL from, Was it from Support? If so, then you may want to advise them that you do not have Access, so they would do it on your behalf. You will need to give them the most uptodate Access Database you have.

Kind Regards

[Noncents]

Thanks Nadeem that sounds like a sensible solution, i'll send a copy to tech support now as i'd all but given up.

[gabrielcrowe]

http://community.actinic.com/showpos...89&postcount=8

bindun, repost.

take the code in this post and save it to a .vbs file, using notepad.

take the sql query and edit it as you like.

double click the vbs AFTER you make a backup.

you may not have access, but you still have the appropriate database engine.

this is the full thread on getting your crashed actinic back after a new/best products list cockup.

http://community.actinic.com/showthread.php?p=192489

[gabrielcrowe]

edited to include your SQL:

'my shop database is located at:
mydb = "C:\Documents and Settings\All Users\Documents\Actinic v8\Sites\site1\ActinicCatalog.mdb"

	Set OBJdbConnection = CreateObject("ADODB.Connection")
	OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & mydb
	SQLQuery = "DELETE NewProducts.* FROM NewProducts LEFT JOIN Product ON NewProducts.sProductRef = Product.[Product Reference] WHERE (((Product.[Product Reference]) Is Null));"
	Set Result = OBJdbConnection.Execute(SQLQuery)
	OBJdbConnection.Close	
	
msgbox("done")

[Noncents]

Thanks Gabriel, i'll give that a try as Actinic support attempted a fix that didn't work and have been ignoring my clients emails ever since. I'm assisting with 2 Actinic sites at present both of which have encountered the same blundering problem. One of them was given a free upgrade to version 9 in the past because 8 was falling apart at the seams! My patience with Actinic is wearing very, very thin.

Is ***** a dirty word on here?

[gabrielcrowe]

apparently so, yes.

[Noncents]

so it seems! I'm going to try your solution tonight so i'll let you know if it works.

[gabrielcrowe]

Remember: BACKUP.

[Noncents]

What a legend! Didn't get round to this for a while longer than I planned but that solved the problem in no time. I can't thank you enough for that.

I think the question here is why couldn't Actinic support have told me or my client that in the last 2 months worth of emails back and forth?

[Noncents]

Anybody from Actinic even hazzard a guess as to why support weren't clued up on a known bug? The previous question wasn't rhetorical.

I don't mean to be rude but this simple solution would have saved literally hours of grief.