Export from Excel to Access

[Todd Bardoni]

I found this code here but it bugs-out on the highlighted line(**********). Any ideas?

Sub Rescan_tomdb()

' excel file that you want to export data to the database.
Dim mData As String
mData = ActiveWorkbook.Name 'this shows the excel workbook that is active

Dim mrow As Integer 'Counting of rows in sheet to be exported

'Create connection
**********Dim mConnection As ADODB.Connection**********
Set mConnection = New ADODB.Connection

'create the link to Access master data file using your connection created.
'You need to specify what drive your data base is on

Dim mtransit_file As String
mtransit_file = "Provider=Microsoft.Jet.oledb.4.0;data Source=" & ThisWorkbook.Path & "\AMOS_Database.mdb"
mConnection.Open mtransit_file

'create link to data table. Make sure you name the table in the mRecordset.open "PUT NAME OF YOUR TABLE "

Dim mRecordset As ADODB.Recordset
Set mRecordset = New ADODB.Recordset
mRecordset.Open "Rescan", mtransit_file, adOpenKeyset, adLockOptimistic


'Get row by row data from import file = excel worksheet

mrow = 2 ' the start row in the worksheet
Do While Len(Range("A" & mrow).Formula) > 0
' repeat until first empty cell in column A
With mRecordset
.AddNew ' create a new record
' add values to each field in the Access Database Record
.Fields("DCN") = Range("A" & mrow).Value
.Fields("ProcessDate") = Range("B" & mrow).Value
.Fields("Product") = Range("C" & mrow).Value
.Fields("Site") = Range("D" & mrow).Value
.Fields("StackName") = Range("E" & mrow).Value
.Fields("AccountID") = Range("F" & mrow).Value
.Fields("PolicyNumber") = Range("G" & mrow).Value
.Fields("ACSAction") = Range("H" & mrow).Value
.Fields("NewDCN") = Range("I" & mrow).Value
.Fields("Completed") = Range("J" & mrow).Value
.Fields("ACSNotify") = Range("K" & mrow).Value
.Fields("ACSNotifyDate") = Range("L" & mrow).Value
.Update ' stores the new record
End With
mrow = mrow + 1 ' next row
Loop

MsgBox "Your file has been successfully sent to the dataBase."

'Close all links - Clear memory
mRecordset.Close
Set mRecordset = Nothing
mConnection.Close
Set mConnection = Nothing

'Clear the excel worksheet.

Range("A2:L65536").Select
Selection.ClearContents
Range("a2").Select

End Sub

 

[acw]

Hi

Try adding the Microsoft DAO x.x Object Library as a reference in the VBE. x.x refers to the version that is available on your machine.


HTH

Tony