Moving data from Excel to Access

[Guest]

Hi all,

Hopefully someone can help. I am trying to use some code to move some text
entered into a spreadsheet into an Access database, however when I run the
code I get the error "Compile error: User-defined type not defined" with the
"cn As ADODB.Connection" portion of the code highlighted. Am I missing
something very obvious?

My code is:-

Sub AddInfoToTAMDB()
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=K:\TAM Message.mdb;"

Set rs = New ADODB.Recordset
rs.Open "Customers", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
.AddNew
.Fields("DateTime") = txtTAMDateTime.Value
.Fields("CallHandler") = txtTAMCH.Value
.Fields("IFAName") = txtIFAName.Value
.Fields("IFAFirm") = txtIFAFirm.Value
.Fields("PostCodeorAgencyNumber") = txtPostCode.Value
.Fields("PhoneNumber") = txtTelNo.Value
.Fields("AccountManager") = txtAccMan.Value
.Fields("Message") = txtMessage.Value
.Update
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

All help greatly appreciated!!

Thanks, Will

 

[Tom Ogilvy]

do you have a reference set to ADO in tools=>references in the VBE?

 

[Guest]

Hi Tom,

Thanks for the reply. Yes I have a reference to "Microsoft ADO 2.7 for DDL
and Security" in the VBE.

Thanks, Will

 

[Tom Ogilvy]

That would give ADOX where you want ADODB

You need a reference to Microsoft Active Data Objects 2.x Library

Then go into the object browser and from the library dropdown you should see
ADODB.

If you don't see it, then you don't have the right reference.

 

[Guest]

Thanks again for your reply Tom, that has solved the problem!!

Thanks, Will