sql will not insert a new record into a linked sql server table ?

sql server linked table  

I have the following insert statement:
that will not insert a new record into a linked sql server table  ?

But I'm getting no errors also ?

strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_assigned_DataEntry]([ID_Racfid],[Track_Code],[Seller_Name],[Director_Name],[Track_Name], [Opened_Date],[Closed_Date],[Expected_Completed_Date],[Status],[Originated_From],[Notes],[Activity_Task],[Col_Id_Template],[Expedited])" & vbCrLf
strsql_sql = strsql_sql & "VALUES (" & Me.txtRacfid.Value & "," & Me.cbo_Track_Code.Value & "," & Me.txtSeller.Value & "," & Me.txtDirector.Value & "," & Me.txtTCName.Value & ",now(),now(),'" & Me.txtExpectedDate.Value & "'," & Me.cboStatus & "," & Me.cboOriginated.Value & "," & Me.txtNotes & "," & Me.cboTask & ", " & Me.txtColumnId & "," & Str_Expedite & ");"
CurrentDb.Execute strsql_sql, dbSeeChanges
Else

14 field with 14 values ??

Open in new window




Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
You wont see errors without the dbFailOnError option.  Try running it again with:

CurrentDb.Execute strsql_sql, dbSeeChanges + dbFailOnError

Open in new window

0
FordraidersAuthor Commented:
ok still no error ?  weird.
0
mbizupCommented:
It looks like you have a couple of text fields that are not delimited with quotes...  Are Expedited and Notes text?  If so, try this.  Note that I also added a debug.print statement.  This will display the actual constructed sql in the debugger window.  Check it for sanity by testing/running it in the query builder window.  That is a very useful debugging tactic to help you go from sql problems to a working solution...

strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_assigned_DataEntry]([ID_Racfid],[Track_Code],[Seller_Name],[Director_Name],[Track_Name], [Opened_Date],[Closed_Date],[Expected_Completed_Date],[Status],[Originated_From],[Notes],[Activity_Task],[Col_Id_Template],[Expedited])" & vbCrLf
strsql_sql = strsql_sql & "  VALUES (" & Me.txtRacfid.Value & "," _
                        & Me.cbo_Track_Code.Value & "," _
                        & Me.txtSeller.Value & "," _
                        & Me.txtDirector.Value & "," _
                        & Me.txtTCName.Value _
                        & ",now(),now(),'" & Me.txtExpectedDate.Value & "'," _
                        & Me.cboStatus & "," _
                        & Me.cboOriginated.Value & ",'" _
                        & Me.txtNotes & "'," _
                        & Me.cboTask & ", " _
                        & Me.txtColumnId & ",'" 
                        & Str_Expedite & "');"
Debug.print strsql_sql   ' **** Useful to see the actual sql that gets executed
CurrentDb.Execute strsql_sql, dbFailOnError + dbSeeChanges

Open in new window

0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

mbizupCommented:
Also, the dates don't look quite right.  They should be hashtag delimited.  

strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_assigned_DataEntry]([ID_Racfid],[Track_Code],[Seller_Name],[Director_Name],[Track_Name], [Opened_Date],[Closed_Date],[Expected_Completed_Date],[Status],[Originated_From],[Notes],[Activity_Task],[Col_Id_Template],[Expedited])" & vbCrLf
strsql_sql = strsql_sql & "  VALUES (" & Me.txtRacfid.Value & "," _
                        & Me.cbo_Track_Code.Value & "," _
                        & Me.txtSeller.Value & "," _
                        & Me.txtDirector.Value & "," _
                        & Me.txtTCName.Value _
                        & ",#" & now() & "#,#" & now() & "#,#" & Me.txtExpectedDate.Value & "#," _
                        & Me.cboStatus & "," _
                        & Me.cboOriginated.Value & ",'" _
                        & Me.txtNotes & "'," _
                        & Me.cboTask & ", " _
                        & Me.txtColumnId & ",'" 
                        & Str_Expedite & "');"
Debug.print strsql_sql   ' **** Useful to see the actual sql that gets executed
CurrentDb.Execute strsql_sql, dbSeeChanges

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
You may also have an issue if the linked table does not contain a Primary Key or a TimeStamp (rowversion) column.

By default, I make sure that every SQL table contains a PK field and another Timestamp field (RowVersion data type).  This is not a date or time field, it provides SQL Server with a method to deconflict write conflicts.

HTH
Dale
0
FordraidersAuthor Commented:
Dale, yes it has a primary key..
0
FordraidersAuthor Commented:
if i have a field that i know will be blank such as  [Closed_Date]
do i put in the sql statement  , ,    so no entry gets put there ?

Thanks
fordraiders
0
FordraidersAuthor Commented:
ok,,,the insert statement worked but only if i provided a  value for  #" & Me.txtExpectedDate.Value & "#

it will not always have a value  . sometimes there will be no data for that field.

fordraiders
0
FordraidersAuthor Commented:
I just took the field out of the insert statement, if i'm not going to put anything it it..

BUT

what if by chance i have other fields that may contain an empty textbox value or just a null value ?

?
Thanks
fordraiders
0
mbizupCommented:
Use a recordset instead.  You don't need delimiters, and this should handle nulls more cleanly:

Dim rs AS dao.recordset
set rs = currentdb.openrecordset
rs.addnew
with rs
 	![ID_Racfid] = Me.txtRacfid.Value
	![Track_Code] = Me.cbo_Track_Code.Value
        'etc
End With
rs.update
rs.close
set rs = nothing

Open in new window

1
FordraidersAuthor Commented:
mbizup.  I had that,  taking to long to update on the server...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I agree with Dale.  Be sure your SQL Server tables have a TimeStamp data type. I've seen this solve many problems.
I name my TimeStamp fields  zTimeStamp.
0
PatHartmanCommented:
You appear to be using an unbound form.  Access would be taking care of this if you were using  a bound form.

In the Insert query, only required fields are necessary.  If you include optional fields, they may be null or have a value

If you are using an unbound form, you should be using T-SQL syntax and use a pass-through query.  You gain nothing but trouble by using Access queries against linked tables for unbound forms and an SQL Server database.

I actually don't know what happens if you send ## as a date field.  I assume, it will be handled correctly but you will have to test carefully.  Same with "" for a text field.  You need to know if the field ends up as null or a ZLS.

To facilitate testing,  print the SQL string to the immediate window.  Copy it and paste it into the SQL view of the QBE.  You will frequently get different/better errors than you get when you use DAO or ADO to run the append query.

You said the query is taking too long to update the server.  Is this code inside a loop?  If not, then you should absolutely be using a bound form.  Just make sure that the form selects a single record.  As the others have suggested, a timestamp (now called something else) will help Access to make the insert more efficient but you should NOT be using Access at all.  There is no point whatsoever to using Access SQL against linked tables in this case.  If you are going to use an unbound form, cut the cord and use pass through queries and stored procedures.  Otherwise, you are simply duplicating what Access is doing only you are doing it less efficiently.
0
Dale FyeOwner, Developing Solutions LLCCommented:
@ford,

When I'm creating a SQL string in VBA, I use a function that I wrote a long time ago for formatting data in a SQL Statement.
Public Function fnQuotes(QuoteWhat as Variant, Optional QuoteWith as String = """") as String

    If isnull(QuoteWhat) then
        fnQuotes = "NULL"
    Else
        fnQuotes = QuoteWith & Replace(QuoteWhat, QuoteWith, QuoteWith & QuoteWith) & QuoteWith
    End IF

End Function

Open in new window

When I use this, I can eliminate the single and double quotes in my strSQL definition, so this:
strSQL = "INSERT INTO myTable (StringField, NumField, DateField) " _
       & "Values ('" & me.txtString & "', " & me.txtNumber & ", #" & me.txtDate & "#)"

Open in new window

with:
strSQL = "INSERT INTO myTable (StringField, NumField, DateField) " _
       & "Values (" & fnQuotes(me.txtString) & ", " _
                    & fnQuotes(me.txtNumber, "") & "," _
                    & fnQuotes(me.txtDate, "#") & ")"

Open in new window

The advantages of this are:
1.  You can pass any delimiter, I generally use the default ", but you could use a single quote or even a #
2.  If the value you pass it is NULL, then the return value is the text value "NULL", so a NULL or empty txtDate would return a string like:

?strSQL
INSERT INTO myTable (StringField, NumField, DateField)
Values("myString", 3, NULL)

3.  if the string value you are inserting has an embedded character that is the same as the character you are trying to wrap it with (generally a " or a '), it replaces that character with two of that character ("" or ''), which JET and SQL Server both interpret as a single single character.
4.  As in the example above, you can see that you can even pass this function a numeric value with an empty string as the "QuoteWith" argument in order to generate a number or the "NULL" entry.  So if me.txtNumber is NULL then the above example would return


INSERT INTO myTable (StringField, NumField, DateField)
Values("myString", NULL, #3/8/2018#)
0
Fabrice LambertFabrice LambertCommented:
Another way to handle null values better than building an SQL string is to create a parameterized query.
Plus it have the adventage of preventing SQL injection.

In the query designer, add parameters with correct names and data types.
Then with VBA:
Dim db As DAO.Database
Set db = CurrentDb

Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("yourQueryName")
qd.Parameters("Param1").Value = myControl1.Value
qd.Parameters("Param2").Value = myControl2.Value
    '// ect ....
qd.execute(dbFailOnError)
qd.close
Set qd = Nothing
Set db = Nothing

Open in new window

0
FordraidersAuthor Commented:
Thanks all !! great learning !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.