Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
export/deletingdata from Excel to access
In order to export data from Excel to Access, Ihave to
have Microsoft ActiveX Data obects 2.7 Library selected. In order to delete the table data in Access before exporting the new data, I have to have MS DAO 3.6 Object Library selected. Whne I have both selected, the delete code won't work. When I get to Set rs = db.OpenRecordset("test") I get an error message that says "type mismatch" Can anyone help me out? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
export/deletingdata from Excel to access
Make explicit declarations and make sure you work consistently with the
objects of each library (although I wouldn't think you would need to use both, but I have heard there are some weaknesses in ADO). Dim rs as ADODB.RecordSet Dim rs1 as DAO.RecordSet -- Regards, Tom Ogilvy "Shin" wrote in message ... In order to export data from Excel to Access, Ihave to have Microsoft ActiveX Data obects 2.7 Library selected. In order to delete the table data in Access before exporting the new data, I have to have MS DAO 3.6 Object Library selected. Whne I have both selected, the delete code won't work. When I get to Set rs = db.OpenRecordset("test") I get an error message that says "type mismatch" Can anyone help me out? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
export/deletingdata from Excel to access
You don't *need* DAO to drop (delete) a table, you can use ADO and SQL e.g.
Sub DeleteMyTable() Dim oCon As ADODB.Connection Const strDB As String = "C:\MyDB.mdb" Set oCon = New ADODB.Connection With oCon .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strDB .Execute "DROP TABLE MyTable" .Close End With Set oCon = Nothing End Sub -- "Tom Ogilvy" wrote in message ... Make explicit declarations and make sure you work consistently with the objects of each library (although I wouldn't think you would need to use both, but I have heard there are some weaknesses in ADO). Dim rs as ADODB.RecordSet Dim rs1 as DAO.RecordSet -- Regards, Tom Ogilvy "Shin" wrote in message ... In order to export data from Excel to Access, Ihave to have Microsoft ActiveX Data obects 2.7 Library selected. In order to delete the table data in Access before exporting the new data, I have to have MS DAO 3.6 Object Library selected. Whne I have both selected, the delete code won't work. When I get to Set rs = db.OpenRecordset("test") I get an error message that says "type mismatch" Can anyone help me out? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
export/deletingdata from Excel to access
but I have heard there are some weaknesses in ADO
I've heard there are a few 'database maintenance' type things you can do with DAO that have no ADO equivalent but they are fairly obscure (well, I've never encountered one!) Otherwise I know of no ADO 'weaknesses'. Make explicit declarations Good advice to explicitly use the class name in declarations. Shame one rarely sees Excel.Range and Excel.Name used here more often <g. -- "Tom Ogilvy" wrote in message ... Make explicit declarations and make sure you work consistently with the objects of each library (although I wouldn't think you would need to use both, but I have heard there are some weaknesses in ADO). Dim rs as ADODB.RecordSet Dim rs1 as DAO.RecordSet -- Regards, Tom Ogilvy "Shin" wrote in message ... In order to export data from Excel to Access, Ihave to have Microsoft ActiveX Data obects 2.7 Library selected. In order to delete the table data in Access before exporting the new data, I have to have MS DAO 3.6 Object Library selected. Whne I have both selected, the delete code won't work. When I get to Set rs = db.OpenRecordset("test") I get an error message that says "type mismatch" Can anyone help me out? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Export from Access to Excel | Excel Discussion (Misc queries) | |||
Export Excel File From Web or Access | Excel Discussion (Misc queries) | |||
Export to Access from Excel | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Export from Access to Excel | Excel Discussion (Misc queries) |