Exporting from Excel to Access: HELP!

[WhytheQ]

I've got the following code. What I'm trying to is export the contents
of a range into an Access table called Scorers. The range C22:E22
contains 'fred','2','2' which correspond to the fields name/age/score.
An error occurs when creating the SQL string.

Do I need to use a loop and individually move each bit of data? Or
should I be using a completely different tact.

Any help greatly appreciated
Jason




Sub AddToAccess()


Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sSQL As String


'create connection string
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\Access
Experiment.mdb"


'create SQL string
sSQL = "INSERT INTO Scorers (name,age,score) VALUES (" & _
Application.Transpose(Range("C22:E22")) & ";"

'make connection and get get the SQL query recordset
Set cnAccess = New ADODB.Connection
cnAccess.ConnectionString = sConnect
cnAccess.Open
cnAccess.Execute sSQL, , adCmdText + adExecuteNoRecords


'close the connection
cnAccess.Close
Set cnAccess = Nothing


End Sub

 

[WhytheQ]

ok instead of using SQL I've gone for the following, possibly more
basic method, instead. It seems to work. Please tell me if it is the
best method, and if there was actaully SQL anwer for transfering data
from a named range in Excel into an Access table in one move.

Any help greatly appreciated
Jason.



Sub ADOFromExcelToAccess()

' exports data from the active worksheet to a table in an Access
database
' this procedure must be edited before use
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim myRecordSet As ADODB.Recordset
Dim r As Long

'create the connection string and then
'connect to the Access database
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.Path & "\Access Experiment.mdb;"
Set cnAccess = New ADODB.Connection
cnAccess.ConnectionString = sConnect
cnAccess.Open

'open a recordset
Set myRecordSet = New ADODB.Recordset
myRecordSet.Open "Scorers", cnAccess, adOpenKeyset, _
adLockOptimistic, adCmdTable

r = 2 'the start row in the worksheet
Do While Len(Sheets("Experiment").Range("A" & r).Formula) > 0
'repeat until first empty cell in column A
With myRecordSet
'create a new record
'add values to each field in the record
.AddNew
.Fields("name") = Sheets("Experiment").Range("A" &
r).Value
.Fields("age") = Sheets("Experiment").Range("B" &
r).Value
.Fields("score") = Sheets("Experiment").Range("C" &
r).Value
.Update 'stores the new record
End With
r = r + 1 'next row
Loop

'clear up at the end
myRecordSet.Close
Set myRecordSet = Nothing
cnAccess.Close
Set cnAccess = Nothing

End Sub

 

[Guest]

Why don't you just copy and paste the data from Excel to Access? Or import
via Access?

 

[WhytheQ]

Thanks Dave.

Why don't you just copy and paste the data from Excel to Access? Or import
via Access?