CurrentDb.Execute

[mbazdell]

Hi all. First post here. Super new to messing around with VBA but have gotten pretty far I think. Here's the problem. I have a variable that contains a perfectly acceptable MySQL statement (as in, I can copy it into mysql directly and it works) yet when I run it through CurrentDb.Execute VariableName I get "Too few parameters. Expected 1."

NewTicketChanges = "INSERT INTO ticketchanges VALUES (NULL," _
& Me.TFID & ",DEFAULT," _
& IIf(Me.TFDetails = CleanArray(1), "NULL,", "'" & EscapeQuotes(Me.TFDetails) & "',") _
& IIf(Me.TFStatus = CleanArray(2), "NULL,", Me.TFStatus & ",") _
& IIf(Me.TFPriority = CleanArray(7), "NULL,", Me.TFPriority & ",") _
& IIf(Nz(Me.TFAssignedTo, "") = Nz(CleanArray(4), ""), "NULL,", Me.TFAssignedTo & ",") _
& IIf(Me.TFCategory = CleanArray(3), "NULL,", Me.TFCategory & ",") _
& IIf(Nz(Me.TFCompany, "") = Nz(CleanArray(5), ""), "NULL,", Me.TFCompany & ",") _
& IIf(Nz(Me.TFLot, "") = Nz(CleanArray(8), ""), "NULL,", Me.TFLot & ",") _
& IIf(Nz(Me.TFWorkOrder, "") = Nz(CleanArray(6), ""), "NULL,", Me.TFWorkOrder & ",") _
& IIf(Me.TFSource = CleanArray(9), "NULL,", Me.TFSource & ",") _
& [TempVars]![tmpUserID] & ");"
CurrentDb.Execute NewTicketChanges

Which the makes

INSERT INTO ticketchanges VALUES (NULL,46,DEFAULT,'few af ae a',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2);

The only thing I can think of is that I'm not declaring the column names because as you can see by the code, it's rather dynamic. So... Any ideas?

 

[vbaInet]

First thing I see is that DEFAULT needs to be wrapped in single quotes.

 

[mbazdell]

Hmmm.. I didn't try that. However when I do it does work except it prevents the server from using it's default. That column is a DATETIME with the default of CURRENT_TIMESTAMP. I don't want to use NOW() in the code because that would use the system time instead of the server's...

 

[vbaInet]

Omit that field and it should insert it's default value. Is this a SQL Server backend?

 

[mbazdell]

It is indeed. If I omit that field I get an error from access of an invalid insert statement. If I use '' it works but the field is blank.

 

[vbaInet]

Let me see the SQL statement after removing it. Also, what constraints did you set on the field?

 

[mbazdell]

This gives an error

INSERT INTO ticketchanges VALUES (NULL,46,,'Hello',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2);

This leaves the column blank

INSERT INTO ticketchanges VALUES (NULL,46,'','Hello',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2);

This works but uses system time

INSERT INTO ticketchanges VALUES (NULL,46,NOW(),'Hello',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2);

The column is just a DATETIME column which only accepts date formatted data. However in a normal entry where I specify the columns and ignore that one, the server uses it's CURRENT_TIMESTAMP. I was just trying to avoid having to dynamically specify the columns too.

 

[vbaInet]

Well, you can't omit a field with any of the methods you tried in any db environment. You will have to explicitly specify the columns in order to omit it (as you've already figured out).

However, if you change the order of your fields and have the Default field as the last field, you might get away with omitting it without specifying the column names. Give it a try.

 

[mbazdell]

Well it was worth shot. Gave the error of too few value for the number of columns. I suppose if I rework it to use INSERT INTO table SET that is supported in MySQL, it may work and not be that difficult. Any idea if this works in Access?

 

[jdraw]

I recommend you name your fields when doing an Insert Into as a "standard practice".
If the table structure is ever changed, you won't have to track down any code that "assumes" a structure.

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Also, if you are building SQL within vba, always test the "rendering" by using a Debug.Print NewTicketChanges to see what Access has determined your SQL to be.

 

[mdlueck]

I have a variable that contains a perfectly acceptable MySQL statement (as in, I can copy it into mysql directly and it works) yet when I run it through CurrentDb.Execute VariableName I get "Too few parameters. Expected 1."
...
So... Any ideas?

I would suggest you use ADO objects for this task in place of using CurrentDb.Execute

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Fun/side project: Build dynamic SELECT query using ADODB.Command / .Parameters objs
http://www.access-programmers.co.uk/forums/showthread.php?t=230838

You will just need the correct connection string to pass to the ADO object since the connection is to MySQL and not Access.

The "default" column people are talking about only is if you have set the MySQL table to have default values for specific columns. If the field does not have a default value AND is a NOT NULL (required) field, then you must provide it else the SQL will end in error.

But for columns which have a default value provided, yes wrapping the .Parameters object creattion with an If statement, and then at the same time tacking on the correct SQL to conditionally support the current column, should be a very doable scenario.