What am I doing wrong? I get a run time error 91 when I get to
" .CommandText = SqlSet".

please help.


Private Sub UpdateAccess1_Click()

Dim DBFullName As String
Dim Cnct As String
Dim cnn As ADODB.Connection
Dim cmdCommand As ADODB.Command
Dim SqlSet


DBFullName = ThisWorkbook.Path & "\NewAccountDatabase.mdb"

Set cnn = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
cnn.Open ConnectionString:=Cnct

SqlSet = " INSERT INTO AccountMaster ([Account Number], [Account
Name], [Clone Manager])"
SqlSet = SqlSet & " VALUES ( " & Range("Number3").Value & " , " &
Range("Name3").Value & ")" & " , " & Range("Manager3").Value & ");"


With cmdCommand
.CommandText = SqlSet
.CommandType = adCmdText
.Execute
End With


cnn.Close
Set cmdCommand = Nothing
Set cnn = Nothing








End Sub

Just a guess (since I don't do this often)

Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnn

before

With cmdCommand
.CommandText = SqlSet


" wrote:

What am I doing wrong? I get a run time error 91 when I get to
" .CommandText = SqlSet".

please help.


Private Sub UpdateAccess1_Click()

Dim DBFullName As String
Dim Cnct As String
Dim cnn As ADODB.Connection
Dim cmdCommand As ADODB.Command
Dim SqlSet


DBFullName = ThisWorkbook.Path & "\NewAccountDatabase.mdb"

Set cnn = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
cnn.Open ConnectionString:=Cnct

SqlSet = " INSERT INTO AccountMaster ([Account Number], [Account
Name], [Clone Manager])"
SqlSet = SqlSet & " VALUES ( " & Range("Number3").Value & " , " &
Range("Name3").Value & ")" & " , " & Range("Manager3").Value & ");"


With cmdCommand
.CommandText = SqlSet
.CommandType = adCmdText
.Execute
End With


cnn.Close
Set cmdCommand = Nothing
Set cnn = Nothing








End Sub

You have not yet set the cmdCommand object to a new or existing
ADODB.Command object. Try including

Set cmdCommand = New ADODB.Command


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





wrote in message
...
What am I doing wrong? I get a run time error 91 when I get to
" .CommandText = SqlSet".

please help.


Private Sub UpdateAccess1_Click()

Dim DBFullName As String
Dim Cnct As String
Dim cnn As ADODB.Connection
Dim cmdCommand As ADODB.Command
Dim SqlSet


DBFullName = ThisWorkbook.Path & "\NewAccountDatabase.mdb"

Set cnn = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
cnn.Open ConnectionString:=Cnct

SqlSet = " INSERT INTO AccountMaster ([Account Number], [Account
Name], [Clone Manager])"
SqlSet = SqlSet & " VALUES ( " & Range("Number3").Value & " , " &
Range("Name3").Value & ")" & " , " & Range("Manager3").Value & ");"


With cmdCommand
.CommandText = SqlSet
.CommandType = adCmdText
.Execute
End With


cnn.Close
Set cmdCommand = Nothing
Set cnn = Nothing








End Sub