|
Moving data from Excel to Access
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 |
Moving data from Excel to Access
do you have a reference set to ADO in tools=references in the VBE?
-- Regards, Tom Ogilvy "William Francis" wrote in message ... 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 |
Moving data from Excel to Access
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" wrote: do you have a reference set to ADO in tools=references in the VBE? -- Regards, Tom Ogilvy "William Francis" wrote in message ... 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 |
Moving data from Excel to Access
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. -- Regards, Tom Ogilvy "William Francis" wrote in message ... 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" wrote: do you have a reference set to ADO in tools=references in the VBE? -- Regards, Tom Ogilvy "William Francis" wrote in message ... 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 |
Moving data from Excel to Access
Thanks again for your reply Tom, that has solved the problem!!
Thanks, Will "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "William Francis" wrote in message ... 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" wrote: do you have a reference set to ADO in tools=references in the VBE? -- Regards, Tom Ogilvy "William Francis" wrote in message ... 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 |
| All times are GMT +1. The time now is 05:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com