| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving excel workbook with access query to another folder | Excel Discussion (Misc queries) | |||
| Moving Data between sheets in the same workbook and moving data between Workbooks. | Excel Worksheet Functions | |||
| Moving an Excel pivot table to access | Excel Worksheet Functions | |||
| Moving linked Excel/Access files to another machine | Links and Linking in Excel | |||
| moving data between excel and access | Excel Programming | |||
![William Francis[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
