CODEWITHSUNDEEP
sqlms-accessvba
Tam N.
Tam N.

Reputation: 2737

Do I have an error in this SQL created using vba?

I cannot see why this query doesn't work. I use a msgbox to display my strSQL, but it looks perfectly fine to me.

Dim strSQL As String
strSQL = "INSERT INTO Jobs (Date, RefNo, ProjectID, PManager, PDesc, PONo, Src_Qty, WC_Qty, Rate, Total, Note, Company) "
strSQL = strSQL & "VALUES (" & JobDate.Value & ", '" _
                            & Text41.Value & "', '" _
                            & ProjectID.Value & "', '" _
                            & PManager.Value & "', '" _
                            & PDesc.Value & "', " _
                            & Text43.Value & ", " _
                            & Src_Qty.Value & ", " _
                            & WC_Qty.Value & ", " _
                            & newRate.Value & ", " _
                            & ProjTotal.Value & ", '" _
                            & Text38.Value & "', '" _
                            & newCompany.Value _
                            & "');"
MsgBox (strSQL)
DoCmd.RunSQL strSQL

Upvotes: 0

Views: 388

Answers (4)

Oorang
Oorang

Reputation: 6780

I agree with klabranche that the most likely culprit is you need to put hash marks ("#") around the date value. However as an addendum to that thought, if you use CurrentDb.Execute instead of DoCmd.RunSQL you will be given an informative error message what will tell you specifically is wrong. This will make debugging much easier. Hope that helps!

Upvotes: 0

Tony Toews
Tony Toews

Reputation: 7882

In addition to using # delimeters around the date as mentioned by klabranche you should also use an unambiguous date format. Either use the ISO standard of yyyy-mm-dd by using format(Jobdate,"yyyy-mm-dd") or see Return Dates in US #mm/dd/yyyy# format

Double check you are using quotes around test fields and no quotes around numeric fields. ProjectID might be numeric or might be text.

I would rename the text43 and test48 fields on the form to be clearer as to what they are. Assuming this is a bound form you can set them to the same name as the field.

You don't need to use the .Value property.

The problem with DoCmd.RunSQL is that it ignores any errors. Either of the following will display any error messages received by the query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror.. For ADO use CurrentProject.Connection.Execute strCommand, lngRecordsAffected, adCmdText You can then remove the docmd.setwarnings lines if any.

If you're going to use docmd.setwarnings make very sure you put the True statement in any error handling code as well. Otherwise weird things may happen later on especially while you are working on the app. For example you will no longer get the "Do you wish to save your changes" message if you close an object. This may mean that unwanted changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two methods. One posting stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As always YMMV.

HansUp has an excellen point on using reserved field names. Visit Tony's Table and Field Naming Conventions

Upvotes: 2

HansUp
HansUp

Reputation: 97131

Add a line:

Debug.Print strSQL

after

MsgBox (strSQL)

Go to the Immediate Window (Ctrl+g) and copy the completed SQL statement. Then create a new query, switch to SQL View, and paste in the SQL statement. Figure out why the statement fails. I think klabranche is on the money if the "Date" field in your Jobs table is Date/Time data type. If you're unable to identify the problem, edit your question to show the failing SQL statement, and tell us the data types of the Jobs fields.

Also, your Jobs table includes 3 fields whose names are reserved words: Date; Rate; and Note. (See http://allenbrowne.com/AppIssueBadWord.html) If you keep those field names, at least surround them with square brackets to avoid confusing Access:

strSQL = "INSERT INTO Jobs ([Date], RefNo, ...

Upvotes: 3

Kevin LaBranche
Kevin LaBranche

Reputation: 21098

If I remember right you will need to wrap your date with the pound for Access.

"VALUES (#" & JobDate.Value & "#, '" _

And I don't think you need the semicolon at the end....

Upvotes: 7

Related Questions