Access Data in Excel Connection Errors

[Guest]

Hi, I have the below code, and I am learning how to store stuff from excel to
Access. I build a program that stored all the info in another Excel
workbook, however it has gotten to big now, and I need something bigger...but
when I run this command, it gives me the below error...anyone got any ideas
how to fix this...

Error:
Run-time error '-2147024770 (8007007e)':

Automation error
The specified module could not be found.

When I click debug, it highlights New ADODB.Connection as being the
error...any ideas??

Example:
Sub ADOFromExcelToAccess()
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=C:\Documents and Settings\Administrator\Desktop\Master_Logs.mdb;"
Set rs = New ADODB.Recordset
rs.Open "User", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 3
Do While Len(Range("A" & r).Formula) > 0
With rs
..AddNew
..Fields("PIP_ID") = BITE.textbox1.Value
..Fields("F_NAME") = BITE.textbox2.Value
..Fields("L_NAME") = BITE.textbox3.Value
..Fields("Initials") = BITE.textbox4.Value
..Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Guest]

Check your Library references to see if you have a reference set the ADO

 

[Guest]

In Excel I have the add-in ADOConnectionObject Class selected & in the VBA
Editor I have references selected for Microsoft ActiveX Data Objects
(Multi-dimensional) 2.7 Library, Microsoft ActiveX Data Objects 2.0 Library,
Mircosoft Access 11.0 Object Library, Microsoft ActiveX Data Objects
Recordset 2.7 Library & OLE Automation selected. If something is missing or
too many are selected, please let me know.

 

[Guest]

I believe you need Microsoft ADO Ext. 2.7 for DDL and Security. Whether or
not you need the others, I don't know. I also don't know which version of
Access you are on, so the above library may not be correct. I am on 2003.

 

[Guest]

I am running Excel 2002 & Access 2003, I have selected the Microsoft ADO Ext.
2.7 for DDL and Security, and it still gives the same error. It keeps
faulting at the ADODB Connection...

 

[Guest]

Sorry, Thomas, but I don't see the problem. Maybe someone more familiar with
ADO can help.

 

[Norbert Bous via AccessMonster.com]

Sorry, Thomas, but I don't see the problem. Maybe someone more familiar with
ADO can help.

I am running Excel 2002 & Access 2003, I have selected the Microsoft ADO Ext.
2.7 for DDL and Security, and it still gives the same error. It keeps

[quoted text clipped - 53 lines]

The reference should be Microsoft ActiveX data Objects 2.x Library. The value
for x depends on your installed ADO version.

 

[Guest]

Klatuu, thanks for the help so far!! I really appreciate it.

 

[Guest]

I do have it referenced, I have Microsoft ActiveX Data Objects
(Multi-dimensional) 2.7 Library. Could this be the wrong one? There was a
2.5, and it did not have the "Multi-dimensonal" associated with it, so I
don't know if possibly that one is meant for something else...

Sorry, Thomas, but I don't see the problem. Maybe someone more familiar with
ADO can help.

I am running Excel 2002 & Access 2003, I have selected the Microsoft ADO Ext.
2.7 for DDL and Security, and it still gives the same error. It keeps

[quoted text clipped - 53 lines]

Set cn = Nothing
End Sub

The reference should be Microsoft ActiveX data Objects 2.x Library. The value
for x depends on your installed ADO version.

 

[Norbert Bous via AccessMonster.com]

Klatuu, thanks for the help so far!! I really appreciate it.

Sorry, Thomas, but I don't see the problem. Maybe someone more familiar with
ADO can help.

[quoted text clipped - 56 lines]

You can check the right version in a new Access database in the VBA editor.
The Multi-dimensional one, referenced as ADOMD, is for other purposes.