ADO Command

[dhartford]

Thank you in advance for your help,

I run following code and receive the error:

"Run-time error, no value given for one or more requried peremeter"



Private Sub cmdSubmit_Click()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "update Block set company = BIC"
.Execute 'error on this line
End With

Set cmd = Nothing
End Sub

[darthobra]

dhartford,

You need to wrap your text in single quotes...


.CommandText = "update Block set company = 'BIC'; "


Darth

[dhartford]

Thank you very, very much, Darth. It works now.

Now I change the code to use connection as following, and receive the error msg:"Data source name not found and no default driver specified". I'm sure that Data source name is correct with correct path:




Sub cmdSubmit_Click()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

Dim strConn As String

strConn = "Microsoft.Jet.OLEDB.4.0" & _
"Data source = F:\fname.xlsx;" & _
"Extended Properties=Excel 12.0 Xml;HDR=YES"

cnn.Open strConn

End Sub

[dhartford]

Sorry, the connection string is
#code
strConn = "Microsoft.ACE.OLEDB.12.0;" & _
"Data source = F:\fname.xlsx;" & _
"Extended Properties=Excel 12.0 Xml;HDR=YES"
#code

[CreganTur]

If your issue is resolved, then please mark this thread as solved: at the top of this thread click Thread Tools -> Mark As Solved

Thanks:thumb

[darthobra]

Provider= is missing.

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data source = F:\fname.xlsx;" & _
"Extended Properties=Excel 12.0 Xml;HDR=YES"

[Carl A]

Need a couple of extra ""

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\fname.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"