How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.
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.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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
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
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 & ")
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
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
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.
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
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
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
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.
Use debug.print to output your debug data to the immediate window to see if your command is being constructed correctly.
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?
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?
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.
.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.
I still get the same error. It does not go as far as the 'add:' line.
Thanks for you help.
ERROR-MESSAGE.PNG
Thanks for you help.
ERROR-MESSAGE.PNG
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.
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.
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.
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
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=c
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.
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=c
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
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.
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.
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.
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.ConnectionSt
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.ConnectionSt
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
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
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
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?
Before you can execute the insert statement you need to create a command object as well. Then you execute the command object and pass the connection object as a parameter to the command object.
For example:
For example:
dim con as new adodb.connection
dim com as adodb.command
dim varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"
con.open varConnection
com.execute('insert statement here', con)
set com = New ADODB.Command
with com
.ActiveConnection = con
.CommandText = "insert statement here"
end with
oops
dim con as new adodb.connection
dim com as adodb.command
dim varConnection = "ODBC;DSN=CLD;UID=CLD;PWD=cld;APP=Microsoft Office 2003;WSID=SID14412"
con.open varConnection
set com = New ADODB.Command
with com
.ActiveConnection = con
.CommandText = "insert statement here"
end with
Check you have the correct connection string for your target database.
http://www.connectionstrings.com/
http://www.connectionstrings.com/
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"
varConnection = "Provider=SQLOLEDB; Data Source=iowaeng; Initial Catalog=CLD; User ID=cld; Password=cld"
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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial