Avatar of rckrch
rckrchFlag for United States of America

asked on 

Inserting data from Excel into SQL database table

I am trying to use the insert command to enter data as a new record into a table in SQL 2005.  I get an error 'Object Required'.  Attached is a snipet of the code.

Thanks in advance.
varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"  ';DATABASE A=CLD;TABLE=dbo.Data"'

        With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time"                            ''6/1/2010 00:00:00'"
            .CommandText = "VALUES (VALKD, VALLow_Target, VALLow_LowerTol, VALLow_UpperTol, VALLow_Deflection, VALNom_Target, VALNom_LowerTol, VALNom_UpperTol, VALNom_Deflection, VALUpp_Target, VALUpp_LowerTol, VALUpp_UpperTol, VALUpp_Deflection, VALTolPercent, VALTol2N, VALTolInput, VALDate_Time"
            .Name = "Query from 159.157.10_1"
            .RefreshStyle = xlOverwriteCells
            .Refresh BackgroundQuery:=False
        
        End With

Open in new window

Microsoft SQL ServerMicrosoft Excel

Avatar of undefined
Last Comment
Lee
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

You're overwriting the command text in the second line:

        With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time"   & _
                                        " VALUES (VALKD, VALLow_Target, VALLow_LowerTol, VALLow_UpperTol, VALLow_Deflection, VALNom_Target, VALNom_LowerTol, VALNom_UpperTol, VALNom_Deflection, VALUpp_Target, VALUpp_LowerTol, VALUpp_UpperTol, VALUpp_Deflection, VALTolPercent, VALTol2N, VALTolInput, VALDate_Time"
            .Name = "Query from 159.157.10_1"
            .RefreshStyle = xlOverwriteCells
            .Refresh BackgroundQuery:=False
       
        End With
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

Thanks.  I tried that, but I can't get my syntax right.  What am I doing wrong?
With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) & _                            ''6/1/2010 00:00:00'"
                VALUES (" & VALKD & ", " & VALLow_Target & ", " & VALLow_LowerTol & ", " & VALLow_UpperTol & ", " & VALLow_Deflection & ", " & VALNom_Target & ", " & VALNom_LowerTol & ", " & VALNom_UpperTol & ", " & VALNom_Deflection & ", " & VALUpp_Target & ", " & VALUpp_LowerTol & ", " & VALUpp_UpperTol & ", " & VALUpp_Deflection & ", " & VALTolPercent", " & VALTol2N & ", " & VALTolInput & ", " & VALDate_Time & ")
            .Name = "Query from 159.157.10_1"
            .RefreshStyle = xlOverwriteCells
            .Refresh BackgroundQuery:=False
        
        End With

Open in new window

Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Copy and paste mine. You missed out the " on the second line beginning VALUES. The & _ is a line continuation character for VBA.

So, In the second line beginning " VALUES there is a deliberate space. It is not strictly speaking needed but if you output you CommandText to the console or immediate window you will be able to see your string.

Lee
.CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) & _                            ''6/1/2010 00:00:00'"
                " VALUES (" & VALKD & ", " & VALLow_Target & ", " & VALLow_LowerTol & ", " & VALLow_UpperTol & ", " & VALLow_Deflection & ", " & VALNom_Target & ", " & VALNom_LowerTol & ", " & VALNom_UpperTol & ", " & VALNom_Deflection & ", " & VALUpp_Target & ", " & VALUpp_LowerTol & ", " & VALUpp_UpperTol & ", " & VALUpp_Deflection & ", " & VALTolPercent", " & VALTol2N & ", " & VALTolInput & ", " & VALDate_Time & ")

Open in new window

Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Well, technically the _ is a line continuation character. The & is there to concatenate the two separate strings together.

Consider:

dim sMyString
sMyString = "hello world"

is syntactically identical to:

dim sMyString
sMyString = "hello " & "world"

and the same as:

dim sMyString
sMyString = "hello " & _
                   "world"


All 3 will return the same result.

Lee
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

I appreciate your help.  I get the following error when using your code.

Thanks

CODE-ERROR.PNG
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

Here is more of my code.  Now I am getting an 'Object Required' error in the 'CommandText' line of the code.  I have difined all variables and checked spelling in my code.

Any help?

Thanks in advance.
Dim VALKD As String
    Dim VALLow_Target As Single
    Dim VALLow_LowerTol As Single
    Dim VALLow_UpperTol As Single
    Dim VALLow_Deflection As Single
    Dim VALNom_Target As Single
    Dim VALNom_LowerTol As Single
    Dim VALNom_UpperTol As Single
    Dim VALNom_Deflection As Single
    Dim VALUpp_Target As Single
    Dim VALUpp_LowerTol As Single
    Dim VALUpp_UpperTol As Single
    Dim VALUpp_Deflection As Single
    Dim VALTolPercent As Single
    Dim VALTol2N As Single
    Dim VALTolInput As Single
    Dim VALDate_Time As Date
    
    VALKD = cboDIE_NO
    VALLow_Target = txtLOWER
    VALLow_LowerTol = txtLOWER_TOL_L
    VALLow_UpperTol = txtUPPER_TOL_L
    VALLow_Deflection = txtLOWER_DEF
    VALNom_Target = txtNOMINAL
    VALNom_LowerTol = txtLOWER_TOL_N
    VALNom_UpperTol = txtUPPER_TOL_N
    VALNom_Deflection = txtNOMINAL_DEF
    VALUpp_Target = txtUPPER
    VALUpp_LowerTol = txtLOWER_TOL_U
    VALUpp_UpperTol = txtUPPER_TOL_U
    VALUpp_Deflection = txtUPPER_DEF
    VALTolPercent = chkTOL_PERCENT
    VALTol2N = chkTOL_2N
    VALTolInput = chkTOL_INPUT
    VALDate_Time = Format(Now(), "m/d/yyyy h:mm AM/PM")
    
    'Update table_name
    'SET column1=value, column2=value2,...
    'WHERE some_column = some_value
    
     
    Sheets("CLD CURVES").Activate
    For TOLCOLUMN = 1 To 200
        If Sheets("CLD CURVES").Cells(1, TOLCOLUMN) = "KD_TOL" Then
            
                If Sheets("CLD CURVES").Cells(2, TOLCOLUMN) = "" Then
                GoTo ADDRECORD
                Else:
                GoTo UPDATERECORD
                End If
        End If
            
    Next TOLCOLUMN
    
ADDRECORD:
        varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"  ';DATABASE A=CLD;TABLE=dbo.Data"'

        With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) VALUES('" & VALKD & "', '" & VALLow_Target & "', '" & VALLow_LowerTol & "', '" & VALLow_UpperTol & "', '" & VALLow_Deflection & "', '" & VALNom_Target & "', '" & VALNom_LowerTol & "', '" & VALNom_UpperTol & "', '" & VALNom_Deflection & "', '" & VALUpp_Target & "', '" & VALUpp_LowerTol & "', '" & VALUpp_UpperTol & "', '" & VALUpp_Deflection & "', '" & VALTolPercent & "', '" & VALTol2N & "', '" & VALTolInput & "', '" & VALDate_Time & "')"
            '.Name = "Query from 159.157.10_1"
            '.RefreshStyle = xlOverwriteCells
            '.Refresh BackgroundQuery:=False
        
        End With

Open in new window

Avatar of G Trurab Khan
G Trurab Khan
Flag of Pakistan image

Where is labe UPDATERECORD defined ??
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Why use goto's? Surely a function call is good here?

After the with block put a break point and then output the commandtext to see if it is valid. Then try running the insert in SQL Management Studio to see if it works.

Lee
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

I think there are deeper issues with the code here rather than just a syntax error defining the command text. The connection string doesn't look right to me to insert into SQL 2005.
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Use debug.print to output your debug data to the immediate window to see if your command is being constructed correctly.
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

UPDATERECORD is defined in the next line of code that is not shown - not included in this step for this procedure based on the condition of the data.

The connection string used here is the same one as that used for selecting data out of the same table - it works fine - no issues.

I tried using the same query text I have in my code in the sql query window and it works fine.  Does exactly what it should.

How do I use debug.print?  Where?
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

it is in the VBA code. After this:

.CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) VALUES('" & VALKD & "', '" & VALLow_Target & "', '" & VALLow_LowerTol & "', '" & VALLow_UpperTol & "', '" & VALLow_Deflection & "', '" & VALNom_Target & "', '" & VALNom_LowerTol & "', '" & VALNom_UpperTol & "', '" & VALNom_Deflection & "', '" & VALUpp_Target & "', '" & VALUpp_LowerTol & "', '" & VALUpp_UpperTol & "', '" & VALUpp_Deflection & "', '" & VALTolPercent & "', '" & VALTol2N & "', '" & VALTolInput & "', '" & VALDate_Time & "')"

add:
debug.print .CommandText

It will appear in the immediate window. If that isn't visible, press Ctrl-G to enable it.
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

I still get the same error.  It does not go as far as the 'add:' line.

Thanks for you help.
ERROR-MESSAGE.PNG
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

The image here shows the immediate window that I have so far.
ERROR-W-IMMEDIATE-WINDOW.PNG
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Take out the add:

It thinks this is another label. Ideally you shouldn't be using labels like this as it is not good practice. Basically you need to output the contents of the .CommandText to the immediate window to see if that is even producing valid SQL. It is difficult to see where the error could be because I don't have all your code to view.
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

This is all of the code from the sub up to the next step which has nothing to do with what the procedure is doing here.

Thanks for your help.
Private Sub cmdCLD_CURVE_Click()
    Dim Date_Time As Date
    Dim SERIALNO As Single
    Dim COLORID As Integer
    Dim varConnection
    
    
    
    COLORID = 1
    
    Dim VALKD As String
    Dim VALLow_Target As Single
    Dim VALLow_LowerTol As Single
    Dim VALLow_UpperTol As Single
    Dim VALLow_Deflection As Single
    Dim VALNom_Target As Single
    Dim VALNom_LowerTol As Single
    Dim VALNom_UpperTol As Single
    Dim VALNom_Deflection As Single
    Dim VALUpp_Target As Single
    Dim VALUpp_LowerTol As Single
    Dim VALUpp_UpperTol As Single
    Dim VALUpp_Deflection As Single
    Dim VALTolPercent As Single
    Dim VALTol2N As Single
    Dim VALTolInput As Single
    Dim VALDate_Time As Date
    
    VALKD = cboDIE_NO
    VALLow_Target = txtLOWER
    VALLow_LowerTol = txtLOWER_TOL_L
    VALLow_UpperTol = txtUPPER_TOL_L
    VALLow_Deflection = txtLOWER_DEF
    VALNom_Target = txtNOMINAL
    VALNom_LowerTol = txtLOWER_TOL_N
    VALNom_UpperTol = txtUPPER_TOL_N
    VALNom_Deflection = txtNOMINAL_DEF
    VALUpp_Target = txtUPPER
    VALUpp_LowerTol = txtLOWER_TOL_U
    VALUpp_UpperTol = txtUPPER_TOL_U
    VALUpp_Deflection = txtUPPER_DEF
    VALTolPercent = chkTOL_PERCENT
    VALTol2N = chkTOL_2N
    VALTolInput = chkTOL_INPUT
    VALDate_Time = Format(Now(), "m/d/yyyy h:mm AM/PM")
    
    'Update table_name
    'SET column1=value, column2=value2,...
    'WHERE some_column = some_value
    
     
    Sheets("CLD CURVES").Activate
    For TOLCOLUMN = 1 To 200
        If Sheets("CLD CURVES").Cells(1, TOLCOLUMN) = "KD_TOL" Then
            
                If Sheets("CLD CURVES").Cells(2, TOLCOLUMN) = "" Then
                GoTo ADDRECORD
                Else:
                GoTo UPDATERECORD
                End If
        End If
            
    Next TOLCOLUMN
    
ADDRECORD:
        varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"  ';DATABASE A=CLD;TABLE=dbo.Data"'

        With varConnection
            .CommandText = "INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) VALUES('" & VALKD & "', '" & VALLow_Target & "', '" & VALLow_LowerTol & "', '" & VALLow_UpperTol & "', '" & VALLow_Deflection & "', '" & VALNom_Target & "', '" & VALNom_LowerTol & "', '" & VALNom_UpperTol & "', '" & VALNom_Deflection & "', '" & VALUpp_Target & "', '" & VALUpp_LowerTol & "', '" & VALUpp_UpperTol & "', '" & VALUpp_Deflection & "', '" & VALTolPercent & "', '" & VALTol2N & "', '" & VALTolInput & "', '" & VALDate_Time & "')"
            '.Name = "Query from 159.157.10_1"
            '.RefreshStyle = xlOverwriteCells
            '.Refresh BackgroundQuery:=False
            Debug.Print .CommandText
        
        End With

Open in new window

Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

I'm struggling here. What happens AFTER the with block? You are using a GOTO to push control to a label. Does it return afterwards? Your error may not be where you think it is as this code is not well structured. In line 58 of your code snippet above you have

Else:

It should be

Else

The interpreter will see this as a label. Labels are typically used to alter control flow when errors occur as VB and VBA did not have proper error handling. Labels should be used to alter the flow of control in the way the code above is trying to do. Also, you have defined a string variable called varConnection which holds a string containing:

ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412

Then below it you're using a With block on the string which is why you're getting an object error as you haven't created a connection object yet. Typically you create a connection object USING and connection string. Then you create a command object and if need be you create a recordset object to return the data. See example below.
Public Function InsertRates() As String
On Error GoTo InsertRates_ERROR:
  Dim bFoundStartPoint                  As Boolean
  Dim xlWS                              As Excel.Worksheet
  Dim sReturn                           As String
  Dim lCurrenciesAvailable              As Long
  Dim bOK                               As Boolean
  Dim oConADODB                         As ADODB.Connection
  
  Set oConADODB = New ADODB.Connection
  oConADODB.ConnectionString = "driver=" & DSN_SQL_DRIVER & ";server=" & DSN_SQL_SERVER & ";uid=" & DSN_SQL_UID & ";pwd=" & DSN_SQL_LIVE_PWD & ";database=" & DSN_SQL_DATABASE & ";"
  oConADODB.ConnectionTimeout = 30
  oConADODB.Open

Open in new window

Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

No it does not return.  The GOTO statement only directs the procedure to the correct query based on the condition (data or no data).  It will either perform an 'Enter New Data' or 'Update Current Data' task.  The Update function is below the code snippet you see.

What trouble shooting and stepping through the code it seems to work as intended in that it evaluates the condition and determines the correct procedure to execute and goes there with teh GOTO statement.

You may be right - it may not be well constructed, but this is the type of code I wright from time with informal self training.
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

The error is not the control flow here. The problem is that you're using a With block on a string variable that contains the connection string. You have no code there that actually makes the connection to the database.
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

OK.

As I understand it then I need to create 2 objects in my code.  Connection and then command.  Below is my defnition and connection string.  When I make the command text string what is the syntax to use them together to actually perform the query.  I also enabled the 'Microsoft ActiveX Data Library 6.0' could that have also been part of the problem?
 
Dim varConnection As ADODB.Connection
Set varConnection = New ADODB.Connection
    varConnection.ConnectionString = "Driver=SQL Server;Server=159.157.10.1;UID=CLD;PWD=cld;Database=CLD;APP=Microsoft Office 2003;WSID=SID14412"
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

See here for example:  http://vbadud.blogspot.com/2009/03/how-to-connect-sql-express-2005-from.html

You probably don't need the recordset object as you're only updating the database rather than returning data. The reference you have used may not be right. You should be using Microsoft ActiveX Data Objects of one version or another. depending on what you have installed on your machine.

Lee
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

How do I define and set up the command text.  What I have done is obviously all wrong.
Avatar of G Trurab Khan
G Trurab Khan
Flag of Pakistan image

Add Microsoft Activex DataObject Library in your project & replace ADDRECORD segment with following cade

 
ADDRECORD:
        varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"  ';DATABASE A=CLD;TABLE=dbo.Data"'
        Dim m_oConn As New ADODB.Connection

        m_oConn.Open varConnection
        With m_oConn
            m_oConn.Execute ("INSERT INTO CLDTolerances (KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time) VALUES('" & VALKD & "', '" & VALLow_Target & "', '" & VALLow_LowerTol & "', '" & VALLow_UpperTol & "', '" & VALLow_Deflection & "', '" & VALNom_Target & "', '" & VALNom_LowerTol & "', '" & VALNom_UpperTol & "', '" & VALNom_Deflection & "', '" & VALUpp_Target & "', '" & VALUpp_LowerTol & "', '" & VALUpp_UpperTol & "', '" & VALUpp_Deflection & "', '" & VALTolPercent & "', '" & VALTol2N & "', '" & VALTolInput & "', '" & VALDate_Time & "')")
            '.Name = "Query from 159.157.10_1"
            '.RefreshStyle = xlOverwriteCells
            '.Refresh BackgroundQuery:=False
        
        End With
        m_oConn.Close

Open in new window

Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

It looks like this is making prorgress - I am getting different errors now.  I don't understand if my connection works on the other querys that are performed in the same program why should I have a problem here?

 User generated image
ASKER CERTIFIED SOLUTION
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

Thanks, but I get the same error at the con.open varConnection line.
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

I tried this connection and I get no error, but nothing else happens either.  No data is entered into database.

varConnection = "Provider=SQLOLEDB; Data Source=iowaeng; Initial Catalog=CLD; User ID=cld; Password=cld"
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Run a SQL trace on the database while it attempts to run so you can see what (if anything) is actually getting to the SQL server.
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

How do i do that?
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

This is what I finally had to do to make it work.  Using RecordSet.

Thanks, you were a big help.

Dim QCommand As ADODB.Command
    Dim varConnection As String
    varConnection = "Provider=SQLOLEDB;Data Source=iowaeng;Initial Catalog=CLD;User ID=CLD;Password=cld"

    con.Open varConnection
   
    Set QCommand = New ADODB.Command
    myFields = "KD_TOL, Low_Target, Low_LowerTol, Low_UpperTol, Low_Deflection, Nom_Target, Nom_LowerTol, Nom_UpperTol, Nom_Deflection, Upp_Target, Upp_LowerTol, Upp_UpperTol, Upp_Deflection, TolPercent, Tol2N, TolInput, Date_Time"
    myTable = "SELECT " & myFields & " FROM CLDTolerances"
    rs1.Open myTable, con, adOpenStatic, adLockOptimistic, adCmdText
    rs1.AddNew

    With rs1
        !KD_TOL = VALKD
        !Low_Target = VALLow_Target
        !Low_LowerTol = VALLow_LowerTol
        !Low_UpperTol = VALLow_UpperTol
        !Low_Deflection = VALLow_Deflection
        !Nom_Target = VALNom_Target
        !Nom_LowerTol = VALNom_LowerTol
        !Nom_UpperTol = VALNom_UpperTol
        !Nom_Deflection = VALNom_Deflection
        !Upp_Target = VALUpp_Target
        !Upp_LowerTol = VALUpp_LowerTol
        !Upp_UpperTol = VALUpp_UpperTol
        !Upp_Deflection = VALUpp_Deflection
        !Date_Time = VALDate_Time
        .Update
    End With
    con.Close
Avatar of rckrch
rckrch
Flag of United States of America image

ASKER

Although the solution may not have been complete.  I would not have been lead in the direction I went without the recommendations for the expert.
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Woo, that took some time :) we got there though. Glad to be of help.

Lee
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo