Excel to Access Data using VBA

[raj08536]

Hi Gurus,

I am trying to load data between Excel to Access using following code in Excel.

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long

' connect to the Access database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Khalix\Khalix Forecast\ForecastSales.mdb;"


' cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Khalix\Khalix Forecast\ForecastSales.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"

' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Community", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Store") = Range("A" & r).Value
.Fields("Location") = Range("B" & r).Value
.Fields("Timeper") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

I am getting Compile Error : User defined type is not defined.

Need your help.

 

[Hermanito]

Just a wild guess, but did you include a reference to the ADO library?

Go to Tools/References and make sure to check the 'Microsoft ActiveX Data Objects 2.8 Library'.

There are probably many other numbers besides the 2.8, you might wanna experiment or google on which one is the most recent, I'm not sure on that...