DBExec and working with Microsoft Access 2007 SQL

[iggytomcarr]

Hi

I am a bit new to using Access 2007 but after a couple of hours of tinkering around i have managed to create a basic database and write some code that successfully retrieves information from a database and stores it in an array.

My problem now is that - i want to write some information i have collected using macro sheduler back into the same database.

When creating the code that retrieves information from the database before i used access' own sql designer and then copied and pasted the sql command it built for me into macro sheduler and the script seemed to work fine.

An example of this 'retrieve code' which uses the DBQuery Command:

Code: Select all

Let>SQL=SELECT [Part Details].[Our Part Number]FROM [Part Details]; //everything after the = was copied from what access generated for me
DBQuery>dbH,SQL,Part_Num_Array,NumRecs,NumFields,0

The next task i want to do is update the database using the DBExec command.

My current code is:

Code: Select all

Let>DB_Add_Update=INSERT INTO [Part Details] SET [Part Details].[Buy At Price] = 1.00, [Part Details].[Sell At Price] = 1555.00, [Part Details].Weight = 1.11 WHERE ((([Part Details].[Our Part Number]) Like AEU14701*));

DBExec>dbH,DB_Add_Update,return

The problem that i seem to be having is that Access 2007 generates this SQL command and formats it over 2 lines.



And within macro scheduler i need to feed it into the SQL variable DB_Add_Update over one line.

This is the code as generated by Access 2007

Code: Select all

UPDATE [Part Details] SET [Part Details].[Buy At Price] = "100.00", [Part Details].[Sell At Price] = "1223.00", [Part Details].Weight = "0.67"
WHERE ((([Part Details].[Our Part Number]) Like "AEU14701*"));

If my current code macrosheduler reports an error and states that there is a problem - Too few parameters, expected 1



I know that i am accessing the database property as if i just tell it to run the code below (although for some reason i have had to remove all the ""s surronding the numerical values i am trying to pass to the database! - i don't know why - but this seems to make it work!!!):

Code: Select all

Let>DB_Add_Update=INSERT INTO [Part Details] SET [Part Details].[Buy At Price] = 1.00, [Part Details].[Sell At Price] = 1555.00, [Part Details].Weight = 1.11

DBExec>dbH,DB_Add_Update,return

The Database is updated successfully - but all the records have been updated rather than just the one i want to specify. (Using the WHERE command)



I hope i have explained this problem clearly and I realise that this isn't strictly a Macro Shelduler coding problem.

Just wondering if any one with experience of using Macro Scheduler to edit Access Databases can help me find the correct syntax?

Any help would be appreciated.


[/b]

[Marcus Tettmar]

AEU14701* is a string. So you would need:

Let>DB_Add_Update=INSERT INTO [Part Details] SET [Part Details].[Buy At Price] = 1.00, [Part Details].[Sell At Price] = 1555.00, [Part Details].Weight = 1.11 WHERE ((([Part Details].[Our Part Number]) Like "AEU14701*"));

DBExec>dbH,DB_Add_Update,return

I don't know if that is the only problem but it is a problem. As you have it with no quotes around AEU14701 you will get a syntax error since AEU14701 is not a number.