export/deletingdata from Excel to access

[Shin]

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?

 

[Tom Ogilvy]

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

 

[onedaywhen]

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

--

 

[onedaywhen]

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>.

--