Export to MS Access


M

Matt

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
 
Ad

Advertisements

G

gimme_this_gimme_that

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_VARCHAR)

login_VARCHAR.Value = Range("B2")

Call adoCommand.Execute
Set adoCommand = Nothing

End Sub
 
M

Matt

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_VARCHAR)

login_VARCHAR.Value = Range("B2")

Call adoCommand.Execute
Set adoCommand = Nothing

End Sub

But why doesn't what I have work?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top