I am trying to export a few fields from a worksheet to an MS Access
table and I am getting an Automation error when I attempt to run the
following. Any ideas on what I am doing wrong or not doing?

Sub ExcelToAccess()

Dim CN As ADODB.Connection, RS As ADODB.Recordset, r As Long
Set CN = New ADODB.Connection
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=U:\Intranet
\PMdata.mdb;"
Set RS = New ADODB.Recordset
RS.Open "test_dtlChangeOrderdtl", CN, adOpenKeyset, adLockOptimistic
RS.AddNew
RS.Fields("JobNumber") = Range("B2").Value
RS.Update

End If

RS.Close
Set RS = Nothing
CN.Close
Set CN = Nothing

End Sub

You might try something like this ...

Set connection = CreateObject("ADODB.Connection")

connection.Open "your connection string"
Insert connection
connection.close


Sub Insert(user)

Dim adoCommand

On Error Resume Next

Set adoCommand = New ADODB.Command

With adoCommand
.CommandType = 1
.ActiveConnection = connection
.CommandText = "INSERT INTO MY_SCHEMA.MY_TABLE (MY_COLUMN)
VALUES (?)"
End With


Dim login_VARCHAR
' Go to M$ site and look up the many different versions of
CreateParameter
Set login_VARCHAR = adoCommand.CreateParameter("MY_COLUMN", 200 ,
1, 40)
Call adoCommand.Parameters.Append(issue_app_name_VARCHA R)

login_VARCHAR.Value = Range("B2")

Call adoCommand.Execute
Set adoCommand = Nothing

End Sub

On Jun 13, 5:34 pm, "
wrote:
You might try something like this ...

Set connection = CreateObject("ADODB.Connection")

connection.Open "your connection string"
Insert connection
connection.close

Sub Insert(user)

Dim adoCommand

On Error Resume Next

Set adoCommand = New ADODB.Command

With adoCommand
.CommandType = 1
.ActiveConnection = connection
.CommandText = "INSERT INTO MY_SCHEMA.MY_TABLE (MY_COLUMN)
VALUES (?)"
End With

Dim login_VARCHAR
' Go to M$ site and look up the many different versions of
CreateParameter
Set login_VARCHAR = adoCommand.CreateParameter("MY_COLUMN", 200 ,
1, 40)
Call adoCommand.Parameters.Append(issue_app_name_VARCHA R)

login_VARCHAR.Value = Range("B2")

Call adoCommand.Execute
Set adoCommand = Nothing

End Sub

But why doesn't what I have work?

For starters ... I don't see your SQL statement.